Update BigQuery data in Sheets with App Scripts and Macros

15/03/2019

Table of contents

What’s new:

Recently, Google launched the BigQuery data connector feature BigQuery. Now you can use tools like App Script and the Macro recorder in Sheets to schedule automatic updates to BigQuery data.

Who’s impacted

End users

Why you’d use it

Stay up to date with the latest and greatest in your business by automatically refreshing BigQuery data in Sheets.

For example, you can set up the sales data to be updated automatically, so that every time you open the data sheet, you can always observe and analyze the latest data every day. You can also let the data automatically update to prepare you for important meetings/presentations that happen weekly or monthly. Or you can set a schedule to update the data automatically every time you open the spreadsheet.

Instructions for using the feature

With Admin: No need to do anything
End users: Go here to learn how to schedule updates for data fetched from BigQuery.
Shoot Macros to Refresh Your Data
- On your computer, open a spreadsheet containing data that is connected to BigQuery in Google Sheets
- Record a macro from another tab, then from the tab you want to refresh
- After starting macro recording, switch to the tab you want to refresh
- At the bottom of the screen, click Refresh
Click Save.
Set a schedule for macros
In the upper part of the screen, select Tools > Script Editor
In the upper part of the screen, choose Edit > Current project's trigger.
In the lower right, click Add trigger and choose the options you need.
Click Save.