Google Sheets脚本需求：记录列中历史唯一值并同步至另一工作表

核心需求 #

某列通过importrange每月动态更新，需要记录该列全量历史唯一值：每月数据更新时，将新数据中的新增唯一值追加到指定列，同时保留过往所有历史唯一值，而非仅覆盖为当前实时唯一值。

修改后的脚本 #

// 获取二维数组的唯一值（扁平化后去重，过滤空值）
const getUnique_ = array2d => [...new Set(array2d.flat().filter(val => val !== ''))];

// 追加新增唯一值到历史列
const appendNewUniqueValues = (sourceSheetName = 'Sheet1', sourceColumn = 1, historyColumn = 2, headers = 1) => {
  const ss = SpreadsheetApp.getActive();
  const sourceSheet = ss.getSheetByName(sourceSheetName);
  
  // 1. 获取当前数据源的所有值并去重
  const sourceRange = sourceSheet.getRange(1 + headers, sourceColumn, sourceSheet.getLastRow() - headers, 1);
  const sourceValues = sourceRange.getValues();
  const currentUniqueValues = getUnique_(sourceValues);

  // 2. 获取历史已存储的唯一值
  const historyLastRow = sourceSheet.getRange(1 + headers, historyColumn).getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();
  const historyRange = sourceSheet.getRange(1 + headers, historyColumn, historyLastRow - headers, 1);
  const historyValues = historyRange.getValues();
  const existingUniqueValues = getUnique_(historyValues);

  // 3. 筛选出当前数据中未在历史里出现的新增值
  const newUniqueValues = currentUniqueValues.filter(val => !existingUniqueValues.includes(val));

  // 4. 追加新增值到历史列的末尾
  if (newUniqueValues.length > 0) {
    // 确定写入起始行：历史列无数据则从表头下一行开始，否则从历史最后一行+1开始
    const startRow = (historyLastRow === 1 + headers && sourceSheet.getRange(historyLastRow, historyColumn).getValue() === '') 
      ? 1 + headers 
      : historyLastRow + 1;
    // 转换为适配setValues的二维数组格式
    const newValuesToWrite = newUniqueValues.map(val => [val]);
    // 写入数据
    sourceSheet.getRange(startRow, historyColumn, newValuesToWrite.length, 1).setValues(newValuesToWrite);
  }
};

脚本说明 #

1. 去重逻辑优化：新增空值过滤，避免空单元格被误判为唯一值存入历史列。
2. 历史值读取：先获取历史列已有的所有唯一值，作为判断新增值的基准。
3. 新增值筛选：通过数组过滤，只保留当前数据中未在历史列出现过的内容。
4. 追加写入：自动定位历史列的最后一行，将新增值追加到末尾，完全保留原有历史数据。
5. 兼容初始状态：如果历史列一开始没有数据，会自动从表头下第一行开始写入。

使用方法 #

1. 打开你的Google Sheets，点击「扩展程序」→「Apps脚本」，替换原有代码为上述内容。
2. 可以手动运行appendNewUniqueValues函数，或者设置时间驱动触发器（每月触发一次，匹配数据更新周期）。
3. 参数可按需调整：
   • sourceSheetName：数据源所在工作表名称（默认Sheet1）
   • sourceColumn：动态更新的数据源列（默认第1列，即A列）
   • historyColumn：存储历史唯一值的列（默认第2列，即B列）
   • headers：表头行数（默认1行）

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