Supply API key to avoid Hit Limit error from Maps

问题:

I have a Google Sheet where we are fetching the driving distance between two Lat/Lng via the Maps Service. The function below works, but the matrix is 4,500 cells, so I'm getting the "Hit Limit" error.

How can I supply my paid account's API key here?

Custom Function

function drivingMeters(origin, destination) {
  if (origin=='' || destination==''){return ''}
  var directions = Maps.newDirectionFinder()
  .setOrigin(origin)
  .setDestination(destination)
  .getDirections();
  return directions.routes[0].legs[0].distance.value ;
}

Example use:

A1: =drivingMeters($E10,G$9)

Where E10 = 42.771328,-91.902281
  and G9  = 42.490390,-91.1626620

回答1:

Per documentation, you should initialize the Maps service with your authentication details prior to calling other methods:

Your client ID and signing key can be obtained from the Google Enterprise Support Portal. Set these values to null to go back to using the default quota allowances.

I recommend storing these values in PropertiesService and using CacheService, to provide fast access. Using this approach, rather than writing them in the body of your script project, means they will not be inadvertently copied by other editors, pushed to a shared code repository, or visible to other developers if your script is published as a library.

Furthermore, I recommend rewriting your custom function to accept array inputs and return the appropriate array output - this will help speed up its execution. Google provides an example of this on the custom function page: https://developers.google.com/apps-script/guides/sheets/functions#optimization

Example with use of props/cache:

function authenticateMaps_() {
  // Try to get values from cache:
  const cache = CacheService.getScriptCache();
  var props = cache.getAll(['mapsClientId', 'mapsSigningKey']);
  // If it wasn't there, read it from PropertiesService.
  if (!props || !props.mapsClientId || !props.mapsSigningKey) {
    const allProps = PropertiesService.getScriptProperties().getProperties();
    props = {
      'mapsClientId': allProps.mapsClientId,
      'mapsSigningKey': allProps.mapsSigningKey
    };
    // Cache these values for faster access (max 6hrs)
    cache.putAll(props, 21600);
  }
  // Apply these keys to the Maps Service. If they don't exist, this is the
  // same as being a default user (i.e. no paid quota).
  Maps.setAuthentication(props.mapsClientId, props.mapsSigningKey);
}
function deauthMaps_() {
  Maps.setAuthentication(null, null);
}

// Your called custom function. First tries without authentication,
// and then if an error occurs, assumes it was a quota limit error
// and retries. Other errors do exist (like no directions, etc)...
function DRIVINGMETERS(origin, dest) {
  if (!origin || !destination)
    return;
  try {
    return drivingMeters_(origin, dest);
  } catch (e) {
    console.error({
      message: "Error when computing directions: " + e.message,
      error: e
    });
    // One of the possible errors is a quota limit, so authenticate and retry:
    // (Business code should handle other errors instead of simply assuming this :) )
    authenticateMaps_();
    var result = drivingMeters_(origin, dest);
    deauthMaps_();
    return result;
  }
}

// Your implementation function.
function drivingMeters_(origin, dest) {
  var directions = Maps.newDirectionFinder()
  ...
}