如何修改Google Apps Script实现批量更新所有匹配行？

原脚本功能是将Y2:Y7作为表单数据，匹配P22:P区域中的Y2值后更新对应行的P22:U数据；未匹配则追加新行。但原脚本仅能更新最后一个匹配行，以下是修改后的实现方案：

修改后的代码 #

function settings() {
  var lock = LockService.getDocumentLock();
  lock.waitLock(20000);
  
  try {
    var sourceSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = sourceSpreadSheet.getSheetByName("Sheet1");
    
    // 获取表单数据（Y2:Y7）并转为一维数组
    var formValues = sheet.getRange("Y2:Y7").getValues().flat();
    var targetId = formValues[0];
    
    // 检查表单是否为空
    if (!targetId) {
      SpreadsheetApp.getUi().alert("Machine #不能为空，请填写后重试");
      return;
    }
    
    // 获取P22到最后一行的所有数据（P列是第16列，取6列：P-U）
    var lastRow = sheet.getLastRow();
    var dataRange = sheet.getRange(22, 16, lastRow > 21 ? lastRow - 21 : 1, 6);
    var allRows = dataRange.getValues();
    var matchFound = false;
    
    // 遍历所有行，更新匹配的记录
    allRows.forEach(function(row, index) {
      if (row[0] === targetId) {
        // 用表单数据替换对应列，保留原数据如果表单对应位置为空
        row = row.map(function(cell, ind) {
          return formValues[ind] !== "" ? formValues[ind] : cell;
        });
        allRows[index] = row;
        matchFound = true;
      }
    });
    
    // 批量更新所有修改后的行
    dataRange.setValues(allRows);
    
    // 如果没有找到匹配项，追加新行
    if (!matchFound) {
      // 整理新行数据，空值保留表单对应值或空
      var newRow = formValues.slice(0,6).map(val => val || "");
      sheet.appendRow(newRow);
    }
    
    SpreadsheetApp.getUi().alert('Settings for Machine #'+ targetId + ' have been updated.');
    SpreadsheetApp.flush();
  } catch (e) {
    SpreadsheetApp.getUi().alert("更新出错：" + e.message);
  } finally {
    lock.releaseLock();
  }
}

关键改动说明 #

• 批量更新所有匹配行：不再用单个变量记录最后一个匹配位置，遍历过程中直接修改所有匹配的行数据，确保符合条件的记录全部被更新
• 优化API调用效率：一次性读取目标区域的所有数据，修改完成后批量写入，减少对Google Sheets服务的调用次数，提升脚本执行速度
• 完善空值处理：保留原逻辑中“表单有值才替换，无值则保留原数据”的规则，同时新增Machine #为空时的提示，避免无效操作
• 添加异常处理：通过try-catch块捕获执行过程中的错误，给出明确的错误提示，防止脚本崩溃
• 锁机制优化：将核心逻辑放在try-finally块中，确保无论脚本执行成功与否，锁都会被释放，避免出现死锁问题

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