Array input for GOOGLEMAPS custom apps script function in Google Sheets

Question

I am trying to upgrade the code below with the ability to accept an array input.

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));
};

// Store the results for 6 hours
const setCache = (key, value) => {
 const expirationInSeconds = 6 * 60 * 60;
 CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};

const GOOGLEMAPS_DISTANCE = (origin, destination, mode = 'driving') => {
 const key = ['distance', origin, destination, mode].join(',');
 // Is result in the internal cache?
 const value = getCache(key);
 // If yes, serve the cached result
 if (value !== null) return value;
 const { routes: [data] = [] } = Maps.newDirectionFinder()
   .setOrigin(origin)
   .setDestination(destination)
   .setMode(mode)
   .getDirections();
 if (!data) {
   GOOGLEMAPS_DISTANCE;
 }
 const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
 // Store the result in internal cache for future
 setCache(key, distance);
 return distance;
};

Currently, the code is able to find the distance between two given addresses and return it for single inputs. In order to work around Google's API request limit, I have added the ability for it to cache previous values. Also, the code re-runs anytime it reaches an error (such as the API request limit).

Now, I would like to upgrade the function to be able to accept an array for origin and destination. I found the Google documentation for adding this ability by using the map call, but I can't seem to make it work. I would highly appreciate it if anyone would be kind enough to respond, and help me with this.

JPV · Accepted Answer · 2021-04-10 11:03:53Z

1

Try adding these few lines immediately after the function declaration.

const GOOGLEMAPS_DISTANCE = (origin, destination, mode = 'driving') => {
 
  
  if(origin.map && destination.map) {
    return origin.map((origin, i) => GOOGLEMAPS_DISTANCE(origin[0], destination[i][0]))
}

//...rest of your code
}

Note: if you want to return a blank for empty rows/cells, try:

const GOOGLEMAPS_DISTANCE = (origin, destination, mode = 'driving') => {
  
  if(origin.map && destination.map) {
      return origin.map( (origin, i) => origin[0] && destination[i][0]? GOOGLEMAPS_DISTANCE(origin[0], destination[i][0]) : [])
  }
  //...rest of your code
  }

EDIT

I adapted your GOOGLEMAPS_DISTANCE function so that it now works with a matrix.

const GOOGLEMAPS_DISTANCE = (locations, mode = 'driving') => {
 
 if(locations.map) {
      return locations.map(location => GOOGLEMAPS_DISTANCE(location));
  }

const [origin, destination] = locations.split("_");
 
 const key = ['distance', origin, destination, mode].join(',');
 // Is result in the internal cache?
 const value = getCache(key);
 // If yes, serve the cached result
 if (value !== null) return value;
 const { routes: [data] = [] } = Maps.newDirectionFinder()
   .setOrigin(origin)
   .setDestination(destination)
   .setMode(mode)
   .getDirections();
 if (!data) {
   return 'no data found'
 }
 const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
 // Store the result in internal cache for future
 setCache(key, distance);
 return distance;
};

Note that a custom function call must return within 30 seconds. So maybe computing the whole matrix (A2:A20 and E1:Z1) will simply be to much.

I managed to get it working with part of the matrix (see screenshot and note the changed formula).

So maybe you can process some columns, then 'freeze' the result (copy, paste as values) and then proceed to the other columns?

See if that helps?

edited Apr 10, 2021 at 11:03

answered Apr 8, 2021 at 8:58

JPV

27.4k4 gold badges37 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

12 Comments

Daniel H. Over a year ago

Hello, thank you for your response. I have tried it this way but it still won't accept the array input and throws this error: TypeError: Cannot read property '0' of undefined (line 22). Do you know what could have gone wrong?

JPV Over a year ago

Tried a couple of locations with your code (and the code I added) and it worked fine. Can you maybe share your spreadsheet (sensitive data erased) so I can have a closer look?

Daniel H. Over a year ago

docs.google.com/spreadsheets/d/… here is the link to a copy of the spreadsheet. I removed all sensitive data and all editing restrictions.

Daniel H. Over a year ago

I gave it another couple tries and it does work if the arrays are both vertical. So, thank you very much. Although, what I am trying to do is create a distance matrix, with a vertical and a horizontal array (as you probably have seen in the sample spreadsheet), and that for some reason, won't work with this code. I assume the issue is that the two arrays that I want to input are different shapes. Do you know why this could be an issue?

Daniel H. Over a year ago

This works well, but the issue is that the matrix has to be dynamic as the left side array is constantly changing so I can't have the values frozen. Do you think it would be possible to have the code calculate the whole matrix without the custom formula? Since the Apps Script execution time limit is 6 minutes, it would have time to get through all of it and then just copy it in. Another possibility I was thinking of is having the code use a separate Google Sheet, where the results are saved instead of a 6 hour Cache, and then all the previous results could just be looked up from there.

|

Collectives™ on Stack Overflow

Array input for GOOGLEMAPS custom apps script function in Google Sheets

1 Answer 1

EDIT

12 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

12 Comments

Your Answer

Sign up or log in

Post as a guest

Related