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!');
// }
}
精彩评论