0

I have got a function (called f1 let's say) in a script which takes too many time to run (in a spreadsheet, it s use setformula transpose importrange and query..take about 2min to run).

F1 is used to combine 2 spreadsheets and set the result in some columns in the 2nd spreadsheet using setformula().

I scheduled this function to run during the night (with trigger) but everytime someone opens it, the function reruns again (processing the same data). I try to put a void onload() function but f1 running as well even if i am not calling it.

Is there a way to run a function once a day and not when people open the spreadsheet?
Is there a way to cache the result for an entire day..until the function re run through the schedule?
Or is there a way to copy the value (not the formula contains in the cell) of an entire spreadsheet to another (i did not find this in documentation)?
or , is there any other workaround?

thanks for help

Loïc

athspk
  • 6,722
  • 7
  • 37
  • 51
user1690564
  • 31
  • 3

1 Answers1

1

If your function is called as a formula, it will be recalculated each time someone opens the spreadsheet and in some other circumstances too.

What you could do is, as you suggested, cache the data and return the cached data immediately. Something on these lines. 

var ONE_DAY = 24 * 60 * 60 * 1000;

function f1(parameters, force){
  var returnVal = CacheService.getPrivateCache().get('somevariablename'); 
  if (force == true || returnVal == null) {
    /* Do your long drawn calculation here */
    CacheService.getPrivateCache.put('somevariablename, value, ONE_DAY);
  }
  else {
    return returnVal;
  }
}

And call your function in your spreadsheet as 

  =f1(parameters, false)
Srik
  • 7,907
  • 2
  • 20
  • 29
  • what sould i put in **parameters** argument and in **somevariablename** ?..nothing ? parameters argument in not use in the function! – user1690564 Sep 22 '12 at 13:21
  • as the name describes, use any variable name. and if you don't use parameters, just get rid of it. – Srik Sep 22 '12 at 15:52
  • 2
    I know this is an older question/answer, but as mentioned here, [StackOverflow](http://stackoverflow.com/a/11568325/388992), the max time anything can be stored in the Cache is 6 hours (21600 seconds) – Timbermar Feb 22 '13 at 14:27