CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-cloud-platformgoogle-bigquery
istivan
istivan

Reputation: 26

On-demand update to BigQuery from Google Sheets

I have the following setup :

Since the mappings are in a google sheet, so far I had them set up like this :

So far, when I update the google sheet I have two options :

  1. wait until the next scheduled time, or go to the bigquery console, or
  2. run a scheduled backfill from the bigquery console.

However I want to offload the update process to my team (we're in finance, not techies, so tech knowledge is a bit limited at the moment) and avoid going to the console to trigger an update.

My question is this : How can I set this up so that whenever the google sheet is updated, it triggers a refresh of the table in bigquery. Should I go the AppsScript route? Or is there a more seamless way to do it?

Upvotes: 0

Views: 1113

Answers (1)

99_m4n
99_m4n

Reputation: 1265

Currently the only way to trigger queries from adhoc events is throught another service.

GCP offers different possibilities to do that like cloud functions, app engine, cloud run, etc.

My solution for your case (without knowing all details nor the knowledge of your team but trying to keep it simple) would be:

  1. Create a cloud function triggered by HTTP which executes the query to update the bigquery table. If I am not wrong it schould be the same as your schedule query
  2. Copy the http-url-link from the cloud function into your google sheets file or anywhere where you want to trigger your update from.
  3. Every time someone clicks the link, the could function will be triggered and it will update your bigquery table.

This is adhoc trigger (compared to trigger by each update) is a good aproach taking into account that google sheets updates with almost each little change (change a character in a cell, press enter in a calculation, etc.) and so much I/O is not intended for bigquery.

Upvotes: 0

Related Questions