优化Google Apps Script：跳过无日期行以提升运行效率

问题背景 #

我有一份数据列表，已编写Google Apps Script将数据导入数据库，通过匹配人员姓名把日期填入对应列。现在需要调整脚本，跳过第三列无日期的行（例如仅更新Brad、Chris、David、Jacob的记录，跳过Dana、Gabi）。

当前脚本存在严重效率问题：每条记录查询数据库耗时约20秒，列表包含40-50个姓名，但仅4-5个有日期，导致总运行时长超过5分钟。以下是现有可运行的脚本：

function datesFASigned() {
      var data4RUSS = SpreadsheetApp.getActiveSpreadsheet(); // Set variable for Google Sheet
      var shDates = data4RUSS.getSheetByName("Live to RUSS"); //Set variable for Franchise Data
      var listLength = shDates.getRange("J4").getValue(); // Set variable for length of FDD list
      let listRange = shDates.getSheetValues(4,5,listLength,3); // Set range for Can Sign FA List

      var toRUSS = SpreadsheetApp.openById("1H--DhiSVjDnhVV8xYCD-H1QJOrTcML33GG05nzrAXN0");
      var dataEntry = toRUSS.getSheetByName("Data Entry"); //Data Entry tab on RUSS
  

    for (var a = 5; a <= listLength+4; a++) //formula to advance through class roster
    {
       var PPAD       = shDates.getRange(a,9).getValue(); //Set variable for Emp roster 
       console.log(PPAD);
       var values = dataEntry.getDataRange().getValues(); //getting the entire values from Emp Data    range and assigning it to values variable
    

        for (var i = 0; i < values.length; i++) // formula to search Emp Data for Emp Name in column CP
     {
       var rowValue = values[i]; // declaring the data row as a variable

       //checking the first value of the record is equal to search item
        if (rowValue[0] == PPAD) {

         var  iRow = i+1; //identify the row number
         console.log(iRow)
 
         dataEntry.getRange(iRow, 43).setValue(shDates.getRange(a,12).getValue()); //Date ppAD  Signed FA

       valuesFound=true;


          }

      }

    }

    }

优化后的脚本 #

function datesFASigned() {
  // 初始化源表格对象
  const sourceSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sourceSheet = sourceSpreadsheet.getSheetByName("Live to RUSS");
  const listLength = sourceSheet.getRange("J4").getValue();

  // 一次性读取所需的姓名（第9列）和日期（第12列）数据
  // 范围：从第4行开始，取listLength行，覆盖第9到第12列
  const sourceData = sourceSheet.getRange(4, 9, listLength, 4).getValues();

  // 初始化目标表格对象
  const targetSpreadsheet = SpreadsheetApp.openById("1H--DhiSVjDnhVV8xYCD-H1QJOrTcML33GG05nzrAXN0");
  const targetSheet = targetSpreadsheet.getSheetByName("Data Entry");
  const targetAllData = targetSheet.getDataRange().getValues();

  // 构建姓名-行号映射表，避免循环查找
  const nameToRowMap = {};
  targetAllData.forEach((row, index) => {
    const employeeName = row[0];
    if (employeeName) {
      nameToRowMap[employeeName] = index + 1; // 转换为表格实际行号（从1开始）
    }
  });

  // 遍历源数据，仅处理有日期的记录
  sourceData.forEach((row, rowIndex) => {
    const employeeName = row[0];
    const signDate = row[3]; // 第12列对应数组索引3

    // 跳过无日期的行
    if (!signDate) {
      console.log(`跳过无日期记录：${employeeName}`);
      return;
    }

    // 查找目标行号并更新日期
    const targetRow = nameToRowMap[employeeName];
    if (targetRow) {
      targetSheet.getRange(targetRow, 43).setValue(signDate);
      console.log(`已更新 ${employeeName} 的签署日期，目标行：${targetRow}`);
    } else {
      console.log(`未找到 ${employeeName} 的目标记录`);
    }
  });
}

优化说明 #

1. 跳过无日期行：在遍历源数据时，先判断日期值是否为空，为空则直接跳过，避免无效处理
2. 减少API调用：一次性读取源表格和目标表格的所有所需数据，避免在循环中反复调用getRange()/getValue()（这类IO操作是脚本耗时的核心原因）
3. 优化查找效率：将目标表格的姓名与行号提前映射为对象，把原来的嵌套循环（O(n*m)时间复杂度）改为直接键值查找（O(1)复杂度），彻底解决单条记录20秒的查询延迟问题

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