如何从Google Apps Script无GCP关联账号访问BigQuery并开放给访客？

没问题，我来帮你理清从Google Apps Script访问BigQuery、同时允许访客获取数据的可行方案，针对你提到的两种需求分别说明：

BigQuery不像传统SQL数据库那样支持IP+用户名密码的直接连接，但我们可以通过服务账号密钥+REST API的方式模拟这种“自主管理连接”的模式，访客无需关联你的GCP项目就能获取数据。具体步骤如下：

• 首先在你的GCP项目中创建一个BigQuery服务账号，给它分配最小必要权限（比如BigQuery Data Viewer，只允许读取数据），避免权限过大带来的风险。
• 下载该服务账号的JSON密钥文件，提取其中的private_key和client_email字段，将它们存储到Apps Script的脚本属性中（不要硬编码在代码里，脚本属性只有你能查看）。
• 在Apps Script中引入OAuth2库（库ID：1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF），通过它生成访问令牌，再调用BigQuery的REST API执行查询。

示例代码：

function fetchBigQueryData() {
  // 从脚本属性读取服务账号信息
  const scriptProps = PropertiesService.getScriptProperties();
  const privateKey = scriptProps.getProperty('BQ_PRIVATE_KEY');
  const clientEmail = scriptProps.getProperty('BQ_CLIENT_EMAIL');
  const projectId = scriptProps.getProperty('BQ_PROJECT_ID');
  
  // 配置OAuth2授权服务
  const bqAuth = OAuth2.createService('BigQueryAccess')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(privateKey.replace(/\\n/g, '\n')) // 处理密钥中的换行符
    .setIssuer(clientEmail)
    .setPropertyStore(scriptProps)
    .setScope('https://www.googleapis.com/auth/bigquery.readonly');
  
  // 检查授权状态
  if (!bqAuth.hasAccess()) {
    throw new Error('获取BigQuery访问权限失败：' + bqAuth.getLastError());
  }
  
  // 执行查询
  const query = 'SELECT * FROM `your-dataset.your-table` LIMIT 100';
  const apiUrl = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/queries`;
  const requestOptions = {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${bqAuth.getAccessToken()}`,
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify({
      query: query,
      useLegacySql: false
    })
  };
  
  const response = UrlFetchApp.fetch(apiUrl, requestOptions);
  return JSON.parse(response.getContentText()).rows;
}

这种方式下，访客运行脚本时，是通过你的服务账号权限访问BigQuery，完全不需要他们关联任何GCP项目，只要服务账号权限足够，就能正常返回数据。

官方的BigQuery Apps Script高级服务确实只能以脚本所有者的身份访问，访客运行会因权限不足被拒绝。但我们可以自己封装REST API调用，做成类似客户端库的可复用函数，让使用体验更接近官方库，同时支持访客访问。

比如你可以封装一个通用的查询函数，在脚本中直接调用：

// 封装的BigQuery客户端函数，可复用
function runBQQuery(projectId, query) {
  const scriptProps = PropertiesService.getScriptProperties();
  const privateKey = scriptProps.getProperty('BQ_PRIVATE_KEY').replace(/\\n/g, '\n');
  const clientEmail = scriptProps.getProperty('BQ_CLIENT_EMAIL');
  
  const bqAuth = OAuth2.createService('BigQueryAccess')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(privateKey)
    .setIssuer(clientEmail)
    .setPropertyStore(scriptProps)
    .setScope('https://www.googleapis.com/auth/bigquery.readonly');
  
  if (!bqAuth.hasAccess()) {
    throw new Error('授权失败：' + bqAuth.getLastError());
  }
  
  const apiUrl = `https://bigquery.googleapis.com/bigquery/v2/projects/${projectId}/queries`;
  const options = {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${bqAuth.getAccessToken()}`,
      'Content-Type': 'application/json'
    },
    payload: JSON.stringify({
      query: query,
      useLegacySql: false
    })
  };
  
  const response = UrlFetchApp.fetch(apiUrl, options);
  return JSON.parse(response.getContentText());
}

// 主函数中调用示例
function getVisitorData() {
  const projectId = 'your-gcp-project-id';
  const query = 'SELECT user_id, user_name FROM `your-dataset.user-table` WHERE active = true';
  const result = runBQQuery(projectId, query);
  // 处理结果并返回给访客
  return result.rows.map(row => row.f.map(field => field.v));
}

你还可以把这个封装函数发布成一个Apps Script库，方便在多个脚本中引用，但一定要确保服务账号密钥只存储在调用库的主脚本中，不要在库代码里暴露密钥。

• 权限最小化：给服务账号只分配需要的权限（比如仅数据读取权限），避免数据泄露风险。
• 密钥安全：绝对不要把服务账号的JSON密钥硬编码到脚本中，必须使用PropertiesService.getScriptProperties()存储，该属性只有脚本所有者能查看。
• 配额管理：BigQuery API有调用配额限制，要确保你的查询不会超出免费配额或GCP项目的配额，避免服务中断。

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