如何通过AppScript借助服务账号将Google Sheets数据推送至BigQuery

背景与疑问 #

我创建了一份将被复制80份的Google Sheet，供各门店录入运营数据。数据通过Sheet内的按钮触发AppScript推送至BigQuery，现有脚本运行正常，但我不想为80个用户逐一分配BigQuery访问权限，希望使用服务账号。作为新手，我有以下疑问：

1. 是否需要在GCP IAM中创建该服务账号？
2. 是否需要在IAM中为其分配权限？
3. 若为服务账号赋予Google Sheet编辑权限，我是否需要修改AppScript以使用该服务账号，否则会出现权限错误？

现有脚本代码 #

/**
 * Loads the content of a Google Drive Spreadsheet into BigQuery
 */
 
function loadCogsPlayupHistory() {
  // Enter BigQuery Details as variable.
  var projectId = 'myproject';
  // Dataset
  var datasetId = 'my_Dataset';
  // Table
  var tableId = 'my table';
    
  // WRITE_APPEND: If the table already exists, BigQuery appends the data to the table.
  var writeDispositionSetting = 'WRITE_APPEND';
  
  // The name of the sheet in the Google Spreadsheet to export to BigQuery:
  var sheetName = 'src_cogs_playup_current';
  Logger.log(sheetName)
  
  var file = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('src_cogs_playup_current');
  Logger.log(file)
  // This represents ALL the data
  var rows = file.getDataRange().getValues();
  var rowsCSV = rows.join("\n");
  var blob = Utilities.newBlob(rowsCSV, "text/csv");
  var data = blob.setContentType('application/octet-stream');
  Logger.log(rowsCSV)
  
  // Create the data upload job. 
  var job = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1,
        writeDisposition: writeDispositionSetting
      }
    }
  };
  Logger.log(job)

  
  // send the job to BigQuery so it will run your query  
  var runJob = BigQuery.Jobs.insert(job, projectId, data);
  //Logger.log('row 61  '+ runJob.status);
  var jobId = runJob.jobReference.jobId
  Logger.log('jobId: ' + jobId);
  Logger.log('row 61  '+ runJob.status);
  Logger.log('FINISHED!');
 // }
} 

解答 #

问题1：是否需要在GCP IAM中创建该服务账号？ #

是的，必须创建。服务账号是GCP中专门用于代表应用/服务执行自动化操作的身份实体，你需要登录GCP控制台，进入IAM & Admin > 服务账号页面，创建专属服务账号来承担BigQuery数据推送的操作。

问题2：是否需要在IAM中为其分配权限？ #

肯定需要，至少要配置两类权限：

• BigQuery权限：给服务账号分配BigQuery Data Editor（用于写入数据到指定表）和BigQuery Job User（用于提交数据加载任务）角色；
• Google Sheet权限：把服务账号的邮箱添加为目标Sheet的编辑者，确保它能读取Sheet中的数据。

问题3：是否需要修改AppScript以使用该服务账号？ #

是的，你当前的脚本默认使用触发按钮的门店用户身份调用BigQuery，必须修改脚本改用服务账号身份执行操作，否则依然会触发用户权限不足的错误。

脚本修改核心步骤

1. 获取服务账号密钥：在GCP控制台为创建的服务账号生成并下载JSON格式的密钥文件；
2. 导入OAuth2库：在AppScript编辑器中，通过资源 > 库添加OAuth2库，库ID为1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF；
3. 重构BigQuery调用逻辑：替换默认的BigQuery.Jobs.insert方法，改用UrlFetchApp配合服务账号的OAuth凭证发起请求。

修改后的示例代码：

// 替换为服务账号JSON密钥中的内容
const PRIVATE_KEY = '-----BEGIN PRIVATE KEY-----\n你的私钥内容\n-----END PRIVATE KEY-----\n';
const CLIENT_EMAIL = '你的服务账号邮箱@xxx.iam.gserviceaccount.com';

// 创建服务账号授权服务
function getBigQueryService() {
  return OAuth2.createService('BigQueryService')
    .setTokenUrl('https://oauth2.googleapis.com/token')
    .setPrivateKey(PRIVATE_KEY)
    .setIssuer(CLIENT_EMAIL)
    .setPropertyStore(PropertiesService.getScriptProperties())
    .setScope([
      'https://www.googleapis.com/auth/bigquery',
      'https://www.googleapis.com/auth/drive'
    ]);
}

function loadCogsPlayupHistory() {
  const service = getBigQueryService();
  if (!service.hasAccess()) {
    Logger.log('授权失败：' + service.getLastError());
    return;
  }

  const projectId = 'myproject';
  const datasetId = 'my_Dataset';
  const tableId = 'my table';
  const writeDispositionSetting = 'WRITE_APPEND';
  const sheetName = 'src_cogs_playup_current';

  const file = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const rows = file.getDataRange().getValues();
  const rowsCSV = rows.join("\n");
  const blob = Utilities.newBlob(rowsCSV, "text/csv");
  const csvData = blob.getBytes();

  // 构造BigQuery加载任务配置
  const jobConfig = {
    configuration: {
      load: {
        destinationTable: {
          projectId: projectId,
          datasetId: datasetId,
          tableId: tableId
        },
        skipLeadingRows: 1,
        writeDisposition: writeDispositionSetting,
        sourceFormat: 'CSV'
      }
    }
  };

  // 构造多部分请求（包含配置和CSV数据）
  const boundary = '----BigQueryBoundary' + Date.now();
  const payload = Utilities.newBlob('')
    .setDataFromString(`--${boundary}\nContent-Type: application/json; charset=UTF-8\n\n${JSON.stringify(jobConfig)}\n--${boundary}\nContent-Type: text/csv\n\n${rowsCSV}\n--${boundary}--`)
    .getBytes();

  // 发起请求
  const options = {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${service.getAccessToken()}`,
      'Content-Type': `multipart/related; boundary=${boundary}`
    },
    payload: payload,
    muteHttpExceptions: true
  };

  const response = UrlFetchApp.fetch(`https://www.googleapis.com/upload/bigquery/v2/projects/${projectId}/jobs?uploadType=multipart`, options);
  const responseData = JSON.parse(response.getContentText());

  if (response.getResponseCode() === 200) {
    Logger.log('jobId: ' + responseData.jobReference.jobId);
    Logger.log('状态: ' + JSON.stringify(responseData.status));
    Logger.log('FINISHED!');
  } else {
    Logger.log('推送失败：' + JSON.stringify(responseData));
  }
}

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