Google Sheets匹配唯一键将表单响应数据同步到Dashboard面板实现方法

完整可运行代码 #

function refreshDashboard() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  // 获取两个目标工作表
  const commentSheet = ss.getSheetByName("Comments");
  const dashboardSheet = ss.getSheetByName("Dashboard");

  // 读取Comments表B2:H区间数据，过滤B列空的无效行
  const commentValues = commentSheet.getRange("B2:H").getValues().filter(row => row[0] !== "");
  // 读取Dashboard表A1的匹配唯一键
  const targetKey = dashboardSheet.getRange("A1").getValue().toString();
  // 筛选所有匹配唯一键的行
  const matchedRows = commentValues.filter(row => row[0].toString() === targetKey);

  // 清空原有填充区域的旧数据
  dashboardSheet.getRange("G3:M20").clearContent();

  // 匹配到数据则写入Dashboard
  if (matchedRows.length > 0) {
    // 匹配多少行就写入多少行，列数固定为7列对应G-M
    const writeRange = dashboardSheet.getRange(3, 7, matchedRows.length, 7);
    writeRange.setValues(matchedRows);
  }
}

// 编辑自动触发逻辑
function onEdit(e) {
  // 仅在编辑Dashboard表A1单元格时执行刷新
  if (e.source.getActiveSheet().getName() === "Dashboard" && e.range.getA1Notation() === "A1") {
    refreshDashboard();
  }
}

使用步骤 #

• 打开Google表格顶部菜单栏的「扩展程序」→「Apps Script」，删除原有代码后粘贴上述代码，点击保存按钮
• 首次运行需要给脚本授权，按照页面提示完成授权即可
• 回到表格页面，修改Dashboard工作表A1的下拉选项，G3:M区间会自动刷新匹配到的数据

注意事项 #

• G3:M20最多可容纳18行数据，若匹配到的行数超过18行，超出部分不会写入，可根据实际需求修改代码中的清空区域范围调整上限
• 日期写入后格式不符合预期的话，手动选中G:M列设置对应单元格格式即可
• 若不需要自动触发，也可手动在Apps Script编辑器中选中refreshDashboard函数运行刷新数据

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