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

1 Answers1

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
  • 727
  • 1
  • 3
  • 21
  • 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