Google Sheets中基于App Script的投诉表数据自动填充方案咨询

实现思路 #

核心逻辑是一次性加载生产表数据到内存并加缓存，避免函数方案反复查询整表的性能损耗；通过监听投诉表的单元格选中事件，当检测到设备ID列的单元格被选中且有有效值时，自动匹配生产表数据并填充到指定列。

完整代码 #

// 配置参数，可根据实际表结构调整
const CONFIG = {
  productionSheetName: 'Production',
  complaintsSheetName: 'Complaints',
  // 设备ID列的索引（A列=0，B列=1...）
  idColIndex: 0,
  // 投诉表列 → 生产表列的映射（键：投诉表列索引，值：生产表列索引）
  columnMapping: {
    2: 1,  // 投诉表C列（设备名称）→ 生产表B列
    3: 2,  // 投诉表D列（型号）→ 生产表C列
    4: 4,  // 投诉表E列（供应商）→ 生产表E列
    5: 3   // 投诉表F列（生产日期）→ 生产表D列
  }
};

// 监听单元格选中事件
function onSelectionChange(e) {
  const range = e.range;
  const sheet = range.getSheet();
  
  // 仅处理投诉表的设备ID列单元格
  if (sheet.getName() !== CONFIG.complaintsSheetName || range.columnStart - 1 !== CONFIG.idColIndex) {
    return;
  }
  
  const deviceId = range.getValue().toString().trim();
  if (!deviceId) return; // 空值跳过
  
  // 获取生产表数据（带缓存）
  const productionData = getProductionData();
  const matchedRow = productionData[deviceId];
  
  if (!matchedRow) {
    SpreadsheetApp.getUi().alert(`未找到设备ID：${deviceId} 的匹配数据`);
    return;
  }
  
  // 填充对应列数据
  const row = range.getRow();
  const complaintsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(CONFIG.complaintsSheetName);
  
  Object.entries(CONFIG.columnMapping).forEach(([complaintsColIndex, productionColIndex]) => {
    const targetCell = complaintsSheet.getRange(row, parseInt(complaintsColIndex) + 1);
    targetCell.setValue(matchedRow[productionColIndex]);
  });
}

// 获取生产表数据，并用缓存优化性能
function getProductionData() {
  const cache = CacheService.getScriptCache();
  const cacheKey = 'production_device_data';
  const cachedData = cache.get(cacheKey);
  
  if (cachedData) {
    return JSON.parse(cachedData);
  }
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const productionSheet = ss.getSheetByName(CONFIG.productionSheetName);
  const data = productionSheet.getDataRange().getValues();
  
  // 转成{设备ID: 整行数据}的对象
  const dataMap = {};
  data.forEach(row => {
    const id = row[CONFIG.idColIndex].toString().trim();
    if (id) dataMap[id] = row;
  });
  
  // 缓存数据1小时（可调整）
  cache.put(cacheKey, JSON.stringify(dataMap), 3600);
  return dataMap;
}

代码解析 #

1. 配置参数：CONFIG对象可直接修改表名、列索引和映射关系，适配你的实际表结构。
2. 事件监听：onSelectionChange是Google Sheets内置的简单触发器，无需手动安装，单元格选中状态变化时自动触发。
3. 数据缓存：getProductionData函数将生产表数据转成键值对并缓存1小时，避免每次选中单元格都重新读取整表，大幅提升性能。
4. 匹配填充：检测到有效设备ID后，根据预设的列映射，把生产表对应数据写入投诉表的指定列。

使用说明 #

1. 打开目标Google表格，点击顶部菜单「扩展程序」→「Apps 脚本」。
2. 清空默认代码，粘贴上述完整代码。
3. 根据你的实际表结构调整CONFIG里的参数（比如列索引、映射关系）。
4. 保存脚本（给项目命名，比如「设备数据匹配」），关闭脚本编辑器。
5. 回到投诉表，在设备ID列输入存在的ID并选中该单元格，对应列会自动填充数据。

注意：如果生产表数据有更新，缓存会在1小时后自动失效；如需立即刷新，可手动运行getProductionData函数清空缓存。

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