CODEWITHSUNDEEP
google-apps-script
Michael Mahoney
Michael Mahoney

Reputation: 11

Prevent volatile Google App Scripts function from recalculating unless values have changed

I have a google app scripts function that updates every time I open the spreadsheet. I only want it to update if the data in the spreadsheet range changes.

I am using the function from the answer to this question: Maps.newDirectionFinder().SetArrive & SetDepart (Google Maps API) in GSheets App Script aren't impacting returned driving times w/ tested scripts

I tried to add a checkValue(e) function but it doesn't run at all. Ideally, I would want something built into the function rather than an external trigger function.

Upvotes: 1

Views: 163

Answers (2)

Cooper
Cooper

Reputation: 64082

Minor tweak to above answer:

  const startAddresses = sh.getRange('A2:A' + sh.getLastRow()).getValues();
  const endAddresses = sh.getRange('B2:B' + sh.getLastRow()).getValues();

To avoid nulls that are created with previous syntax

Upvotes: 0

doubleunary
doubleunary

Reputation: 18884

Google Sheets tends to recalculate custom functions every time the spreadsheet is opened. There is nothing you can do in the custom function itself to prevent that from happening.

The custom function you mention uses CacheService to cache results for up to six hours, which speeds performance and helps avoid exceeding rate limits. If it is the rate limits you are concerned with, using the cache should usually be enough to avoid overruns. Note that with this particular custom function, the cache will only be efficiently used when you provide a depart_time in the formula where you call the function.

If you have some other reason that makes it necessary to avoid recalculation, you cannot use a custom function but will have to implement the same functionality through a "regular" function, like this:

/**
* Gets the distance between a number of start addresses and end addresses.
*
* Uses GoogleMapsDistance() https://stackoverflow.com/a/73015812/13045193
*/
function runGoogleMapsDistance() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const startAddresses = sheet.getRange('A2:A').getValues();
  const endAddresses = sheet.getRange('B2:B').getValues();
  const result = GoogleMapsDistance(startAddresses, endAddresses);
  sheet.getRange('C2')
    .offset(0, 0, result.length, result[0].length)
    .setValues(result);
}

You can run the function through a button, a custom menu item, a sidebar or a trigger.

Upvotes: 3

Related Questions