Apps Script批量替换优化：限定L2:P100范围提速求助

原代码遍历工作表所有单元格导致运行缓慢，且尝试限定L2:P100区域时频繁出现dataRange空值、未定义错误。以下是修正后的代码，既实现范围限定，又通过批量读写大幅提升效率：

修改后代码 #

function FindReplace() {
  // 指定要处理的工作表名称
  const targetSheets = ["firstsheet", "secondsheet"];
  // 查找替换字典
  const findReplaceMap = {
    "AA": "Done",
    "BB": "Check",
    "CC": "Complete"
  };
  // 限定处理范围：L2:P100（行从2到100，列L是第12列，P是第16列）
  const startRow = 2;
  const endRow = 100;
  const startCol = 12;
  const endCol = 16;

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // 遍历每个目标工作表
  targetSheets.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    if (!sheet) return; // 跳过不存在的工作表
    
    // 获取指定范围的所有数据（批量读取，减少API调用）
    const range = sheet.getRange(startRow, startCol, endRow - startRow + 1, endCol - startCol + 1);
    const values = range.getValues();
    const formulas = range.getFormulas(); // 获取公式，用于跳过带公式的单元格
    
    // 遍历查找替换字典的每个键值对
    Object.entries(findReplaceMap).forEach(([findStr, replaceStr]) => {
      // 遍历范围的每一行每一列
      for (let row = 0; row < values.length; row++) {
        for (let col = 0; col < values[row].length; col++) {
          // 如果单元格有公式，跳过处理
          if (formulas[row][col]) continue;
          
          let cellValue = values[row][col];
          // 只处理字符串类型的值，避免非字符串报错
          if (typeof cellValue === 'string') {
            values[row][col] = cellValue.replace(findStr, replaceStr);
          }
        }
      }
    });
    
    // 批量写入修改后的数据（仅一次API调用，大幅提升速度）
    range.setValues(values);
  });
}

关键修改说明 #

• 范围限定：明确指定L2:P100对应的行列索引（L是第12列，P是第16列，行从2到100），通过getRange(startRow, startCol, rowCount, colCount)精准获取目标区域
• 批量读写：用getValues()一次性读取所有单元格值，修改后用setValues()一次性写入，避免原代码逐单元格调用getValue()/setValue()的频繁API请求（这是速度慢的核心原因）
• 错误规避：
  • 增加工作表存在性判断，避免因工作表名称错误导致的空值报错
  • 提前获取公式数组，跳过带公式的单元格，保留原逻辑
  • 只处理字符串类型的值，避免非字符串（如数字、日期）调用replace()报错
• 逻辑简化：用forEach替代原代码的for循环，更简洁易读；移除原代码中冗余的变量定义（如重复定义的r、num_rows等）

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