如何通过脚本快速为Google Sheets中不同值的单元格设置对应批注

核心优化逻辑是减少 Spreadsheet 服务的API调用次数，所有读写操作尽量批量完成，避免逐值、逐单元格调用接口，这是替代textFinder慢问题的核心方案。

实现步骤 #

1. 一次性从VLANS表读取VLAN值和对应的AH列批注内容，在内存中构建键值对映射表，避免后续反复查询表格
2. 一次性读取Network Patching表所有单元格的值到内存数组
3. 遍历单元格数组，对照映射表匹配批注内容，生成对应大小的批注数组
4. 调用setComments接口一次性将所有批注写入表格，仅需1次API调用

完整修改后代码 #

function couleursAndComments() {
  var ss = SpreadsheetApp.getActive();
  var NP = ss.getSheetByName("Network Patching");
  var VLANS = ss.getSheetByName("VLANS");
  var conditionalFormatRules = []; // 直接新建规则数组，不需要先读再清空，减少一次API调用

  // 一次性读取VLANS表需要的所有数据：F列（VLAN ID）、背景色、字体颜色、AH列（批注内容）
  const vlanDataRange = VLANS.getRange(3, 6, VLANS.getLastRow() - 2, 29); // 从F列(6)到AH列(34)，共29列
  const vlanValues = vlanDataRange.getValues();
  const vlanBackgrounds = vlanDataRange.getBackgrounds();
  const vlanFontColors = vlanDataRange.getFontColors();

  // 构建VLAN ID到批注、背景色、字体颜色的映射表
  const vlanMap = {};
  for (let i = 0; i < vlanValues.length; i++) {
    const vlanId = vlanValues[i][0];
    const comment = vlanValues[i][28]; // AH列在当前读取范围内的索引为28（数组从0开始，F列是索引0）
    const bgColor = vlanBackgrounds[i][0];
    const fontColor = vlanFontColors[i][0];
    if (vlanId && comment && bgColor !== "#ffffff") {
      vlanMap[vlanId] = {
        comment: comment,
        bg: bgColor,
        font: fontColor
      };
      // 同时生成条件格式规则，直接push到数组
      conditionalFormatRules.push(
        SpreadsheetApp.newConditionalFormatRule()
          .setRanges([NP.getRange(1, 1, NP.getLastRow(), NP.getLastColumn())])
          .whenTextEqualTo(vlanId)
          .setBackground(bgColor)
          .setFontColor(fontColor)
          .build()
      );
    }
  }

  // 一次性写入所有条件格式规则，仅1次API调用
  NP.setConditionalFormatRules(conditionalFormatRules);

  // -------------------- 批量添加批注部分 --------------------
  // 读取NP表所有单元格值
  const npRange = NP.getRange(1, 1, NP.getLastRow(), NP.getLastColumn());
  const npValues = npRange.getValues();
  // 初始化批注数组，和单元格数组大小一致
  const commentsArray = npValues.map(row => row.map(cell => vlanMap[cell]?.comment || null));
  // 一次性写入所有批注，仅1次API调用
  npRange.setComments(commentsArray);
}

注意：如果你的VLANS表中批注所在列不是AH列，可自行调整代码中comment = vlanValues[i][xxx]的索引值即可。

性能说明 #

相比textFinder逐值遍历全表的实现，该方案的时间复杂度仅为O(单元格总数 + VLAN总数)，数据量越大性能优势越明显，对于万级单元格的表格，运行速度可提升10倍以上。

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