Stream data from Google Sheets to BigQuery to visualise in Tableau

4
votes

I am trying to create an timed, automatic data upload from Google Sheets to BigQuery so that I can visualise the data in Tableau. The data originates in import.io.

Based on I have been able to use Google Apps Scripts to create a .csv from the Google Sheets file, and upload the .csv file to BigQuery as a one off. However, this cannot be used as a recurring solution because the BigQuery load script requires a static URL for the .csv file (and each .csv download file gets a unique URL).

I believe that streaming data from Google Sheets, or directly from but, having studied Google BigQuery Analytics have not yet been able to solve this.

What steps are needed to stream data from Google Sheets to BigQuery?

1
google-sheetsgoogle-bigquerystreamingimport.io
Are you open to connecting import.io to BQ using Python? – Rohit
I am connecting BQ to Google Spreadsheets and Google Spreadsheets to import.io using import.io's integrate script, which I copy to Google sheets. I am just taking my first steps with Python (using Mac terminal). Would be great if you could provide some links if using Python is a better way. Afraid I am an ageing analyst trying to learn some developer skills. Sorry if this is basic and I really appreciate your response. – reusen
For the import.io side, the Python client integration examples can be found here: import.io/data/integrate/#python – Chris Alexander

1 Answers

0
votes

In the example you are linking to, the URL to the csv doesn't need to be static. The example is inserting into bigquery passing the octect/stream data directly, so all you need is the reference to a csv file.

I don't know which procedure you are following to export your spreadsheet to csv, but you can take a look at this tutorial https://developers.google.com/apps-script/articles/docslist_tutorial#section2

You will find there how to export your csv data as a new file, then with that file you can already import from your csv (using DriveApp folder object you can get the reference to the csv file).