BigQuery-Google Sheet权限问题：服务账号无法访问受限表格的解决方案

方案1：通过有权限用户的脚本定期同步到BigQuery内部表 #

• 核心逻辑：找拥有Sheet访问权限的内部用户，编写脚本定时将Sheet数据同步到BigQuery内部表，后续通过内部表给授权用户开放访问。
• 具体实现：
  • Google Apps Script 方式：绑定到目标Sheet，编写脚本读取数据，调用BigQuery API将数据写入指定内部表，设置每日时间触发器自动执行。需确保该用户账号拥有BigQuery数据集的写入权限。
  • 本地脚本（Python）方式：用gspread库读取Sheet数据，转成DataFrame后通过google-cloud-bigquery库写入内部表，借助cron（Linux）或任务计划程序（Windows）定时运行脚本。认证时使用有权限用户的OAuth2凭据。
• 优势：内部表权限可独立管控，完全避开Sheet的权限限制；数据同步后，授权用户可直接通过BigQuery访问，无需接触原始Sheet。
• 注意事项：需处理全量/增量同步逻辑，避免重复数据；定期检查脚本执行状态，防止用户账号权限变更导致同步失败。

方案2：用Cloud Dataflow/Cloud Functions 配合代理用户中转 #

• 核心逻辑：创建一个在允许域内、拥有Sheet访问权限的代理用户，让GCP托管服务以该用户身份读取Sheet数据，写入BigQuery内部表。
• 具体实现：
  • Cloud Dataflow：构建数据管道，使用代理用户的OAuth凭据连接Sheet，读取数据后直接写入BigQuery内部表，配置每日定时触发作业。
  • Cloud Functions：编写函数实现Sheet数据读取与BigQuery写入逻辑，设置Cloud Scheduler定时触发函数执行。
• 优势：托管在GCP上，稳定性更高，适合长期运行的同步任务；可在管道中加入数据清洗、验证逻辑，提升数据质量。

方案3：Sheet自动导出到Cloud Storage，再创建BigQuery外部表 #

• 核心逻辑：让有权限用户配置Sheet自动导出到Cloud Storage，再基于存储桶中的文件创建BigQuery外部表，通过管控存储桶权限实现访问控制。
• 具体实现：
  • 用Google Apps Script定时将Sheet导出为CSV/Parquet格式，上传到指定Cloud Storage桶；或通过Sheet的「发布到Web」功能定时生成可访问的文件链接，再同步到存储桶。
  • 给BigQuery服务账号或授权用户设置Cloud Storage桶的「存储对象查看者」权限，然后创建BigQuery外部表指向存储桶中的文件。
• 优势：绕过Sheet直接授权限制，通过Cloud Storage中转实现权限隔离；无需全量同步数据，外部表可直接读取最新导出文件。
• 注意事项：需管理导出文件的版本，避免读取旧数据；确保导出格式符合BigQuery要求（如CSV表头与字段类型匹配）。

方案4：授权用户定时执行查询并共享结果表 #

• 核心逻辑：由拥有Sheet访问权限的用户编写查询，直接读取Sheet数据（借助其权限可访问的外部表），定时将查询结果写入共享内部表，供其他授权用户访问。
• 具体实现：
  • 有权限用户创建BigQuery脚本，查询Sheet外部表并将结果插入到共享内部表，通过BigQuery的预定查询功能每日执行。
• 优势：无需额外工具，仅用BigQuery自身功能即可实现；适合只需定期获取最新数据的场景。

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