Script Timing Out in Sheets (Finds matching Zip codes)

[mholl...@michaelhollinger.org]

I have a domain name and am a paid GSUITE user - Supposedly, my scripys, at this membership level, should run 3200 times a day - For the life of me, I cannot get around the "ERROR - Service invoked too many in one day.geocode.line7" issue.

Can anyone help?

In cell E2 of my Google Sheet, I have: =geozipmh(D2) where D2 looks like this: 154 Clinton Ave.NY

It runs about 50 times then the error starts popping up.

All I want to do is fill in all the zip codes, save the file, export to Excel and call it a day - 

Here is my sheet: https://docs.google.com/spreadsheets/d/1ltmzfBkm9XNYTuIwWqdHYo_3Ijh44EdQZR-oYJIFDwc/edit?usp=sharing

Here's my script:

function geozipmh(a) {
    var response=Maps.newGeocoder().reverseGeocode(lat(a),long(a));
    return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
    var response = Maps.newGeocoder().geocode(pointa);
    return response.results[0].geometry.location.lat
}
function long(pointa) {
    var response = Maps.newGeocoder().geocode(pointa);
    return response.results[0].geometry.location.lng
}

[ekoleda...@googlers.com]

This error is about quota on the usage of the Maps geocoder, not script executions per day. We don't publish the quotas for the built-in geocoder, but you might want to try calling the Maps Geocoding API directly using UrlFetchApp instead:

https://developers.google.com/maps/documentation/geocoding/start

I'd also suggest not using custom functions for this, since they can recalculate fairly often, causing more quota to be used.

- Eric

[Faustino Rodriguez]

Quick question @Eric:

When using UrlFetchApp to call the Maps Geocoding API directly,

- How to set the HTTP referrer for the Maps Api key in the GCP?

- Or there is other kind of restriction to apply when using UrlFetchApp calls?

[Eric Koleda]

Using HTTP referrers is for client-side usage, since browsers set that header. It's possible to use IP address restrictions, but it Google uses a large range of IPs and they change often. Securing you API keys is more important when they are exposed to end users in some way, which they aren't in this case.

- Eric

--
You received this message because you are subscribed to a topic in the Google Groups "Google Apps Script Community" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-apps-script-community/EXBE4D33GGo/unsubscribe.
To unsubscribe from this group and all its topics, send an email to google-apps-script-c...@googlegroups.com.
Visit this group at https://groups.google.com/group/google-apps-script-community.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/6e992b4f-7840-4e2f-a600-5e064ec3c899%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Faustino Rodriguez]

Of course, thanks @Eric

(I didn't realized there is no need for protection from server-side)

[Michael Hollinger]

Thanks - Can you guide me as to what I would change to make this work?