Thursday, February 7, 2019

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: 
    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.
    2. 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 

Availability 

  • Rapid Release domains: Gradual rollout (up to 15 days for feature visibility) starting on February 5, 2019. 
  • Scheduled Release domains: Full Rollout (1-3 days for feature visibility) starting on February 19, 2019. 
G Suite editions 
  • Available to G Suite Business, G Suite Enterprise, G Suite for Education, G Suite Enterprise for Education, and Drive Enterprise. 
  • Not available to G Suite Basic and G Suite for Nonprofits 

On/off by default? 
  • This feature will be ON by default.

Stay up to date with G Suite launches