如何开发Google Sheets跨表脚本实现输入匹配、自动备份清表功能?
电子表格联动脚本开发需求梳理
场景背景
- 处于实习开发任务阶段,已梳理完成大部分需实现的业务逻辑,因编程基础不足无法独立完成全部功能开发
- 涉及3个带唯一ID的独立电子表格,各表定位为:
- SpreadSheet1:存储全量源业务数据
- SpreadSheet2:脚本运行的宿主表格,每个用户持有独立的SpreadSheet2实例
- SpreadSheet3:SpreadSheet2的统一备份表
- 需求配套可视化示例图地址:https://i.sstatic.net/IqPx9.png
核心功能要求
输入联动匹配规则
- 触发条件:在SpreadSheet2的A列或B列输入内容时,脚本自动运行
- 执行逻辑:自动到SpreadSheet1的A列检索完全匹配值
- 匹配成功:根据输入所在列,将SpreadSheet1匹配行的后续业务数据复制到当前输入行的对应位置,同时删除SpreadSheet1中的匹配源行
- 匹配失败:自动清空当前单元格的输入内容
备份重置规则
- 触发条件:在SpreadSheet2的B列输入值为
0时,脚本自动运行 - 执行流程:
- 将SpreadSheet2中除该输入值外的所有现有业务数据,复制到SpreadSheet3的首个空白行位置
- 清空SpreadSheet2中除首行表头外的所有内容
适配要求
- 脚本可直接复制到不同SpreadSheet2实例中复用,无需逐表修改适配
- 支持多用户使用场景:多用户通过各自独立的SpreadSheet2操作同一套SpreadSheet1源数据时,所有备份操作统一向同一个SpreadSheet3写入
现有待优化的半成品代码
function example1(e) { const range = e.range; const sheet = range.getSheet(); if (sheet.getSheetName() != "Sheet1" || range.getColumn() != 1 || range.getRow() == 1) return; const value = range.getValue(); const values = sheet.getRange("A2:A" + sheet.getLastRow()).getValues(); values.splice(range.getRow() - 2, 1); if (values.flat().includes(value)) { range.clearContent(); return; } const srcSheet = e.source.SpreadsheetApp.openById("SSheetID1"); const srcSheet3 = e.source.SpreadsheetApp.openById("SSheetID3"); const range3 = e.range; const sheet = range3.srcSheet3; const srcRange = srcSheet.getRange("A2:A" + srcSheet.getLastRow()).createTextFinder(value).matchEntireCell(true).findNext(); if (srcRange) { srcRange.offset(0, 1, 1, srcSheet.getLastColumn() - 1).copyTo(range.offset(0, 1)); srcRange.offset(0, 1, 1, srcSheet.getLastColumn() - 1).copyTo(range3.offset(0,1)); srcRange.clearContent(); } else { range.clearContent(); return; } }
内容的提问来源于stack exchange,提问作者Prometheus
相关产品推荐
相关产品推荐

