Skip to content

Instantly share code, notes, and snippets.

@WouterNieuwerth
Created November 9, 2018 19:51
Show Gist options
  • Save WouterNieuwerth/8eb7db76b566e4cbb935e64aec1ff326 to your computer and use it in GitHub Desktop.
Save WouterNieuwerth/8eb7db76b566e4cbb935e64aec1ff326 to your computer and use it in GitHub Desktop.
/**
* 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!');
}
@dklimok
Copy link

dklimok commented Nov 9, 2019

Thank you very much

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment