如何为Google Sheets插件用户配置专属Big Query视图的关联工作表访问权限

核心思路 #

依托Google Sheets的关联工作表功能，结合BigQuery参数化查询与Google Apps Script的权限控制，让用户通过侧边栏按钮生成仅包含自身数据的关联视图，全程数据保留在我方BigQuery项目，不生成基础表格。

具体实现步骤 #

1. BigQuery侧配置 #

• 确保目标数据集/表格包含唯一用户标识字段（如user_id，可绑定用户Google邮箱或自定义ID）。
• （可选但推荐）给目标表格配置行级权限（RLS）：创建权限规则，限制仅当user_id匹配请求身份时才能访问对应行，作为双重安全保障，防止查询被篡改导致数据泄露。

2. Google Apps Script插件开发 #

前置准备

在Apps Script编辑器中启用两个高级服务：

• BigQuery API
• Sheets API

后端脚本（处理查询与关联工作表创建）

function showMyData() {
  // 获取当前用户唯一标识（此处用Google邮箱，可替换为自定义用户ID）
  const userId = Session.getActiveUser().getEmail();
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const ourProjectId = "your-project-id"; // 替换为我方BigQuery项目ID
  const targetTable = "your-dataset.your-table"; // 替换为目标数据集和表名

  // 构建带参数的BigQuery查询，仅返回当前用户数据
  const query = `SELECT * FROM \`${ourProjectId}.${targetTable}\` WHERE user_id = @user_id`;
  const queryParams = [
    {
      name: "user_id",
      parameterType: { type: "STRING" },
      parameterValue: { value: userId }
    }
  ];

  // 配置BigQuery查询请求（使用标准SQL）
  const bqRequest = BigQuery.newQueryRequest()
    .setQuery(query)
    .setQueryParameters(queryParams)
    .setUseLegacySql(false);

  // 执行查询（使用我方项目权限，需确保部署时用服务账号）
  BigQuery.Jobs.query(bqRequest, ourProjectId);

  // 创建新工作表（命名区分用户与时间）
  const newSheet = activeSpreadsheet.insertSheet(`我的数据_${Date.now()}`);
  const sheetId = newSheet.getSheetId();

  // 配置关联数据源，将BigQuery查询绑定到新工作表
  const dataSourceConfig = {
    dataSource: {
      bigQueryDataSource: {
        projectId: ourProjectId,
        query: query,
        parameters: queryParams,
        useStandardSql: true
      }
    },
    sheetId: sheetId,
    startRow: 0,
    startColumn: 0
  };

  // 创建关联工作表
  Sheets.Spreadsheets.DataSources.create(dataSourceConfig, activeSpreadsheet.getId());

  // 给用户反馈
  SpreadsheetApp.getUi().alert("专属数据工作表已创建完成！");
}

侧边栏UI（触发按钮）

创建sidebar.html文件：

<!DOCTYPE html>
<html>
  <body style="padding: 16px;">
    <button style="padding: 8px 16px; cursor: pointer;" onclick="loadMyData()">查看我的专属数据</button>
    <script>
      function loadMyData() {
        google.script.run
          .withSuccessHandler(() => window.close())
          .withFailureHandler(err => alert(`加载失败：${err.message}`))
          .showMyData();
      }
    </script>
  </body>
</html>

添加打开侧边栏的函数：

function openSidebar() {
  const html = HtmlService.createHtmlOutputFromFile("sidebar")
    .setTitle("我的数据");
  SpreadsheetApp.getUi().showSidebar(html);
}

3. 插件部署与权限配置 #

• 部署插件时选择以开发者身份运行，使用我方项目的服务账号，确保该服务账号拥有BigQuery的BigQuery Data Viewer权限（访问目标数据集/表）。
• 根据需求设置插件的授权范围（如仅限组织内用户或公开），确保用户能正常授权使用。

约束条件满足验证 #

• 专属视图：通过参数化查询+可选的BigQuery行级权限，确保每个用户仅能查看自身数据，即使插件被恶意调试，也无法获取他人数据。
• 数据存储：关联工作表仅作为实时视图，数据始终存储在我方BigQuery项目，不会复制到用户的Google Sheets中。
• 无基础表格：创建的是关联数据源绑定的工作表，而非普通单元格写入的基础表格，完全符合需求范围。

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