CODEWITHSUNDEEP
google-bigquery
Ashish YADAV
Ashish YADAV

Reputation: 9

Send google sheet into Big Query

How can I send my google sheet into big query table? I want to sync the contents of the table once a day to Google sheet.

This simply means that I want my big query table to sync with a specific google sheet.

Upvotes: 0

Views: 2381

Answers (1)

Eric Uldall
Eric Uldall

Reputation: 2391

There are a few problems with trying to "sync" a sheet with big query: (https://cloud.google.com/bigquery/loading-data-into-bigquery)

  1. Big query is append only data - This means if you change a value in a pre-existing row in your sheet it won't be reflected in big query.
  2. If you delete a row from your spreadsheet it won't be deleted from big query.
  3. If you change the schema of your spreadsheet you'll be limited to certain scheme changes in big query: (https://cloud.google.com/bigquery/docs/reference/v2/tables/patch)
    1. You may add repeated or nullable columns to the end of a schema
    2. You can set a required column to nullable

I suggest you use federated data sources: (https://cloud.google.com/bigquery/federated-data-sources)

Federated data sources allow you to create big query tables backed by a csv. You can either use a google sheet directly, or a csv stored in google cloud storage. Then you don't have to worry about syncing once a day as the data should be eventually consistent on update of your csv.

NOTE: There seems to be a bug when using a google sheet backed table and trying to either save your query as a view or accessing the table from a user that does not have access to the google sheet. It's an access issue that I'm currently trying to figure out. Good luck.

Upvotes: 2

Related Questions