Connect BigQuery to Google Sheets (Push + Query)

What to do when you reach the end of the spreadsheet? Dust off your SQL, crack open BigQuery, and use this template to make magic happen.

This script allows you to push data up to BigQuery from Google Sheets, and pull it back into Sheets using queries written in SQL.

Grab the script from the CIFL Trello Vault here:

[KingCommerce]

Beyond Brilliant!

Hi David,
I work at a crowdfunding agency and have been struggling with this issue for the past couple of weeks.

I had a lot of CSV data that I was trying to run SQL Queries on.
So, my process was:

1) Setup NodeJS server with MySQL library installed.
2) Convert CSV to JSON
3) Run SQL-like queries with JavaScript
3) Convert the results back to CSV
4) Upload CSV back to Google Sheets.

I walked through your tutorial successfully, and am happy to explore this new workflow.

[jamesunderhill]

Love this approach leveraging SQL with BigQuery. I'm using a similar approach with a MySQL instance I query within sheets using the JBDC connector. Looks like you're using sheets to ETL supermetrics data to BigQuery here. Do you have recommendations for services to load data sets that might be too big for sheets?

[Moz]

Excellent tutorial and script, it was just what i was looking for. Thank you. I've got a long way to go on my journey with Big Query but this is a huge help. Next step for me is to figure out how to schedule regular data uploads to Big Query from a sheet

Brilliant !
Only blocking issue for me is that there's a lot of access permissions required to run the scripts, way too much especially if the data is sensitive:

CIFL Agency Data Pipeline wants to access your Google Account :
See, edit, create, and delete all of your Google Drive files
Manage the data in your Google SQL Service instances
View and manage your data in Google BigQuery
View and manage your Google Docs documents
See, edit, create, and delete your spreadsheets in Google Drive
Connect to an external service
Allow this application to run when you are not present
Display and run third-party web content in prompts and sidebars inside Google applications

Anyway it's a great demo and it seems to totally solve my issue and even grant me much more power.
My original issue : I have several bigquery queries returning 30-50k rows and it's above the 16k rows limit to export as a .csv or in a Gsheet.
The workaround for me is to export in GCS, then download, then import in Gsheet. I'll stick with my process for now.

Keep the good work :)

[mileezy]

Great template and tutorial, thanks. Is there any way to schedule data pulls from BigQuery?

[artiompucinskij]

I've installed the g suite plugin but cant see it in the addons menu of sheets. Any idea why? :(

[FerideKilic-bfxn]

I just couldnt find any information about how we update data in big query and google sheets simultaneously. Is there any way?

[SolutionuTx]

Hi David, I am at step 2, while running Create Bigquery Tables and View, the script is running without error but its not create tables in my Bigquery project. The project is showing empty. I am not sure what could be the reason. like in video when u r running the script for " Create Bigquery Tables and View, " setup column is showing yes but for me its showing emplty

[damiomole]

Hello this is a good tool but how does it handle version control . I know version control is available when use google sheets on its own but in the case where the data warehouse is in big query how is the version control handled

[alexabbuhl]

Hi David,
When I try and run your scripts it says that I need permission. Do you know why that is?

[alejandrosaiz]

Hi! First of all, thank you for all your code and explanation!

One question: I have more than 90 BigQuery queries, and when I run "Pull Queries from BigQuery", the script works but it stops when it arrives to query number 30. Is it because of something that is in your code? I have checked it but I see nothing...and I would like to be able to retrieve the result of more than 30 BigQuery queries.

Thank you!

[vaibhavchogle]

its paid version so of no use, any free tool available to pull data from website

[shubhamgupta]

Hi, I really liked your video. When I am trying to do the same thing I m geeting error "Sign in with Google temporarily disabled for this app. This app has not been verified yet by Google in order to use Google Sign In." Can you please suggest me what should I do to solve this issue?