2

I have the following script in my Google spreadsheet to calculate the distance in miles and time driving between two addresses. It works perfect until I get an error "Exception: Service invoked too many times for one day." I'm assuming it is constantly requesting in a loop instead of only once when I enter the address or when I change it. This would only be a few times a day and no where near the 2500 times allotted by Google. Any help with modifying the script or adding a formula to the spreadsheet would be greatly appreciated.


/*
 *
 * Google Maps Formulas for Google Sheets
 *
 * Written by Amit Agarwal
 *
 * Web: https://labnol.org/google-maps-formulas-for-sheets-200817
 *
 */

const md5 = (key = "") => {
  const code = key.toLowerCase().replace(/\s/g, "");
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
    .map((char) => (char + 256).toString(16).slice(-2))
    .join("");
};

const getCache = (key) => {
  return CacheService.getDocumentCache().get(md5(key));
};

const setCache = (key, value) => {
  const expirationInSeconds = 6 * 60 * 60; // max is 6 hours
  CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["duration", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return value;
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  setCache(key, time);
  return time;
};

/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The distance in miles
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["distance", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  setCache(key, distance);

  return distance;
};
2
  • Hi, welcome to WebApps. You've obviously tried the caching solution suggested by Labnol. There's also a related question here How do I get multiple driving times in Google sheets without "Service invoked error"?. Personally, I'd be using some Logger statements to try and troubleshoot the source and frequency.
    – Tedinoz
    Aug 30, 2022 at 23:38
  • Have you used the "Executions" tab of the Project Editor to watch the timing of execution of the function? This should help you understand why/whether the function is executing so often, and (perhaps) whether there are one or more other functions that are acting as triggers for the "googlemaps" function.
    – Tedinoz
    Sep 1, 2022 at 2:35

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.