How to automate the refresh option in google sheets using Google App Script

Scenario: We want to automate the refresh option of Google sheets such that every time the sheet is opened it fetches the latest data from the underlying source.

Let’s say we have Bigquery as the data source for this sheet. Here is a story that tells about how to get data from bigquery to google sheets.

Let’s get this up and running!

1. From the google sheets, Click on Extensions → AppScript. A new AppScript tab is opened.
2. Open the App Script Editor and edit the macros.gs file to add the following code:

function Refresh(){ 
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet
     (spreadsheet.getSheetByName(‘@connected_sheet_name’),true);
  SpreadsheetApp.enableAllDataSourcesExecution();
  spreadsheet.getActiveSheet().asDataSourceSheet().refreshData();
  Browser.msgBox(“@connected_sheet_name refreshed”);
  spreadsheet.setActiveSheet
     (spreadsheet.getSheetByName(‘@extracted_sheet_name’),true);
  spreadsheet.getRange(‘A1’).getDataSourceTable()[0].refreshData();
  Browser.msgBox(“@extracted_sheet_name refreshed”); 
} 

4. Open the appscript.json file and make sure the following permissions are added under OAuth scopes:

“oauthscopes”: [“https://www.googleapis.com/auth/drive”, “https://www.googleapis.com/auth/bigquery”, “https://www.googleapis.com/auth/spreadsheets”,“https://www.googleapis.com/auth/bigquery.readonly”]

5. Create a Trigger to execute this function on file Open event — Create a triggers tab from the App script window. Click on Add Trigger, choose the function name as “Refresh”, event source as “From Spreadsheet”, and event type as “Open” and Save.

That’s it. Test the functionality by reopening the file. Note that the first time this is executed google will ask for permissions to which we need to select allow option.