Google Sheets脚本问题：行变动后L列背景色未随值更新

问题背景 #

使用onEdit()脚本根据L列单元格的值，从另一个标签页匹配并设置背景色。但updateClients()脚本在将客户列表复制到DATA、EPHAD、LIVRET等标签页时，仅粘贴值导致背景色无法随单元格值同步移动——比如原L8单元格值为“RDV 1”时背景色为蓝色，新增行后值移至L10，但蓝色仍留在L8，L10保留原有背景色。

已知修复方向：在updateClients()中添加逻辑，当三个标签页完成B4:R区域粘贴后，读取这三个标签页L4:L的值，匹配VALIDATION标签页C6:C对应值的背景色，再为L4:L设置对应颜色。

原脚本问题 #

原脚本尝试直接复制CLIENTS标签页的背景色，但存在范围错误，且未实现“值匹配颜色”的核心逻辑，导致背景色与值脱节。

修改后的完整脚本 #

function updateClients() {
  const sss = SpreadsheetApp.openById('1NTSTb8MlLyncgaEAPY5GwEeRY0qGy7B8UbuzjpiXZEI');
  SpreadsheetApp.getActive().toast("", "⏳ Chargement des Leads en Cours...",  -1);
  const sh = sss.getSheetByName("CLIENTS");
  const vs = sh.getRange("B4:R" + getColumnHeight(2,sh,sss)).getValues();

  const ss = SpreadsheetApp.getActive();
  const csh = ss.getSheetByName("COMMENTAIRES");
  csh.getRange(4,2,csh.getLastRow() - 3,csh.getLastColumn() - 1).clearContent();
  csh.getRange(4,2,vs.length,vs[0].length).setValues(vs);

  // 构建值与背景色的映射表（从VALIDATION标签页读取）
  const validationSheet = ss.getSheetByName("VALIDATION");
  const validationRowCount = getColumnHeight(3, validationSheet, ss);
  const validationValues = validationSheet.getRange(`C6:C${6 + validationRowCount - 1}`).getValues().flat();
  const validationColors = validationSheet.getRange(`C6:C${6 + validationRowCount - 1}`).getBackgrounds().flat();
  const colorMap = new Map();
  validationValues.forEach((val, index) => {
    if (val) colorMap.set(val, validationColors[index]);
  });

  // 按C列值分组处理目标标签页
  let oObj = { sA: [] };
  vs.forEach((r, i) => {
    if (!oObj.hasOwnProperty(r[1])) {
      oObj.sA.push(r[1]);
      oObj[r[1]] = [];
      oObj[r[1]].push(r);
    } else {
      oObj[r[1]].push(r)
    }
  });

  oObj.sA.forEach(n => {
    let targetSheet = ss.getSheetByName(n);
    if (!targetSheet) return;

    // 清空原有内容
    if (targetSheet.getLastRow() > 3) {
      targetSheet.getRange(4, 2, targetSheet.getLastRow() - 3, targetSheet.getLastColumn() - 1).clearContent();
    }
    
    // 粘贴新值
    targetSheet.getRange(4, 2, oObj[n].length, oObj[n][0].length).setValues(oObj[n]);

    // 同步L列背景色
    const targetLRange = targetSheet.getRange(4, 12, oObj[n].length, 1); // L列为第12列
    const targetLValues = targetLRange.getValues().flat();
    const targetColors = targetLValues.map(val => colorMap.get(val) || "#ffffff"); // 无匹配时用白色
    targetLRange.setBackgrounds(targetColors.map(color => [color]));
  });

  // 同步COMMENTAIRES标签页的L列背景色
  const commentLRange = csh.getRange(4, 12, vs.length, 1);
  const commentLValues = commentLRange.getValues().flat();
  const commentColors = commentLValues.map(val => colorMap.get(val) || "#ffffff");
  commentLRange.setBackgrounds(commentColors.map(color => [color]));

  SpreadsheetApp.getActive().toast("", "✅ Chargement des Leads Terminé");
}

function getColumnHeight(col, sh, ss) {
  var ss = ss || SpreadsheetApp.getActive();
  var sh = sh || ss.getActiveSheet();
  var col = col || sh.getActiveCell().getColumn();
  var rcA = [];
  if (sh.getLastRow()){ rcA = sh.getRange(1, col, sh.getLastRow(), 1).getValues().flat().reverse(); }
  let s = 0;
  for (let i = 0; i < rcA.length; i++) {
    if (rcA[i].toString().length == 0) {
      s++;
    } else {
      break;
    }
  }
  return rcA.length - s;
}

核心修改点 #

• 颜色映射表：从VALIDATION标签页的C6:C区域提取值和对应背景色，存入Map结构，实现高效的颜色匹配。
• 移除无效代码：删除原脚本中直接复制CLIENTS标签页背景色的错误逻辑，避免范围不匹配问题。
• 批量设置背景色：在每个目标标签页粘贴值后，提取L列所有值，通过映射表匹配颜色，批量设置背景色，确保值与颜色一一对应。
• 兼容COMMENTAIRES标签页：单独处理该标签页的L列颜色同步，保持逻辑一致性。

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