0

Trying to create a macro that refreshes a bigquery data connection.

Have tried to record a macro, starting on a different tab, clicking into the tab with the data connector and clicking refresh.

function Refresh2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('F3').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Extract 2'), true);
J_Macca
  • 1
  • 1

1 Answers1

1

If you follow the guide here, you should be able to do it.

Here is what I've got and it works flawlessly:

/** @OnlyCurrentDoc */

function refresh() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Data Sheet 1'), true);
  SpreadsheetApp.enableAllDataSourcesExecution();
  spreadsheet.getCurrentCell().getDataSourceTables()[0].refreshData();
};

This discussion may be interesting if you have yet another step after refreshing BigQuery source.

Yun Zhang
  • 5,185
  • 2
  • 10
  • 29
  • Does this work within google sheets also? When I copy that code into my script it still doesn't refresh. – J_Macca Oct 18 '19 at 00:28
  • Updated my answer, I pasted the macro that got recorded. It seems your macro recording must have had some problem. – Yun Zhang Oct 18 '19 at 00:56