使用后端用户授权Google App Script请求BigQuery的技术咨询

核心思路 #

放弃让每个用户以自身权限访问BigQuery，改用Google Cloud服务账号作为统一执行身份，所有脚本操作都通过该服务账号完成——既避免逐一授权的繁琐，又能通过服务账号的权限严格控制数据访问范围。

可行方案及实操步骤 #

方案1：直接在Apps Script中使用服务账号（推荐） #

通过OAuth2库模拟服务账号身份，绕过默认的当前用户权限，直接调用BigQuery API：

1. 创建服务账号：在Google Cloud控制台「IAM与管理→服务账号」创建新账号，分配所需的最小BQ权限（如特定数据集的数据查看者、作业用户），下载JSON密钥文件。
2. 导入OAuth2库：在Apps Script编辑器中，点击「资源→库」，输入库ID 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF，选择最新版本并保存。
3. 配置服务账号认证：将JSON密钥中的private_key和client_email存入脚本属性（「项目设置→脚本属性」），避免硬编码。
4. 编写认证及BQ调用代码：

function getBQServiceAccount() {
  const props = PropertiesService.getScriptProperties();
  return OAuth2.createService('BQ_Service')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(props.getProperty('BQ_PRIVATE_KEY'))
    .setIssuer(props.getProperty('BQ_CLIENT_EMAIL'))
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope('https://www.googleapis.com/auth/bigquery');
}

// 示例：用服务账号执行查询并写入Sheet
function runBQQuery() {
  const service = getBQServiceAccount();
  if (!service.hasAccess()) throw new Error('认证失败：' + service.getLastError());

  const queryPayload = JSON.stringify({
    query: 'SELECT * FROM `your-project.your-dataset.your-table` LIMIT 100',
    useLegacySql: false
  });

  const response = UrlFetchApp.fetch(
    'https://bigquery.googleapis.com/bigquery/v2/projects/your-project/queries',
    {
      method: 'POST',
      headers: { Authorization: 'Bearer ' + service.getAccessToken() },
      contentType: 'application/json',
      payload: queryPayload
    }
  );

  const result = JSON.parse(response.getContentText());
  // 将结果解析后写入当前Sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  sheet.clearContents();
  // 写入表头
  const headers = result.schema.fields.map(field => field.name);
  sheet.appendRow(headers);
  // 写入数据行
  result.rows.forEach(row => sheet.appendRow(row.f.map(cell => cell.v)));
}

5. 权限管控：只需确保服务账号拥有所需的最小BQ权限，所有使用脚本的用户无需任何BQ权限，只需能运行Apps Script即可（可通过Google Workspace组管控脚本访问）。

方案2：用Cloud Functions作为中间层 #

如果需要更复杂的逻辑或隔离BQ细节，可将BQ操作封装到Cloud Functions中，Apps Script仅调用HTTP接口：

• 在Cloud Functions中创建HTTP触发的函数，默认使用服务账号身份执行BQ操作。
• 配置Cloud Functions的IAM权限，允许特定用户/组调用该函数。
• Apps Script中通过UrlFetchApp调用云函数接口，无需直接访问BQ。

方案3：过渡方案——行级权限（Row-Level Security） #

如果暂时无法切换到服务账号，可在BigQuery中配置行级权限，限制每个用户只能访问特定范围的数据，但此方案无法解决逐一授权的工作量问题，仅能降低越权风险。

关键注意事项 #

• 最小权限原则：服务账号仅分配完成任务所需的最低BQ权限，避免赋予全局权限。
• 密钥安全：服务账号私钥必须存储在脚本属性或Secret Manager中，绝对不能硬编码在代码里。
• 脚本访问管控：通过Google Workspace组或Apps Script的共享设置，限制能运行脚本的用户范围。

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