Refresh BigQuery data in Sheets using Apps Script and Macros
When you compare data in Sheets and BigQuery, the following procedure should be adopted.
- Change how the data is formatted in Sheets. For example, data formatted as number could be changed to plain text.
- Cast the value in a query. For example: WHERE zip_code = CAST(@ZIP AS string)
- When you run a query, your spreadsheet permissions are used, not the spreadsheet owner’s.
- You can pull up to 10,000 rows per query from BigQuery.
- You can scan up to 1 TB of data in a single query in Sheets.
- Data that you pull from BigQuery is read-only.
- Only standard SQL is supported by BigQuery.
- On your computer, open a spreadsheet that contains data connected to BigQuery in Google Sheet.
- Record a macro from a different tab then the one you want to refresh.
- After you start recording, switch to the tab you want to refresh.
- At the bottom left, click Refresh.
- Click Save
Schedule your macro
- At the top, click Tools > Script editor.
- At the top, click Edit > Current project’s triggers.
- At the bottom right, click Add trigger and select your options.
- Click Save.