在google-apps script中超过最大执行时间

Question

我有两个电子表格。我想把spreadsheet1的C列和spreadsheet2的A列相匹配。这两个电子表格都有8000多条记录。由于大量的记录，我的脚本经常给出exceeded maximum execution time错误。这是剧本

function compare() {

var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetList1=ss.getSheetByName("spreadsheet1");
  var sheetList2=ss.getSheetByName("spreadsheet2");

   var sheet1Data=sheetList1.getRange(2,3,sheetList1.getLastRow(),1).getValues();

   var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues();

   for (i in sheet2Data){
    var row = 2;
      for (j in sheet1Data){
        if (sheet1Data[j][0]==sheet2Data[i][0]){
           sheetList1.getRange("A"+row).setValue('Inactive');           
        }
        row++;
      }


   }

}

任何关于优化这个脚本的建议。或者如何处理这个错误？(预先谢谢:)

编辑

谢谢你精彩的回答。有一个问题。如果我在newSheet1Data数组中在if语句之前推送数据，那么它会写两次Inactive。也就是说，如果有两行，则将inactive写入四个rows.Like

newSheet1Data.push(sheet1Data[j]); 
        if (sheet1Data[j][2]==sheet2Data[i][0]){
          newSheet1Data[j][0]='Inactive';
        }

如果我在if语句中推送数据而没有匹配，那么它就找不到行并给出错误TypeError: Cannot set property "0.0" of undefined to "Inactive"。喜欢

if (sheet1Data[j][0]==sheet2Data[i][0]){
           newSheet1Data.push(sheet1Data[j]);
           newSheet1Data[j][0]='Inactive';           
        }

Answer 1

您应该避免在循环中调用电子表格API，特别是当您有这么多数据时。

这样做的目的是只对数组进行播放，并在完成后将结果写回。下面的代码是这样做的(根据您的代码，如果sheet1中的数据与sheet2 col中的数据相同，那么在sheet1 col中编写一个字符串)。我希望我没有弄错，但我没有机会测试我的代码.它可能需要一些调试;-)

function compare() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetList1=ss.getSheetByName("Sheet1");
  var sheetList2=ss.getSheetByName("Sheet2");
  var sheet1Data=sheetList1.getDataRange().getValues();// get the whole sheet in an array
  var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues();
  var newSheet1Data = [] ; // create a new array to collect data
   for (i=0;i<sheet1Data.length;++i){
      for (j=0;j<sheet2Data.length;++j){
      if(i!=j){continue};
        newSheet1Data.push(sheet1Data[i]); // add the full row to target array
        if (sheet1Data[i][2]==sheet2Data[j][0]){
          newSheet1Data[i][0]='Inactive';//if condition is true change column A
          break
        }
      }
   }
  newSheet1Data.shift();// remove first row (probably headers ?)
  sheetList1.getRange(2,1,newSheet1Data.length,newSheet1Data[0].length).setValues(newSheet1Data);  // write back to sheet1 in one batch
} 

编辑:看完你的医生后，我更清楚你想要什么.这是新代码(在您的SS上测试)。

function compare() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var sheetList1=ss.getSheetByName("Sheet1");
  var sheetList2=ss.getSheetByName("Sheet2");
  var sheet1Data=sheetList1.getDataRange().getValues();// get the whole sheet in an array
  var sheet2Data=sheetList2.getRange(1,1,sheetList2.getLastRow(),1).getValues();
  var newSheet1Data = [] ; // create a new array to collect data
  for (i=0;i<sheet1Data.length;++i){
    newSheet1Data.push(sheet1Data[i]); // add the full row to target array
       for (j=0;j<sheet2Data.length;++j){
         if (sheet1Data[i][2]==sheet2Data[j][0]){
          newSheet1Data[i][0]='Inactive';//if condition is true change column A
          break;// don't continue after condition was true, this will speed up the process
        }
      }
   }
  newSheet1Data.shift();// remove first row (probably headers ?)
  sheetList1.getRange(2,1,newSheet1Data.length,newSheet1Data[0].length).setValues(newSheet1Data);  // write back to sheet1 in one batch
}