提供API密钥,以避免来自Apps脚本中Maps Service的命中限制错误 [英] Supply API key to avoid Hit Limit error from Maps Service in Apps Script
问题描述
我有一个Google表格,我们通过地图服务。下面的函数有效,但矩阵为4,500个单元格,因此出现命中限制错误。
如何在此处提供我的付费帐户的API密钥?
自定义功能
功能驾驶仪表(来源,目的地){
if(origin ==''|| destination ==''){return}}
var Directions = Maps.newDirectionFinder()
.setOrigin(origin )
.setDestination(destination)
.getDirections();
返回directions.routes [0] .legs [0] .distance.value;
}
示例用法:
A1:= drivingMeters($ E10,G $ 9)
其中E10 = 42.771328,-91.902281
和G9 = 42.490390,-91.1626620
每个文档,您应该< a href = https://developers.google.com/apps-script/reference/maps/maps#setAuthentication(String,String) rel = nofollow noreferrer>使用您的身份验证详细信息初始化地图服务在调用其他方法之前:
您的客户ID和签名密钥可以从Google Enterprise支持门户获取。将这些值设置为
null
以使用默认配额限额。
我建议将这些值存储在 PropertiesService
,并使用 CacheService
,以提供快速访问。使用这种方法,而不是在脚本项目的主体中编写它们,意味着如果您的脚本以库的形式发布,它们将不会被其他编辑器无意复制,推送到共享代码存储库或其他开发人员可见。另外,我建议重写您的自定义函数以接受数组输入并返回适当的数组输出-这将有助于加快其执行速度。 Google在自定义函数页面上提供了一个示例: https:// developers.google.com/apps-script/guides/sheets/functions#optimization
使用道具/缓存的示例:
函数authenticateMaps_(){
//尝试从缓存中获取值:
const cache = CacheService.getScriptCache();
var props = cache.getAll([[mapsClientId','mapsSigningKey']);;
//如果不存在,请从PropertiesService中读取。
if(!props ||!props.mapsClientId ||!props.mapsSigningKey){
const allProps = PropertiesService.getScriptProperties()。getProperties();
props = {
'mapsClientId':allProps.mapsClientId,
'mapsSigningKey':allProps.mapsSigningKey
};
//缓存这些值以加快访问速度(最多6小时)
cache.putAll(props,21600);
}
//将这些键应用于地图服务。如果不存在,则为
//与默认用户相同(即没有付费配额)。
Maps.setAuthentication(props.mapsClientId,props.mapsSigningKey);
}
函数deauthMaps_(){
Maps.setAuthentication(null,null);
}
//您调用的自定义函数。首先尝试不通过身份验证,
//,然后如果发生错误,则认为这是配额限制错误
//,然后重试。确实存在其他错误(例如没有方向等)...
函数DRIVINGMETERS(origin,dest){
if(!origin ||!destination)
return;
try {
return driveMeters_(origin,dest);
} catch(e){
console.error({
消息:计算路线时出错: + e.message,
错误:e
}) ;
//可能的错误之一是配额限制,因此请进行身份验证并重试:
//(业务代码应处理其他错误,而不是简单地假设:))
authenticateMaps_();
var结果= DrivingMeters_(origin,dest);
deauthMaps_();
返回结果;
}
}
//您的实现函数。
函数DrivingMeters_(origin,dest){
var direction = 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()
...
}
这篇关于提供API密钥,以避免来自Apps脚本中Maps Service的命中限制错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!