Slide thumbnail

Update knowledge G Suite

Knowledge

Refresh BigQuery data in Sheets using Apps Script and Macros

15/03/2019

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

  1. 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.

  1. 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

 

Updated: Gimasys