You may convey the ability of Google Maps to your Google Sheets utilizing easy formulation with no coding. You don’t must sign-up for the Google Maps API and all outcomes from Google Maps are cached within the sheet so you’re unlikely to hit any quota limits.
To offer you a fast instance, when you have the beginning tackle in column A and the vacation spot tackle in column B, a formulation like =GOOGLEMAPS_DISTANCE(A1, B1, "driving") will shortly calculate the gap between the 2 factors.
Or modify the formulation barely =GOOGLEMAPS_TIME(A1, B1, "strolling") to understand how lengthy it can take for an individual to stroll from one level to a different.
If you want to attempt the Google Maps formulation with out stepping into the technical particulars, simply make a duplicate of this Google Sheet and you’re all set.
Utilizing Google Maps inside Google Sheets
This tutorial explains how one can simply write customized Google Maps features inside Google Sheets that can aid you:
- Calculate distances between two cities or any addresses.
- Calculate the journey time (strolling, driving or biking) between two factors.
- Get the latitude and longitude co-ordinates of any tackle on Google Maps.
- Use reverse geocoding to search out the postal tackle from GPS co-ordinates.
- Print driving instructions between any factors on earth.
- Get the tackle from the zip code itself.
1. Calculate Distances in Google Sheets
Specify the origin, the vacation spot, the journey mode (strolling or driving) and the operate will return the gap between the 2 factors in miles.
=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "strolling")
/**
* Calculate the gap between two
* areas on Google Maps.
*
* =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "strolling")
*
* @param {String} origin The tackle of start line
* @param {String} vacation spot The tackle of vacation spot
* @param {String} mode The mode of journey (driving, strolling, bicycling or transit)
* @return {String} The space in miles
* @customFunction
*/
const GOOGLEMAPS_DISTANCE = (origin, vacation spot, mode) => {
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(vacation spot)
.setMode(mode)
.getDirections();
if (!information) {
throw new Error('No route discovered!');
}
const { legs: [{ distance: { text: distance } } = {}] = [] } = information;
return distance;
};2. Reverse Geocoding in Google Sheets
Specify the latitude and longitude and get the total tackle of the purpose by reverse geocoding of coordinates.
=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "strolling")
/**
* Use Reverse Geocoding to get the tackle of
* some extent location (latitude, longitude) on Google Maps.
*
* =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
*
* @param {String} latitude The latitude to lookup.
* @param {String} longitude The longitude to lookup.
* @return {String} The postal tackle of the purpose.
* @customFunction
*/
const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => {
const { outcomes: [data = {}] = [] } = Maps.newGeocoder().reverseGeocode(latitude, longitude);
return information.formatted_address;
};3. Get the GPS coordinates of an tackle
Get the latitude and longitude of any tackle on Google Maps.
=GOOGLEMAPS_LATLONG("10 Hanover Sq., NY")
/**
* Get the latitude and longitude of any
* tackle on Google Maps.
*
* =GOOGLEMAPS_LATLONG("10 Hanover Sq., NY")
*
* @param {String} tackle The tackle to lookup.
* @return {String} The latitude and longitude of the tackle.
* @customFunction
*/
const GOOGLEMAPS_LATLONG = (tackle) => {
const { outcomes: [data = null] = [] } = Maps.newGeocoder().geocode(tackle);
if (information === null) {
throw new Error('Deal with not discovered!');
}
const { geometry: { location: { lat, lng } } = {} } = information;
return `${lat}, ${lng}`;
};4. Print the driving instructions between addresses
Specify the origin tackle, the vacation spot tackle, the journey mode and the operate will use the Google Maps API to print step-by-step driving instructions.
=GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "strolling")
/**
* Discover the driving course between two
* areas on Google Maps.
*
* =GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "strolling")
*
* @param {String} origin The tackle of start line
* @param {String} vacation spot The tackle of vacation spot
* @param {String} mode The mode of journey (driving, strolling, bicycling or transit)
* @return {String} The driving course
* @customFunction
*/
const GOOGLEMAPS_DIRECTIONS = (origin, vacation spot, mode = 'driving') => {
const { routes = [] } = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(vacation spot)
.setMode(mode)
.getDirections();
if (!routes.size) {
throw new Error('No route discovered!');
}
return routes
.map(({ legs }) => {
return legs.map(({ steps }) => {
return steps.map((step) => {
return step.html_instructions.change(/<[^>]+>/g, '');
});
});
})
.be a part of(', ');
};5. Measure the journey time with Google Maps
Specify the origin tackle, the vacation spot tackle, the journey mode and the operate will measure your approximate journey time between the required addresses, offered a route exists.
=GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "strolling")
/**
* Calculate the journey time between two areas
* on Google Maps.
*
* =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "strolling")
*
* @param {String} origin The tackle of start line
* @param {String} vacation spot The tackle of vacation spot
* @param {String} mode The mode of journey (driving, strolling, bicycling or transit)
* @return {String} The time in minutes
* @customFunction
*/
const GOOGLEMAPS_DURATION = (origin, vacation spot, mode = 'driving') => {
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(vacation spot)
.setMode(mode)
.getDirections();
if (!information) {
throw new Error('No route discovered!');
}
const { legs: [{ duration: { text: time } } = {}] = [] } = information;
return time;
};
Tip: Enhance Method Efficiency by Caching
The Google Sheets features internally use the Google Maps API to calculate routes, distances and journey time. Google presents a restricted quota for Maps operations and in case your sheet performs too many queries in a brief period, you’re prone to see errors like “”Service invoked too many instances for at some point” or one thing related.
To get across the quota concern, it’s endorsed that you simply use Apps Script’s built-in cache to retailer outcomes and, if the outcomes of a operate exist already within the case, you’ll make one much less request to Google Maps.
The Maps features inside this Google Sheet additionally use caching and right here’s how one can implement it.
// The cache key for "New York" and "ny " must be similar
const md5 = (key = '') => {
const code = key.toLowerCase().change(/s/g, '');
return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
.map((char) => (char + 256).toString(16).slice(-2))
.be a part of('');
};
const getCache = (key) => {
return CacheService.getDocumentCache().get(md5(key));
};
// Retailer the outcomes for six hours
const setCache = (key, worth) => {
const expirationInSeconds = 6 * 60 * 60;
CacheService.getDocumentCache().put(md5(key), worth, expirationInSeconds);
};
/**
* Calculate the journey time between two areas
* on Google Maps.
*
* =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "strolling")
*
* @param {String} origin The tackle of start line
* @param {String} vacation spot The tackle of vacation spot
* @param {String} mode The mode of journey (driving, strolling, bicycling or transit)
* @return {String} The time in minutes
* @customFunction
*/
const GOOGLEMAPS_DURATION = (origin, vacation spot, mode = 'driving') => {
const key = ['duration', origin, destination, mode].be a part of(',');
// Is end result within the inside cache?
const worth = getCache(key);
// If sure, serve the cached end result
if (worth !== null) return worth;
const { routes: [data] = [] } = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(vacation spot)
.setMode(mode)
.getDirections();
if (!information) {
throw new Error('No route discovered!');
}
const { legs: [{ duration: { text: time } } = {}] = [] } = information;
// Retailer the lead to inside cache for future
setCache(key, time);
return time;
};Additionally see: Embed Google Maps in Emails and Paperwork
