/** | |
* Loads the content of a Google Drive Spreadsheet into BigQuery | |
* Based on: | |
* - https://www.lunametrics.com/blog/2017/07/26/connect-google-analytics-data-tools-via-bigquery/ | |
* - https://developers.google.com/apps-script/advanced/bigquery | |
* Edit by Wouter Nieuwerth, w.nieuwerth@adwise.nl | |
* https://www.adwise.nl | |
*/ | |
function loadSpreadsheet() { | |
// Replace this value with the project ID listed in the Google | |
// Cloud Platform project. | |
var projectId = 'project-id-01234567890'; | |
// Create a dataset in the BigQuery UI (https://bigquery.cloud.google.com) | |
// and enter its ID below. | |
var datasetId = 'enterDatasetId'; | |
// Create a table OR let the script make it for you. | |
// Enter its ID below. | |
var tableId = 'enterTableId'; | |
// The URL of the Google Spreadsheet you wish to export to BigQuery: | |
var url = 'https://docs.google.com/spreadsheets/d/enteryourspreadsheethere'; | |
// The name of the sheet in the Google Spreadsheet you wish to export to BigQuery: | |
var sheetName = 'enterSheetName'; | |
// Create the table. | |
var table = { | |
tableReference: { | |
projectId: projectId, | |
datasetId: datasetId, | |
tableId: tableId | |
}, | |
// Details about schema can be found here: https://cloud.google.com/bigquery/docs/schemas | |
// Enter a schema below: | |
schema: { | |
fields: [ | |
{name: 'date', type: 'DATE'}, | |
{name: 'company', type: 'STRING'}, | |
{name: 'spend_14_day', type: 'FLOAT'}, | |
{name: 'hi_score', type: 'FLOAT'}, | |
{name: 'budget_index', type: 'FLOAT'}, | |
{name: 'extension_index', type: 'FLOAT'}, | |
{name: 'settings_index', type: 'FLOAT'}, | |
{name: 'mobile_index', type: 'FLOAT'}, | |
{name: 'kw_ads_index', type: 'FLOAT'}, | |
{name: 'audience_index', type: 'FLOAT'} | |
] | |
} | |
}; | |
// the write disposition tells BigQuery what to do if this table | |
// already exists | |
// WRITE_TRUNCATE: If the table already exists, BigQuery overwrites the table data. | |
// WRITE_APPEND: If the table already exists, BigQuery appends the data to the table. | |
// WRITE_EMPTY: If the table already exists and contains data, a 'duplicate' error is returned in the job result. | |
var writeDispositionSetting = 'WRITE_APPEND'; | |
//------------------------------------------ | |
//No edits below this line needed | |
// Create a new table if it doesn't exist yet. | |
try {BigQuery.Tables.get(projectId, datasetId, tableId)} | |
catch (error) { | |
table = BigQuery.Tables.insert(table, projectId, datasetId); | |
Logger.log('Table created: %s', table.id); | |
} | |
var file = SpreadsheetApp.openByUrl(url).getSheetByName(sheetName); | |
// 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'); | |
// Create the data upload job. | |
var job = { | |
configuration: { | |
load: { | |
destinationTable: { | |
projectId: projectId, | |
datasetId: datasetId, | |
tableId: tableId | |
}, | |
skipLeadingRows: 1, | |
writeDisposition: writeDispositionSetting | |
} | |
} | |
}; | |
// send the job to BigQuery so it will run your query | |
var runJob = BigQuery.Jobs.insert(job, projectId, data); | |
Logger.log(runJob.status); | |
var jobId = runJob.jobReference.jobId | |
Logger.log('jobId: ' + jobId); | |
var status = BigQuery.Jobs.get(projectId, jobId); | |
// wait for the query to finish running before you move on | |
while (status.status.state === 'RUNNING') { | |
Utilities.sleep(500); | |
status = BigQuery.Jobs.get(projectId, jobId); | |
Logger.log('Status: ' + status); | |
} | |
Logger.log('FINNISHED!'); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
This comment has been minimized.
Thank you very much