优化Google Sheets数组代码：解决作物采收计划生成超时问题

我在Google Sheet的「Site base production plan」标签页里有两个表格：第一个表格每行记录一种作物信息，第二个是对应作物的拆分数据，需要基于这些数据生成采收计划数据集。

我不是专业开发，大学学过C++，用逐单元格读写的类C++风格代码能正常运行，但速度太慢，经常触发执行超时错误。尝试改成数组方式优化后没得到预期输出，求正确的数组式Google Apps Script代码。

原逐单元格读写代码 #

function writetoSheet()
{
var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var tab = ss.getActiveSheet();
var write_tab = ss.getSheetByName("Expected Site Production Data");
var read_tab = ss.getSheetByName("Site base production plan");
var lstrowtable1 = columnlastrow(read_tab,1);                        //Gets last row of table 
var lstrowtable2 = columnlastrow(read_tab,23);                       //Gets last row of table 
generateharvestdata(lstrowtable1,lstrowtable2,read_tab,write_tab);
}

function columnlastrow(read_tab,clmn)                      
{
  var j= read_tab.getLastRow()+1;
  do {      
      j=j-1;
    } while(read_tab.getRange(j,clmn).getValue() == "")
  return j;
}

function generateharvestdata (lstrowtable1,lstrowtable2,read_tab,write_tab)
{
 var k=2
 for ( var i =1;i <= lstrowtable1;i++)
 {  
   var checkvalidaggregate = read_tab.getRange(i+2,22).getValue();
   if(checkvalidaggregate != "N")
   {
    var recordid1=read_tab.getRange(i+2,1).getValue();
      for ( var j=1; j<=lstrowtable2;j++)
      { 
        var recordid2 = read_tab.getRange(j+2,23).getValue();
        var checkvalidbreakup = read_tab.getRange(j+2,43).getValue();
        if(recordid1 == recordid2 && checkvalidbreakup != "N" ) 
         {  
          var frequency=read_tab.getRange(j+2,34).getValue();
          var pickingsdate=new Date(read_tab.getRange(j+2,35).getValue());
          var pickingedate=new Date(read_tab.getRange(j+2,36).getValue());
          var currentpickingdate= new Date();
          currentpickingdate = pickingsdate;
          do
            {
              write_tab.getRange(k,1).setValue(recordid2);          
                
              var siteid= read_tab.getRange(j+2,24).getValue();    
              write_tab.getRange(k,2).setValue(siteid);            
                
              var fy_year= read_tab.getRange(j+2,25).getValue();
              write_tab.getRange(k,3).setValue(fy_year);
                
              var crop=read_tab.getRange(j+2,26).getValue();
              write_tab.getRange(k,4).setValue(crop);

              var season=read_tab.getRange(j+2,27).getValue();
              write_tab.getRange(k,5).setValue(season);
                
              write_tab.getRange(k,6).setValue(currentpickingdate);
               
              var pickquanity=read_tab.getRange(j+2,37).getValue();
              write_tab.getRange(k,7).setValue(pickquanity);
               
              //var unitofmeasure=read_tab.getRange(j+2,32).getValue();
              //write_tab.getRange(k,8).setValue(unitofmeasure);
                
              currentpickingdate.setTime(currentpickingdate.getTime()+frequency*(24*60*60*1000));
                
              k++;
          
              } while(currentpickingdate.getTime()< pickingedate.getTime())
          }
      }    
   } 
 }
}

尝试的数组优化代码（未得到预期输出） #

// Attempt using arrays...

/*function generateharvestdata (lstrowtable1,lstrowtable2,read_tab,write_tab)
{ var harvestdarray =[];
  harvestdarray = [  
                    ["Base Production Record ID","Site ID","Financial Year","Crop","Season","Picking date","Picking Quantity","Unit of measurement"]
                  ];
 for ( var i =1;i <= lstrowtable1;i++)
 {  
   var checkvalidaggregate = read_tab.getRange(i+2,22).getValue();
   if(checkvalidaggregate != "N")
   {
    var recordid1=read_tab.getRange(i+2,1).getValue();
      for ( var j=1; j<=lstrowtable2;j++)
      { 
        var recordid2 = read_tab.getRange(j+2,23).getValue();
        var checkvalidbreakup = read_tab.getRange(j+2,43).getValue();
        if(recordid1 == recordid2 && checkvalidbreakup != "N" ) 
         {  
          var frequency = read_tab.getRange(j+2,34).getValue();
          var pickingsdate=new Date(read_tab.getRange(j+2,35).getValue());
          var pickingedate=new Date(read_tab.getRange(j+2,36).getValue());
          var currentpickingdate= new Date();
          currentpickingdate = pickingsdate;
          do
            {
              var siteid = read_tab.getRange(j+2,24).getValue();
              var fy_year = read_tab.getRange(j+2,25).getValue();
              var crop=read_tab.getRange(j+2,23).getValue();
              var cropping_season =read_tab.getRange(j+2,26).getValue();
              var pickingquantity = read_tab.getRange(j+2,27).getValue();
              var unitofmeasure = read_tab.getRange(j+2,37).getValue();
              
              harvestdarray.push([recordid2,siteid,fy_year,crop,cropping_season,currentpickingdate,pickingquantity,unitofmeasure]);
              currentpickingdate.setTime(currentpickingdate.getTime()+frequency*(24*60*60*1000));
                
              } while(currentpickingdate.getTime()< pickingedate.getTime())
            write_tab.getRange(1,1,harvestdarray.length,8).setValues(harvestdarray);
          }
      }    
   } 
 }
}
*/

