Re: Help with Google Sheets auto-refresh

[Clark Lind]

Hi Eric, 
If you want to post your script (sanitize the URL as necessary), folks can take a look. Sometimes I look at something so long, I miss the obvious.
Also, that blog post had a part at the bottom in case the script stops working that you can try. 

On Thursday, April 7, 2022 at 10:25:23 AM UTC-4 Eric Habermas wrote:

Hi all -

I am following the steps outlined in this blog post to auto refresh IMPORTHTML data in Google Sheets:

https://blog.coupler.io/importhtml-function-google-sheets/

I have set up my script and my trigger. The trigger runs every 5 minutes as intended and is successful. But nothing actually happens unless I manually execute the function, which of course defeats the purpose.

Any help would be much appreciated. I must be overlooking something simple.

Thanks!

[Clark Lind]

p.s., in the Drive API (Advanced), there is a method Files.touch which you might be able to incorporate. I believe it causes a file to update. 
You can test it by putting the sheets' fileID in the API and run it. Then check the sheet to see if it updated.

[Clark Lind]

I got it to work by being more specific with the function.  
1) change the function name so there is no conflict with the default "myFunction" anywhere else in the code.
2) change the first line of the script to get the actual sheet (tab) by name since it may not always be the "active" one.
3) edit the trigger to run the new function name:

function refresh() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Refresh");

 

  var cell = sheet.getRange("B4");

 

  var refresh = parseInt(cell.getValue().toString());

  var increment = refresh + 1;

 

  cell.setValue(increment);

}

[Geoffrey Callaghan]

https://fabform.io/a/how-to-find-google-sheet-id