javascript - 如何使用谷歌脚本从列表中返回另一个列表中不存在的值

Question

我有两个清单。列表一包含所有当前员工姓名，列表二包含所有当前和过去员工姓名。

我需要开发一个脚本来返回所有过去的名字以及列表二中连接到它们的信息。我编写了这段代码，它将所有当前名称与其当前信息相匹配，但无法弄清楚如何做相反的事情。

function current(){

  var app = SpreadsheetApp;
 
 var activeSheet = app.getActiveSpreadsheet();

  var issued = app.getActiveSpreadsheet().getSheetByName("Issued");

  var waiting = app.getActiveSpreadsheet().getSheetByName("Waiting for Inventory");

  var active = app.getActiveSpreadsheet().getSheetByName("Active Officers");
  
 for (var j = 2; j<100; j++){ 
  
  var officer = issued.getRange(j,11).getValue();
 
  for(var i = 2; i<70; i++){ 

    var officerList = active.getRange(i, 2).getValue();

     if (officer === ""){
       continue; 
      }

     if (officer === officerList){
      var rowInfo = issued.getRange(j,1,1,12).getValues();
      var lastrow = waiting.getLastRow();
      waiting.getRange(lastrow+1,1,1,12).setValues(rowInfo);
      }
    }
  }
}

标签： javascriptarraysgoogle-apps-script

Answer 1

检索数组中是否存在字符串的最简单方法是使用indexOf()

• -1如果未找到值，它将返回您
• 使用flat()从数组中检索所有官员active并将值转换为1D数组
• 检索所有的官员（比issued使用getValues多个请求getValue并循环访问它们更有效）
• 这种设置只需要一个for循环而不是两个循环，这将使您的代码显着加快

样本：

function current(){
  
  var app = SpreadsheetApp;
  
  var activeSheet = app.getActiveSpreadsheet();
  
  var issued = app.getActiveSpreadsheet().getSheetByName("Issued");
  
  var waiting = app.getActiveSpreadsheet().getSheetByName("Waiting for Inventory");
  
  var active = app.getActiveSpreadsheet().getSheetByName("Active Officers");
  
  var officerList = active.getRange(2, 2, 69, 1).getValues().flat();
  var officers = issued.getRange(2,11,99,1).getValues();
  Logger.log(officerList);
  for (var j = 0; j < 99; j++){     
    var officer = officers[j][0];
    if (officerList.indexOf(officer) ==-1){
      Logger.log("officer " + officer + " from Issued does not exist in Active Officers");
      var rowInfo = issued.getRange(j+2,1,1,12).getValues();
      var lastrow = waiting.getLastRow();
      waiting.getRange(lastrow+1,1,1,12).setValues(rowInfo);
    }
  }
}