Refresh BigQuery data in Sheets using Apps Script and Macros
What’s changing
Recently we launched the BigQuery data connector to allow users to easily import data from larger datasets into Sheets. Now, you can use tools like Apps Script and the macro recorder to schedule automatic updates within Sheets to the connected BigQuery data.
Who’s impacted
End Users
Why you’d use it
Stay on top of the latest and greatest data critical to your business by automatically refreshing the BigQuery data in your sheet.
For example, you can set sales data to automatically refresh so that it’s ready for analysis at the beginning of each day. You can also auto-update data in preparation for key meetings or presentations that occur on a weekly or monthly basis. Or you could set a trigger to auto-update your data each time you open the spreadsheet.
How to get started
Admins: No action required.
End users: Here’s how to schedule a refresh on the data pulled in by the BigQuery data connector:
- Record a macro to refresh your data
- On your computer, open a spreadsheet that contains data connected to BigQuery in Google Sheets.
- 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.
Additional details
For key permissions for the BigQuery data connector see here.
As always, use sharing permissions to control who can view, edit or share your data.
Helpful links
