Google Sheets使用IMPORTRANGE跨表导入如何避免源表删数据影响目标表

IMPORTRANGE属于动态引用函数，所有展示内容完全依赖源表数据的存在，只要源表删除对应行，引用端就会同步丢失数据，无法满足静态存档的需求。你可以通过Google Apps Script实现自动复制、数据永久存档的需求，具体操作如下：

操作步骤 #

• 打开你的Sheet1（待采购物资表），点击顶部菜单栏「扩展程序」-「Apps 脚本」，进入脚本编辑页面。
• 把原有默认代码全部删除，粘贴下方自定义脚本：

function autoArchiveCompletedOrders() {
  // 以下参数请根据你的实际表格情况修改
  const SOURCE_SPREADSHEET_ID = "Sheet1的文档ID，从浏览器地址栏d/和/edit之间的内容复制";
  const SOURCE_SHEET_NAME = "Sheet1";
  const TARGET_SPREADSHEET_ID = "Sheet2的文档ID，获取方式同上";
  const TARGET_SHEET_NAME = "Sheet2";
  const ORDER_NO_COLUMN = 1; // 订单号所在列，A列填1、B列填2，以此类推
  const STATUS_COLUMN = 4; // 下单状态所在列，示例为D列，可自行修改
  const COMPLETED_FLAG = "已完成"; // 标记下单完成的状态值
  const HEADER_ROW_COUNT = 1; // 表头行数，不需要同步表头就填1

  // 读取源表数据
  const sourceSs = SpreadsheetApp.openById(SOURCE_SPREADSHEET_ID);
  const sourceSheet = sourceSs.getSheetByName(SOURCE_SHEET_NAME);
  const sourceAllRows = sourceSheet.getDataRange().getValues();
  // 读取目标表已有订单号，避免重复存档
  const targetSs = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID);
  const targetSheet = targetSs.getSheetByName(TARGET_SHEET_NAME);
  const existedOrderNos = targetSheet.getDataRange().getValues().map(row => row[ORDER_NO_COLUMN - 1]);

  const archiveRows = [];
  const deleteSourceRowNums = [];
  // 遍历筛选已完成、未存档的订单
  for (let i = HEADER_ROW_COUNT; i < sourceAllRows.length; i++) {
    const currentRow = sourceAllRows[i];
    const orderNo = currentRow[ORDER_NO_COLUMN - 1];
    const orderStatus = currentRow[STATUS_COLUMN - 1];
    if (orderStatus === COMPLETED_FLAG && !existedOrderNos.includes(orderNo)) {
      archiveRows.push(currentRow);
      deleteSourceRowNums.push(i + 1); // 表格行号从1开始计数，所以索引+1
    }
  }

  // 批量写入存档表
  if (archiveRows.length > 0) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, archiveRows.length, archiveRows[0].length).setValues(archiveRows);
  }

  // 批量删除源表已存档行，不需要自动删除可以注释掉下方代码块
  if (deleteSourceRowNums.length > 0) {
    deleteSourceRowNums.reverse().forEach(rowNum => sourceSheet.deleteRow(rowNum));
  }
}

• 保存脚本，为项目自定义命名，按照提示授权脚本访问你的表格权限。
• 点击脚本编辑器左侧的「触发器」按钮，选择「添加触发器」，按需求配置触发规则：
  • 选择运行函数：autoArchiveCompletedOrders
  • 选择事件源：如果需要修改状态后立刻同步，选「电子表格」-「编辑时」；如果不需要太高实时性，选「时间驱动」-「小时计时器」，设置每15分钟/1小时运行一次即可。

注意事项 #

• 写入Sheet2的数据是静态固定值，后续修改、删除Sheet1的所有内容都不会影响已存档的数据，不会出现同步删除的问题。
• 如果不需要脚本自动删除Sheet1的已完成行，注释掉代码中批量删除的代码块即可，手动删除Sheet1数据也不会影响存档。

内容的提问来源于stack exchange，提问作者Rach.H