CODEWITHSUNDEEP
javascriptgoogle-sheetsgoogle-bigquery
Leonid
Leonid

Reputation: 31

insert data to bigquery from gsheet

I would like to upload data to a table in bigquery from gsheet, I mean I want to upload the data with code and not with add on, because it will be scheduled later on. Is there any code example that I can use for that for both ways (append and truncate)? I have a 2 columns data that I want to load. The columns are:

name    lastName    
josh    big
john    troble

May be some code using the following function 

BigQuery.Jobs.insert(resource, projectId)

Upvotes: 2

Views: 2196

Answers (3)

Lloyd Tabb
Lloyd Tabb

Reputation: 86

Looker is great on top of BigQuery and provides a very easy interface for embedding query results in google sheets (as well as excel).

http://www.looker.com/docs/sharing-and-publishing/publishing-looks-with-public-urls

  • note, I work at (and on) Looker, but I also love BigQuery

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

You might want to check Google BigQuery API in Apps Script
https://developers.google.com/apps-script/advanced/bigquery

Upload Job is to be used after you get data from gsheet

BigQuery.Jobs.insert

Upvotes: 0

Michael Sheldon
Michael Sheldon

Reputation: 2057

Here is a tutorial showing how to use Apps Script within a Google Spreadsheet to: (1) run a query and extract the results and put them into the spreadsheet, and (2) run a load job to import a file from Google Cloud Storage:

https://developers.google.com/apps-script/advanced/bigquery

Since that tutorial was written, BigQuery now supports inserting table rows directly through the tabledata.insertAll method:

https://cloud.google.com/bigquery/docs/reference/v2/tabledata/insertAll

If you want to upload some rows from a Google Spreadsheet into a BigQuery table, it looks like you can use Apps Script to insert the rows directly to BigQuery.

Another approach that should work: you can use Apps Script to create a file in Google Cloud Storage, then you can use example #2 from the first link above to load the data. The Google Cloud Storage API is accessible through Apps Script, so this should be possible. Here's a post showing how one user accomplished this step: http://ctrlq.org/code/20074-upload-files-to-google-cloud-storage

Upvotes: 0

Related Questions