How to read and write (sync) data from Google sheets to Bigquery

Scenario: We need to show some data from google’s big query table to google sheets and then take inputs/comments from the user/Stakeholder for every entry. Once the comments are received they need to be updated in BigQuery.

So, What are our options here?

1. To read data from google sheets: Create an external table in GCP pointing to the required google sheet.
2. To write data into the google sheets: The first method is to use the readily available data connectors. But you may have concerns about using 3rd party connectors in that case we can use google “Connected sheets”.

Note: Connected sheets option is available only for enterprise users. Additionally, to complete this activity, we require the google sheets API enabled, Gsuite access, and a bigquery admin role.

Let’s get it up and running:

1. Write a SQL query to fetch the data from BQ tables to sheets.
2. Create a google sheet. Select Data → Data Connectors → Connect to BigQuery.

3. Select your project from the list (you should see a list of all GCP projects linked to your account)

4. Click on “Write Custom Query” and then paste the query written in step 1 into the Editor. Click on Connect.

5. Now we have the live data pulled from your BigQuery Table every time we hit the refresh option.

6. Create an Extract of the columns you want to show to your stakeholder into a new sheet using the extract option in the connected sheets.

7. Add the extra columns in the “Extract” sheet to capture the comments from stakeholders.

8. Create a GCP external table (don’t forget to skip the first line while defining the schema). we can utilize scheduled queries to make sure we do not lose any feedback provided by the user.

9. Use the external table to update the data in other GCP tables as per your data model.

That’s it! We have now created a sync system between Google sheets and BigQuery. Currently, the google sheet needs to be refreshed manually but this can also be automated via a simple macro. Here is a link for that Story.