在Apps Script中通过Service Account调用BigQuery API查询VPC项目数据

1. VPC项目执行BigQuery作业是否需要Service Account #

如果你的VPC项目开启了VPC Service Controls、或者配置了个人账号访问BigQuery的权限限制，或者你开发的Data Studio社区连接器需要对所有访问用户统一授权，就必须使用Service Account发起请求。如果你的个人账号已经被加入VPC项目的权限白名单、同时在VPC SC的允许访问主体列表里，也可以用个人身份执行，但可复用性和安全性远低于服务账号方案。

2. Service Account授权配置步骤 #

• 第一步：在VPC所属的GCP项目中创建Service Account，为其分配最小必要权限：roles/bigquery.jobUser（用于提交查询作业）、对应数据集的roles/bigquery.dataViewer（用于读取查询目标表数据），如果开启了VPC Service Controls，需要将该服务账号加入对应访问边界的允许主体列表中。
• 第二步：为该Service Account创建JSON格式的密钥文件，下载保存到本地。
• 第三步：在Apps Script项目中添加OAuth2 for Apps Script库，替换原有内置BigQuery高级服务的调用逻辑，使用服务账号令牌发起API请求，示例代码如下：

// 服务账号授权配置
function getOAuthService() {
  const serviceAccountKey = {
    "private_key": "你下载的JSON密钥里的private_key内容",
    "client_email": "你下载的JSON密钥里的client_email内容"
  };
  return OAuth2.createService('BigQueryServiceAccount')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(serviceAccountKey.private_key)
    .setIssuer(serviceAccountKey.client_email)
    .setScope('https://www.googleapis.com/auth/bigquery')
    .setCache(CacheService.getScriptCache())
    .setProperties(PropertiesService.getScriptProperties());
}

// 改造后的查询方法
function runQuery() {
  const sql = "SELECT Distinct ss_cd FROM `vf-pt-ngbi-dev-gen-03.AEAD_DataSet_test.d_customer` WHERE end_dttm IS NOT NULL";
  const projectId = "你的VPC项目ID";
  const service = getOAuthService();
  if (!service.hasAccess()) {
    Logger.log('授权失败: ' + service.getLastError());
    return;
  }
  // 手动调用BigQuery API
  const apiUrl = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/queries`;
  const payload = JSON.stringify({
    query: sql,
    timeoutMs: 100000,
    useLegacySql: false
  });
  const options = {
    method: 'POST',
    contentType: 'application/json',
    headers: {
      Authorization: 'Bearer ' + service.getAccessToken()
    },
    payload: payload
  };
  try {
    const response = UrlFetchApp.fetch(apiUrl, options);
    const queryResults = JSON.parse(response.getContentText());
    // 把结果存入数组
    const resultArray = queryResults.rows.map(row => row.f[0].v);
    Logger.log(resultArray);
    return resultArray;
  } catch (err) {
    Logger.log(err);
    return;
  }
}

3. 其他可行方案 #

• 方案1：在VPC内部署Cloud Function/Cloud Run服务，将BigQuery查询逻辑封装成HTTP接口，Apps Script通过调用该接口获取查询结果，接口直接返回JSON格式数据，转换为数组即可使用。该方案无需在Apps Script侧配置复杂的服务账号授权，云服务本身运行在VPC内，天然符合网络权限要求。
• 方案2：提前将查询结果定时同步到BigQuery的中间表，Apps Script只需读取中间表数据即可，适合查询逻辑固定、数据实时性要求不高的场景，可减少重复查询的资源消耗。
• 方案3：如果你的VPC配置了VPN或者专线连通本地环境，也可以在本地部署定时任务执行BigQuery查询，将结果同步到Apps Script可访问的存储位置（比如Google Sheets、Cloud Storage），再由Apps Script读取存入数组。

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