0

I have a function on a google sheet that extrapolates data from an ESI and fills in rows and columns with said data. Since the data gets old and needs to be updated it would be handy to have a script to run that deletes all rows and columns (except A1 where function resides) of said sheet and re-runs the function so that it repopulates it with new data (effectively refreshing the sheet). If also possible to only let it execute at intervals greater or equal to 24 hours to not stress the data server.

Hope someone can help me out with this.

EDIT: managed to create function to refresh data and rerun custom function but not yet able to set a timeframe except with sleep command.

Making a google script I achieved this:

function refreshData() {

  var app = SpreadsheetApp; 
  var sheet1 = app.getActiveSpreadsheet().getSheetByName("sheet1");
  sheet1.getRange("A:K").clearContent();
   sheet1.getRange("A1").setValue("=customFunction()");
   //Utilities.sleep(1800000); - stops script for 30 minutes

}
Rubén
  • 34,714
  • 9
  • 70
  • 166
Simon Riccio
  • 23
  • 3

1 Answers1

0

You can use a time-driven trigger to run the function refreshData with a frequency you specify. In this case, you would have to install an everyMinutes trigger.

You can do it manually, by following the steps specified here, or programmatically, by copying and running this function once:

function createTimeDrivenTrigger() {
  ScriptApp.newTrigger("refreshData")
  .timeBased()
  .everyMinutes(30) // Valid values are 1, 5, 10, 15 and 30
  .create();
}

Note:

  • Not sure why you are using a custom formula for this, but I'd recommend you to update your data via a regular Apps Script function.

Reference:

Iamblichus
  • 18,540
  • 2
  • 11
  • 27