javascript - Google 表格脚本，每个函数都与 && 运算符结合并设置值范围以检查数组

Question

跟进将重复数据发送到数组，然后检查所述数组是否为空，以决定接下来运行什么代码，如果语句不能正常工作。

我非常想复制我用来通知用户“坏”数据的条件格式，但使用脚本来防止发送坏数据并给出唯一的错误消息。根据我上一个问题的答案，检查和防止发送不发送数据或重复数据的工作，但我现在坚持防止发送与四位数格式不匹配的数据。

我的条件格式是在不等于或介于 1000 到 9999 之间的任何内容上将背景颜色设置为橙色，但我无法让脚本正常工作——我正在尝试对介于两者之间的任何内容进行否定两个值作为“false”将提示错误消息，“true”让脚本的其余部分运行，将数据和通知电子邮件发送出去。但是，即使我确实有正确的数据，这也表明存在错误的值。删除否定可以让任何事情通过，就像它实际上并没有检查它一样。有任何想法吗？

我要问的部分是最后一个 else if 语句：

else if (!data.every(function(num) {return num >= 1000 && num <= 9999})) {
    SpreadsheetApp.getUi().alert("You have incorrectly formatted tallies, tallies must be four digits.", SpreadsheetApp.getUi().ButtonSet.OK);
  }

总代码如下。

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var ssSheet = ss.getActiveSheet();

//https://techyesplease.com/code/google-apps-script-find-duplicates-sheets/
function readData() {
  var dataColumn = 3;
  var firstRow = 6;
  var lastRow = ssSheet.getLastRow();
  var numRows = lastRow - firstRow + 1;
  var columnRange = ssSheet.getRange(firstRow, dataColumn, numRows);
  //var rangeArray = columnRange.getValues();
  // Convert to one dimensional array
  //rangeArray = [].concat.apply([], rangeArray);
  var rangeArray = columnRange.getValues().flat().filter(String);
  return rangeArray;
}

// Sort data and find duplicates
function findDuplicates(dataAll) {
  var sortedData = dataAll.slice().sort();
  var duplicates = [];
  for (var i = 0; i < sortedData.length - 1; i++) {
    if (sortedData[i + 1] == sortedData[i]) {
      duplicates.push(sortedData[i]);
    }
  }
  return duplicates;
}

//Use the same string for this variable as the name for the requestor for his or her column of data. E.g., John Doe
//****GLOBALS****

var targetSheet = 'All Tallies'; //replace with sheet/tab name
var targetSpreadsheetID = 'id'  //replace with destination ID
var targetURL = 'url'

//var dataNotificationReceivingEmailAddresses = 
//Set up to be able to easily change what emails the data notification goes to?

function sendDataAndTimestamp2() {

 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var ssSheet = ss.getActiveSheet();
 var sourceRange = ssSheet.getRange('C6:C401');  
//assign the range you want to copy, could make C6:C? No, would like dynamic.
 var data = sourceRange.getValues();

 var nameRange = ssSheet.getRange('C4:D4');
 var nameValue = nameRange.getDisplayValue();


 var tallyDateRange = ssSheet.getRange('C2');
 var tallyDateValue = tallyDateRange.getDisplayValue();
 var tallyDateText = 'Tallies to run on '+ tallyDateValue;


 var tallyAmountRange = ssSheet.getRange(8,1);
 var tallyAmount = tallyAmountRange.getDisplayValue();
 var tallyAmountNumberOnly = data.filter(String).length; 
//Used as tallyAmount includes text, for some cases need the number only
 
 var thisDocumentUrl = ss.getUrl();
  //Variables for the sending/source spreadsheet above


 //Initial confirmation alert, need checks for blank or error tallies first. First condition needs to loop through data variable and check that if any values are numbers not between 1000 and 9999, throw up Ui alert error message. Second condition goes to result variable?

//Reference the earlier functions
  var dataArray = readData();
  var duplicates = findDuplicates(dataArray);

//Need to check data and have error message if  duplicates.length >=1, if 0 allow, refuse if data length less than 1
  if (duplicates.length !== 0) {
SpreadsheetApp.getUi().alert("Your tallies include duplicates, please remove them then try again.", SpreadsheetApp.getUi().ButtonSet.OK);
Logger.log(duplicates);
  }
  else if (dataArray.length ===0) {
    SpreadsheetApp.getUi().alert("You have not input any tallies.", SpreadsheetApp.getUi().ButtonSet.OK);
  }

  else if (!data.every(function(num) {return num >= 1000 && num <= 9999})) {
    SpreadsheetApp.getUi().alert("You have incorrectly formatted tallies, tallies must be four digits.", SpreadsheetApp.getUi().ButtonSet.OK);
  }

/*https://www.youtube.com/watch?v=gaC290XzPX4&list=PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD&index=10
Every method
var arr = [1,2,3,4];
var allTalliesGood = data.every(function(num){
    return num < 9
});
//Use with num >= 1000, num <=9999, and then if true continue, if false stop and give error msg
*/




/*
dataFiltered = data.filter(filterlogic);

var filterlogic = function(tally){
  if (tally >= 1000 && tally <= 9999){
    return true;
  } else {
    return false
    }
}
//if use false for good tallies, and rename dataFiltered to something like "dataBad", then check if dataBad has true matches in it...
//need to check for strings? what happens if letters/symbols in tally range?
//var dataBad = data.filter(function(tally){ return tally < 1000 || tally > 9999;});
// use OR (||) for getting tallies great than or less than, what about letters/symbols? Use NOT good range?

//https://www.youtube.com/watch?v=hPCIOohF0Fg&list=PLv9Pf9aNgemvD9NFa86_udt-NWh37efmD&index=8
//sort method link above
*/



  else {
//    rest of code
  


  var result = SpreadsheetApp.getUi().alert("You're about to notify scheduler of the following number of tallies: " + tallyAmountNumberOnly, SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);


  if(result === SpreadsheetApp.getUi().Button.OK) {
    //Code to send out emails and data

标签： javascriptarraysgoogle-apps-scriptgoogle-sheetsgoogle-sheets-api

Answer 1

据我所知，sendDataAndTimestamp2()您需要从函数中删除空值data。可以通过以下filter(String)方法完成：

var data = sourceRange.getValues().filter(String); // <-- here

. . .

else if (!data.every(function(num) {return num >= 1000 && num <= 9999}))

. . .

或者，我不知道，也许您的意思dataArray是data：

else if (!dataArray.every(function(num) {return num >= 1000 && num <= 9999}))

顺便说一句，这条线可以缩短一点：

else if (!data.every(x => (x >= 1000) && (x <= 9999)))