开发者

Push data from googlesheets to to BigQuery with Appscript- how do I set up service account to load data. I don't want to add each user to BigQuery?

I have build a google sheet that will be replicated 80 times for stores to enter operational data into. This user generated data is then pushed to BigQuery with an Appscript via a button in the relevant sheet.

The script works fine, but I don't want to give each of the 80 users access to BigQuery, I would like to use a service account. As a bit of a newbie, I am not sure how to do this. I have some questions

  • Do I set this service account up in GCP IAM?
  • Do I assign permissions in IAM? If I add service account to the GoogleSheet with editor permission- I assume that I would need to modify my appscript to use the service account. Otherwise I will get the error as per screenshot enter image description here
/**
 * 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 开发者_JS百科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!');
 // }
} 


0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