代码之家 › 专栏 › 技术社区 › John Cappelletti

提供api密钥以避免apps脚本中来自maps服务的命中限制错误

custom-function google-direction google-sheets google-apps-script

2

John Cappelletti · 技术社区 · 8 年前

我有一张谷歌表,我们通过 Maps Service . 下面的函数可以工作,但是矩阵是4500个单元格,所以我得到了“命中极限”错误。

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

自定义函数

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

实例使用:

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

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

1 回复 | 直到 8 年前

1

3

tehhowch 8 年前

根据文档,您应该 initialize the Maps service with your authentication details 在调用其他方法之前:

您的客户机id和签名密钥可以从google企业支持门户获得。将这些值设置为 null 返回到使用默认配额津贴。

我建议将这些值存储在 PropertiesService 并使用 CacheService ,以提供快速访问。使用这种方法,而不是将它们写在脚本项目的主体中,意味着它们不会被其他编辑器无意地复制、推送到共享代码存储库中,或者在脚本作为库发布时对其他开发人员可见。

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

使用道具/缓存的示例: example props

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