Google Sheets中是否有方法可追踪关联表格及对应连接信息？

Google Sheets没有原生的全局IMPORTRANGE依赖统计功能，你可以通过Google Apps Script实现需求A和需求B，操作方法如下：

实现脚本 #

打开任意Google Sheets，点击顶部菜单栏「扩展程序」→「Apps Script」进入脚本编辑器，替换默认代码为以下内容：

// 替换为你要排查的目标表格ID（表格URL中d/和/edit之间的字符串）
const TARGET_SPREADSHEET_ID = "输入你要查询的表格ID";
// 扫描结果会自动生成新表格存储
const resultSheet = SpreadsheetApp.create("IMPORTRANGE关联扫描结果");
const resultTab = resultSheet.getActiveSheet();
resultTab.appendRow(["关联表格名称", "关联表格链接", "当前表格匹配的连接数"]);

function scanImportRangeDependencies() {
  let totalConnectionCount = 0;
  // 拉取当前账号下所有Google Sheets文件
  const sheetsFiles = DriveApp.getFilesByType(MimeType.GOOGLE_SHEETS);

  while (sheetsFiles.hasNext()) {
    const currentFile = sheetsFiles.next();
    let currentFileMatchCount = 0;

    try {
      const currentSs = SpreadsheetApp.openById(currentFile.getId());
      const allSheets = currentSs.getSheets();

      // 遍历当前文件所有工作表的所有公式
      for (const sheet of allSheets) {
        const allFormulas = sheet.getDataRange().getFormulas();
        for (const row of allFormulas) {
          for (const formula of row) {
            // 匹配指向目标ID的IMPORTRANGE函数
            if (formula.includes("IMPORTRANGE") && formula.includes(TARGET_SPREADSHEET_ID)) {
              currentFileMatchCount++;
              totalConnectionCount++;
            }
          }
        }
      }

      // 存在关联则写入结果表
      if (currentFileMatchCount > 0) {
        resultTab.appendRow([currentFile.getName(), currentFile.getUrl(), currentFileMatchCount]);
      }
    } catch (err) {
      // 自动跳过无访问权限的表格
      continue;
    }
  }

  // 写入总连接数统计
  resultTab.appendRow(["总连接数", "", totalConnectionCount]);
  console.log("扫描完成，结果表地址：" + resultSheet.getUrl());
}

操作步骤 #

• 替换代码中TARGET_SPREADSHEET_ID的取值为你要排查的表格ID，比如统计SheetX的关联就填SheetX的ID，统计SheetY的关联就填SheetY的ID
• 点击编辑器顶部的运行按钮，首次运行需要按照提示授权脚本访问你的Google Drive和Google Sheets权限
• 运行完成后会自动生成名为IMPORTRANGE关联扫描结果的新表格，里面包含所有关联表格的清单和总连接数，同时满足你的两个需求

迁移验证方法 #

针对SheetX替换为SheetY的场景，按以下步骤操作即可完成验证：

1. 先将TARGET_SPREADSHEET_ID填为SheetX的ID，运行脚本导出所有关联SheetX的表格清单，作为待修改列表
2. 完成所有关联表格的数据源切换后，再运行两次脚本：
   • 第一次填写SheetX的ID，确认总连接数为0，没有残留的旧数据源连接
   • 第二次填写SheetY的ID，确认总连接数和关联表格数量和之前的待修改列表一致，所有关联都已经切换到新数据源

注意事项 #

• 脚本只会扫描单元格内直接写入的IMPORTRANGE函数，如果你的团队使用了命名函数、脚本动态生成公式等场景，需要自行调整扫描逻辑
• 请使用拥有所有团队表格查看权限的账号运行脚本，无权限的表格会被自动跳过，可能导致统计遗漏
• 团队表格数量较多时，脚本运行时间可能较长，建议按共享盘目录拆分扫描范围调整脚本

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