在 Apps 脚本中提供 API 密钥以避免来自地图服务的命中限制错误 [英] Supply API key to avoid Hit Limit error from Maps Service in Apps Script

问题描述

我有一个 Google 表格，我们通过 地图服务.下面的函数有效，但矩阵是 4,500 个单元格，所以我收到了Hit Limit"错误.

如何在此处提供我的付费帐户的 API 密钥?

自定义功能

functiondrivingMeters(origin, destination) {if (origin=='' || destination==''){return ''}var 方向 = Maps.newDirectionFinder().setOrigin(原点).setDestination(目的地).获取路线();返回 direction.routes[0].legs[0].distance.value ;}

示例使用:

A1: =drivingMeters($E10,G$9)其中 E10 = 42.771328,-91.902281和 G9 = 42.490390,-91.1626620

解决方案

根据文档，您应该 在调用其他方法之前使用您的身份验证详细信息初始化地图服务:

<块引用>

您的客户端 ID 和签名密钥可以从 Google Enterprise 支持门户获得.将这些值设置为 null 以返回使用默认配额限额.

我建议将这些值存储在 PropertiesService 并使用 CacheService，提供快速访问.使用这种方法，而不是将它们写在您的脚本项目正文中，意味着它们不会被其他编辑器无意中复制、推送到共享代码存储库，或者如果您的脚本作为库发布，其他开发人员也不会看到它们.

此外，我建议重写您的自定义函数以接受数组输入并返回适当的数组输出 - 这将有助于加快其执行速度.Google 在自定义函数页面上提供了一个示例:https://developer.google.com/apps-script/guides/sheets/functions#optimization

使用 props/cache 的例子:

function authenticateMaps_() {//尝试从缓存中获取值:const 缓存 = CacheService.getScriptCache();var props = cache.getAll(['mapsClientId', 'mapsSigningKey']);//如果它不存在，从 PropertiesService 读取它.if (!props || !props.mapsClientId || !props.mapsSigningKey) {const allProps = PropertiesService.getScriptProperties().getProperties();道具 = {'mapsClientId': allProps.mapsClientId,'mapsSigningKey': allProps.mapsSigningKey};//缓存这些值以加快访问速度(最多 6 小时)cache.putAll(props, 21600);}//将这些键应用于地图服务.如果它们不存在，这就是//与默认用户相同(即没有付费配额).Maps.setAuthentication(props.mapsClientId, props.mapsSigningKey);}函数 deauthMaps_() {Maps.setAuthentication(null, null);}//您调用的自定义函数.第一次尝试没有身份验证，//然后如果发生错误，则假定它是配额限制错误//并重试.确实存在其他错误(例如没有方向等)...功能驾驶仪表(原点，目的地){if (!origin || !destination)返回;尝试 {返回 driveMeters_(origin, dest);}赶上(e){控制台错误({消息:计算方向时出错:"+ e.message，错误:e});//可能的错误之一是配额限制，因此进行身份验证并重试://(业务代码应该处理其他错误而不是简单地假设这个:))认证地图_();变量结果 = 驱动仪表_(原点，目标)；deauthMaps_();返回结果；}}//你的实现函数.功能驱动仪表_(原点，目标){var 方向 = Maps.newDirectionFinder()...}

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

解决方案

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()
  ...
}

这篇关于在 Apps 脚本中提供 API 密钥以避免来自地图服务的命中限制错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！