如何获取使用BigQuery数据的Connected Sheets对应的Spreadsheet ID

方法1：通过BigQuery审计日志查询（最准确） #

如果你之前没找到日志内容，大概率是筛选条件或查询方式不对，按以下步骤操作：

1. 进入Google Cloud控制台的Cloud Logging页面，切换到高级查询模式
2. 输入以下日志查询语句，替换your-project-id和your-dataset-id（如果需要过滤特定数据集）：

SELECT
  JSON_VALUE(protoPayload.serviceData.jobCreateRequest.job.configuration.query.connectionProperties, '$.spreadsheetId') AS spreadsheet_id,
  protoPayload.authenticationInfo.principalEmail AS user_email,
  TIMESTAMP_TRUNC(protoPayload.startTime, DAY) AS last_access_date
FROM
  `your-project-id.cloudaudit_googleapis_com.data_access`
WHERE
  protoPayload.serviceName = 'bigquery.googleapis.com'
  AND protoPayload.methodName = 'google.cloud.bigquery.v2.JobService.InsertJob'
  AND JSON_VALUE(protoPayload.serviceData.jobCreateRequest.job.configuration.query.connectionProperties, '$.spreadsheetId') IS NOT NULL
  -- 可选：过滤特定数据集
  -- AND resource.labels.dataset_id = 'your-dataset-id'
GROUP BY
  spreadsheet_id, user_email, last_access_date
ORDER BY
  last_access_date DESC

3. 运行查询后，结果中的spreadsheet_id列就是所有关联BigQuery数据的表格ID，同时还能看到访问的用户和时间。

方法2：用Google Apps Script遍历域内表格（适用于G Suite/Workspace管理员） #

如果你的用户都在企业域内，可通过脚本批量检查所有表格是否关联BigQuery：

1. 打开Google Apps Script编辑器，新建一个脚本项目
2. 粘贴以下代码：

function findBigQueryConnectedSheets() {
  const connectedSheets = [];
  // 遍历域内所有Google表格
  const spreadsheetFiles = DriveApp.searchFiles('mimeType="application/vnd.google-apps.spreadsheet"');
  
  while (spreadsheetFiles.hasNext()) {
    const file = spreadsheetFiles.next();
    const ssId = file.getId();
    
    try {
      const spreadsheet = SpreadsheetApp.openById(ssId);
      // 获取表格内的所有数据源表
      const dataSourceTables = spreadsheet.getDataSourceTables();
      
      if (dataSourceTables.length > 0) {
        dataSourceTables.forEach(table => {
          const dataSource = table.getDataSource();
          // 判断是否为BigQuery数据源
          if (dataSource.getType() === 'BIGQUERY') {
            connectedSheets.push({
              id: ssId,
              name: file.getName(),
              owner: file.getOwner()?.getEmail() || '未知'
            });
          }
        });
      }
    } catch (error) {
      // 跳过无权限访问的表格
      continue;
    }
  }
  
  // 生成结果报告
  const reportSheet = SpreadsheetApp.create('BigQuery关联表格报告').getActiveSheet();
  reportSheet.appendRow(['Spreadsheet ID', '表格名称', '所有者']);
  connectedSheets.forEach(sheet => {
    reportSheet.appendRow([sheet.id, sheet.name, sheet.owner]);
  });
}

3. 运行脚本前，确保已启用Drive API和Sheets API（脚本编辑器右上角的「编辑器」→「项目设置」→「启用Google API」）
4. 以域管理员身份运行脚本，完成后会自动生成包含所有关联表格信息的报告。

补充说明 #

官方文档提到的审计日志需要关注Data Access类型的日志，而非Admin Activity，并且要通过JSON字段提取spreadsheetId——这是之前可能遗漏的关键点。

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