Send google sheet into Big Query

0
votes

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.

1
google-bigquery
Anything you can do on BigQuery, you can do with the API by sending a precise request to the correct URL. You'll need a script to call this everyday I'd say. In any case, your question would require a lot more information to be good for Stack Overflow :S... - Patrice

1 Answers

2
votes

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.