0

I have a connected sheet that has results of a bigquery query(extract sheet). I created a preview of the results sheet. How do i write an appscript fuction to refresh the results in the extract and preview sheets? Please help.

I tried to use this code to refresh the connected sheet query , but it gave me an error saying : "Exception: The data object does not exist." I would like to write an apps script function if possible.

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

enter image description here

user17243359
  • 55
  • 6
  • Whenever possible, you need to include a [minimal example](https://stackoverflow.com/help/minimal-reproducible-example) that reproduces the issue. You can also include the expected behavior, the actual behavior, and how they differ, that would be helpful as well. Please visit [How to ask](https://stackoverflow.com/help/how-to-ask) to have some tips on how to write a question, so the community will be able to help you out in a better way. – David Salomon Feb 08 '22 at 01:47
  • @DavidSalomon, sorry I wasn't sure how to add an example as it is just two sheets(the extract and preview). I added the code snippet i tried, hopefully that helps a little? – user17243359 Feb 08 '22 at 02:15
  • About your comment of `@Tanaike can you please answer my question-stackoverflow.com/questions/71027348/…` to [my answer](https://stackoverflow.com/a/71281311/7108653), I saw your question. But I have to apologize for my poor English skill. Unfortunately, I cannot understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I would be grateful if you can forgive my poor English skill. – Tanaike Feb 27 '22 at 08:55
  • @Tanaike Sure no problem. I have a bigquery that is connected to google sheets. I added it by going to Data --> Data connectors --> Connect to BigQuery. I would like to write an apps script function/code to refresh this query's results everyday. The query results are in a tab called "query" and the extract(of the query results) are in a tab called "test". I would like to refresh the query tab and the extract tab everyday. I hope this makes sense – user17243359 Feb 27 '22 at 19:23
  • Thank you for replying. I have to apologize for my poor English skill, again. Unfortunately, I cannot still understand your question. But I would like to try to understand it. When I could correctly understand it, I would like to think of the solution. I would be grateful if you can forgive my poor English skill. – Tanaike Feb 28 '22 at 01:33
  • @Tanaike I've added a picture . You can see from the picture the extract and preview of the bigquery results. I need some code to automate the refresh. I would like to refresh it once a day – user17243359 Feb 28 '22 at 03:00

2 Answers2

0

The solution, a macro

1. Record a macro to refresh your data

  • Open the 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.
David Salomon
  • 804
  • 1
  • 7
  • 24
  • Thanks David, I was hoping to write an apps script function and not use a macro. I haven't used macro's before, so if I cant get it into an apps script function form, I will use the macro method. – user17243359 Feb 08 '22 at 02:14
0

If you record a Macro, it'll produce a script that you can use or edit. For example, it produced this script for me. As the google apps script function name changes over time, the Macro way always create the most updated script.

function Refreshdatamacro() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('sheet_1'), true);
  SpreadsheetApp.enableAllDataSourcesExecution();
  spreadsheet.refreshAllDataSources();
};
saeed ramazany
  • 1