if-statement - Googlescript 'if' question - 只做两个任务之一

Question

我有“else if”语句，只有在前一个“if”没有执行时才需要调用它。第一个'if'（检查状态）工作完美，第二个工作完美，但'else if'每次都完成，因为表中有不同的类型（A，B，C等）

编辑： 1.如果没有打开的A，则必须添加新行；2.如果有不止一个A-都必须关闭；3. 忽略所有其他类型，仅在没有 As open 的情况下添加新行；

这是我的代码：

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetName');
  var data = sheet.getRange(2,1,sheet.getLastRow(),2).getValues();
  var nextRow = SpreadsheetApp.getActiveSheet().getDataRange().getLastRow()+1;

  for (var i=0; i<data.length; i++){
    var row = data[i];
    var type = row[0];
    var status = row[1];

//check it always     
    if (status != 'close'){

//check first and if it's true don't do 'else if'      
       if (type == 'A') {
         sheet.getRange(i+2,2).setValue('close')
       }

//this should only be called if the previous 'if' is not true      
      else if (type != 'A' && type != ''){
        var values = [['A','open']];
        sheet.getRange("A"+nextRow+":B"+nextRow).setValues(values); 
      }
    }
  }
}

标签： if-statementgoogle-apps-script

Answer 1

流动：

• 遍历所有数据以查找是否有“A:Open”
• 如果在所有数据中找到，只需关闭该行
• 否则，添加一个新行：“A:Open”
• 使用布尔变量 ( isThereASingleAOpen) 来跟踪 A:Open found 状态

示例脚本：

function closeAandAddOpenA() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('SheetName');

  //Separate range and data
  var range = sheet.getRange(2, 1, sheet.getLastRow(), 2);
  var data = range.getValues();

  //Is there a A Open in the data? Let's assume there's none
  var isThereASingleAOpen = false;

  //Loop through data array
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var type = row[0];
    var status = row[1];

    //If status is != close and type == 'A'
    if (status != 'close' && type == 'A') {
      //Change current row status to close in the data array
      row[1] = 'close';
      //Oops We were wrong. There was a A open in the data
      isThereASingleAOpen = true;
    }
  } //Loop closed

  //Set the modified data back to the range
  //Batch  operations are faster
  range.setValues(data);

  //if there is NOT a single A open in all of data, Append a new line
  if (!isThereASingleAOpen) {
    sheet.appendRow(['A', 'Open']);
  }
}