优化后的数组式代码 #

function writetoSheet() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const writeTab = ss.getSheetByName("Expected Site Production Data");
  const readTab = ss.getSheetByName("Site base production plan");
  
  // 获取两个表格的有效最后一行
  const table1LastRow = getColumnLastRow(readTab, 1);
  const table2LastRow = getColumnLastRow(readTab, 23);
  
  // 一次性读取所有需要的数据到数组，避免多次调用getRange
  const table1Data = readTab.getRange(3, 1, table1LastRow - 2, 22).getValues();
  const table2Data = readTab.getRange(3, 23, table2LastRow - 2, 21).getValues();
  
  // 初始化采收计划数组，包含表头
  const harvestData = [["Base Production Record ID","Site ID","Financial Year","Crop","Season","Picking date","Picking Quantity","Unit of measurement"]];
  
  // 遍历表格1的有效记录
  table1Data.forEach(row1 => {
    const checkValidAggregate = row1[21]; // 对应原代码第22列（数组索引从0开始）
    if (checkValidAggregate !== "N") {
      const recordId1 = row1[0]; // 对应原代码第1列
      
      // 遍历表格2，匹配相同的记录ID
      table2Data.forEach(row2 => {
        const recordId2 = row2[0]; // 对应原代码第23列
        const checkValidBreakup = row2[20]; // 对应原代码第43列
        if (recordId1 === recordId2 && checkValidBreakup !== "N") {
          
          // 提取表格2的各项数据
          const frequency = row2[11]; // 对应原代码第34列
          const startDate = new Date(row2[12]); // 对应原代码第35列
          const endDate = new Date(row2[13]); // 对应原代码第36列
          const siteId = row2[1]; // 对应原代码第24列
          const fyYear = row2[2]; // 对应原代码第25列
          const crop = row2[3]; // 对应原代码第26列
          const season = row2[4]; // 对应原代码第27列
          const pickingQty = row2[14]; // 对应原代码第37列
          // const unit = row2[9]; // 对应原代码第32列，需要的话可以取消注释
          
          // 生成所有采收日期的记录
          let currentDate = new Date(startDate);
          while (currentDate.getTime() < endDate.getTime()) {
            harvestData.push([
              recordId2,
              siteId,
              fyYear,
              crop,
              season,
              new Date(currentDate), // 复制日期对象，避免引用导致的异常
              pickingQty
              // unit // 需要单位的话取消注释
            ]);
            // 计算下一次采收日期
            currentDate.setDate(currentDate.getDate() + frequency);
          }
        }
      });
    }
  });
  
  // 清空目标表并批量写入数据
  writeTab.clearContents();
  if (harvestData.length > 0) {
    writeTab.getRange(1, 1, harvestData.length, harvestData[0].length).setValues(harvestData);
  }
}

// 优化后的获取列最后一行函数，用数组读取提升效率
function getColumnLastRow(sheet, column) {
  const values = sheet.getRange(1, column, sheet.getLastRow()).getValues();
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0] !== "") {
      return i + 1;
    }
  }
  return 0;
}

优化说明 #

• 一次性读取数据：将两个表格的所有数据一次性加载到数组，避免循环中频繁调用getRange，大幅减少与Sheet的交互次数，提升运行速度。
• 修正列索引错误：修复了尝试数组代码中列索引匹配错误的问题（比如作物列、采收量列的索引）。
• 日期处理优化：复制日期对象，避免因引用导致的日期值异常。
• 批量写入：所有数据生成完成后一次性写入目标表格，仅调用一次setValues，避免逐行写入的性能损耗。
• 清空目标表：写入前清空目标表内容，避免旧数据残留。

内容的提问来源于stack exchange，提问作者user506606