Bring mapping capabilities and geocoding to Google Sheets: insert static maps with heatmap, administrative boundaries, shapes and geo-objects, place photos and street views, download GeoJSON and KML, get address components, find places with details, build routes, show isolines and have some other geocoding functions in your spreadsheet. Try our Maps for Sheets add-on for Google Sheets today! See our Privacy Policy.

Install Maps for Sheets add-on free and try it: menu Extensions - Maps for Sheets - Show Ideas.

Attention HERE Maps API users:

Final end-of-life for predecessor versions of HERE Location Services has been announced to end on December 31, 2023. The new version of the API does not support the provision of shapes and boundaries currently. HERE technical support does not provide information about the prospects for the availability of this functionality in the new version.

To preserve the functionality of retrieving shapes and boundaries for our users, we are making the corresponding functionality of the Bing Maps API available starting in early 2024. To use the corresponding formulas, obtain your own Bing Maps API key and save it in the settings after updating the add-on version in Google Workspace Marketplace.

Important note! Until the issue with using a google script app in a multiuser environment (signed-in in one session) is resolved, work with the add-on sidebars with one only signed-in Google account, who is the owner of the spreadsheet.

Important note! Do not forget to enable the add-on in your current document to make functions operational and enable add-on menu; reload the page with current spreadsheet in your browser:

1. Click Add-ons Manage add-ons.

2. Next to the add-on, click OptionsUse in this document to turn the add-on on or off. 

3. Reload the page to update the add-on menu. 

This app is powered by Google Maps and requires a Google Maps API Key  with enabled Places API, Places API (New), Directions API, Maps Static API, Street View Static API, Maps JavaScript API, Geocoding API for several functions. Shapes and boundaries functionality requires Bing Maps API. Isoline functionality requires Bing Maps API or Here Maps API key. The app comes with shared API keys for a limited trial period. Links to obtain your own keys are available on the add-on Settings page. Get free usage quota for Maps, Routes, and Places every month. Find Google pricing for Maps, Routes, and Places here.

Ideas for use

Click menu Extensions - Maps for Sheets - Show ideas to find some ideas for use of Maps for Sheets formulas.

Insert (for turned on add-on only)

Click menu Extensions - Maps for Sheets - Insert to insert Maps for Sheets formulas into your sheet interactively. Find relevant notes here.

Update formulas

To avoid wasting your quota of Maps API calls some results are cached by default.

To update formulas or refresh it after some issues

1. Click menu Extensions - Maps for Sheets - Update formulas - In Selection to update cells with formulas in a selected range.

2. Click menu Extensions - Maps for Sheets - Update Formulas - With Errors to update formulas with error status.

3. Click menu Extensions - Maps for Sheets - Update Formulas - All to refresh the entire sheet. It could be useful to flush cached results.

Replace formulas with values

To avoid API credits “leakage” due to periodical formulas updates by Google Sheets you may replace formulas with their values.

1. Click menu Extensions - Maps for Sheets - Replace formulas with values - In Selection for formulas in a selected range.

2. Click menu Extensions - Maps for Sheets - Replace formulas with values - With Errors for formulas with error status.

3. Click menu Extensions - Maps for Sheets - Replace formulas with values - All for the entire sheet.

Formulas inside =IMAGE() with simple arguments only, such as a range or value, are allowed for replacement. Recurring and nested functions calls returning images can not be evaluated and replaced.

Download

Select items to download and click the menu item under Extensions - Maps for Sheets - Download.

For images loaded from cells with the formula = IMAGE (), you can select a second range containing the title of the image.

For example, if column A contains titles, and column C contains the formula =IMAGE(), then you should first select column C, then column A, then select the menu Extensions - Maps for Sheets - Download - =IMAGE() images to download selected images from column C named as specified in column A.

Settings

1. Click menu Extensions - Maps for Sheets - Settings

2. To get a Google Maps API Key click Get Google Maps API Key link.
   Paste it to the Google Maps API Key field.

3. To get a Bing Maps API Key click Get Bing Maps API Key link.

Paste it to the Bing Maps API Key field

4. To get a HERE Maps API Key click Get HERE Maps API Key link.
   Paste it to the HERE Maps API Key field.
   If you have legacy HERE Maps API ID and Code pair paste it to the HERE Maps API ID and Code fields.

5. Click Save.

6. To avoid wasting a free quota of Maps API calls some results are cached by default. You might decide to turn it off for example to display live data.

7. Clear cache by clicking Clear cache link.

Subscription

Check this add-on subscription status at Extensions - Maps for Sheets - Subscription.

This add-in subscription does not cover Google, Bing, Here Maps API usage fees.

Trial and 1 sheet subscriptions are assigned to 1 spreadsheet only. Unassign it under subscription management to reuse in another spreadsheet.

For in-cell formulas operation the subscription is to be issued to 

- the owner of the document for spreadsheets on Google Drive;

- the person who first used the add-on in the document on Google Team Drive.

For interactive functions operation under Extensions - Maps for Sheets - Insert every current user of the spreadsheet needs an individual active subscription.

Function List

gsGetLatLng(place; [n_results])

bsGetLatLng(place; [n_results]) B

hsGetLatLng(place; [n_results]) H

gsParseLatLng(latlng)

gsGetPoint(latitude; longitude)

gsGetAddress(latitude; longitude)

gsGetPlaceID(place; [n_results])

gsGetPlaceUrl([placeId]; [address])

gsGeocode(place; [n_results]; [fields]; [language])

bsGeocode(place; [n_results]; [fields]; [language]; [entityTypes]) B

hsGeocode(place; [n_results]; [fields]; [language]) H

gsGetAddressComponents(place; [n_results]; [components]; [language]; [short_long])

bsGetAddressComponents(place; [n_results]; [components]; [language]) B

hsGetAddressComponents(place; [n_results]; [components]; [language]) H

gsReverseGeocode(lat; lng; [n_results]; [fields]; [language]) G

bsReverseGeocode(lat; lng; entityTypes; [n_results]; [fields]; [language]) B

hsReverseGeocode(location; level; [n_results]; [fields]; [language]) H

gsBoundaries(place; level; [components]; [language]) H

gsPlusCode(place; [fields]) G

gsFindPlaces2(query, [n_results]; [fields]; [type]; [location]; [radius]; [locationRestriction]; [rankby]; [openNow]; [priceLevels]; [minRating]; [language]; [region]; [strictTypeFiltering]; [evOptions]; [strictGeometryFiltering]) G

gsFindPlaces(query; [n_results]; [fields]; [type]; [location]; [radius]) G

gsFindPlaceFromText(input; inputtype; fields; locationbias; language; n_results) G

gsNearbyPlaces2(location; radius; [n_results]; [fields]; [keyword]; [includedTypes]; [excludedTypes]; [includedPrimaryTypes]; [excludedPrimaryTypes]; [rankby]; [language]; [region]) G

gsNearbyPlaces(location; radius; [n_results]; [fields]; [keyword]; [type]; [rankby]; [opennow]; [minprice]; [maxprice]) G

hsFindPlaces(query; location; [radius]; [n_results]; [fields]; [language]) H

gsGetPlaceDetails(place_id; [fields]; [language]) G

gsGetPlaceDetailsJSON(place_id; [fields]; [language]) G

gsGetDistanceLatLng(lat1; lon1; lat2; lon2)

gsGetLinearDistance(waypoints; ...)

gsGetRouteDistance([mode]; [options]; [departure_time]; [arrival_time]; waypoints; ...)

gsGetRouteTime(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...)

gsGetRouteCost(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...)

gsGetRouteUrl(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...)

gsGetRouteMap(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...) G

gsGetPointsMap(waypoints; ...) G

bsDirectionsDistance(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]) B

bsDirectionsTime(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]) B

bsDirectionsCost(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]) B

bsDirectionsRegionTravelSummary(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]; [fields]) B

bsDirectionsMap(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]; [imagerySet]) B

bsDirectionsUrl(waypoints; [mode]; [imagerySet]) B

gsGetPlacePhoto(place_id; [photo_n]; [maxwidth]; [maxheight]) G

gsGetStreetView(location; [width]; [height]; [heading]; [fov]; [pitch]; [radius]; [source]) G

gsGetIsolineMapFrom(origins; mode; type; ranges; [departure]; [options]; [show_mode]) GB/H

gsGetIsolineMapTo(destinations; mode; type; ranges; [arrival]; [options]; [show_mode]) GB/H

gsCommuteDistance(origin; destination; [mode]; [options]; [departure_time]; [arrival_time])

gsCommuteTime(origin; destination; [mode]; [options]; [departure_time]; [arrival_time])

gsCommuteCost(origin; destination; [mode]; [options]; [departure_time]; [arrival_time])

gsCommuteURL(origin; destination; [mode]; [options]; [departure_time]; [arrival_time])

gsCommuteRouteMap(origin; destination; [mode]; [options]; [departure_time]; [arrival_time]) G

generateRgbaColors(amount; [saturation]; [lightness])

hslToRgba(h; s; l)

highlightDuplicates([colIndex]; [sheet]) I

gsMultiNearbyPlaces IG [ Extensions - Maps for Sheets - Insert - Places - Batch nearby search ]

gsMultiFindPlaces IG [ Extensions - Maps for Sheets - Insert - Places - Batch text search ]

gsInsertHeatmap IG [ Extensions - Maps for Sheets - Insert - Heatmap - Heat map ]

Shapes and geo-objects:

geoPoint(points; [n_results]; [details]; [properties]) G*

geoPolygon(points; [properties]) G*

geoRectangle(points; width; height; [units]; [properties]) G*

geoCircle(points; radius; [units]; [steps]; [properties]) G*

geoShape(searchtext; [n_results],[backoff]) GB

geoLineString(points, properties) G*

geoBBox(geo) GH*

geoDraw IG [ Extensions - Maps for Sheets - Insert - Shapes and geo-objects - Draw shape]

geoLoadJSON(url)

geoIsolineFrom(origin; mode; type; ranges; [departure]; [options]) GB/H

geoIsolineTo(destination; mode; type; ranges; [arrival]; [options]) GB/H

geoToMap(objects) G

geoMapOptions([width]; [height]; [mapType]; [language]; [zoom]; [center_lat]; [center_lng]; [style])

geoToKml(objects)

gsDownload(arg; filename)

geoSetColor(geo; [color]; [strokeColor]; [strokeWeight])

geoSetMarkerIcon(geo; imageUrl)

geoDistance(object1; object2; [units])

geoGetArea(geo)

geoGetCentroid(geo)

geoGetCenter(geo)

geoGetLatLng(geo)

geoGetLatLngString(geo)

geoGetCoords(geo; [type])

geoProperties(input)

geoSetProperty(geo; key; property)

geoSetProperties(geo; properties)

geoGeometry(geo)

geoFlip(geo)

geoToTable(input; [fields])

geoLinkContained(geo)

geoLinkResolve(geo)

geoIntersect(objects)

geoUnion(geo; ...)

geoSubtract(base; shapes; ...)

geoGetChilds(geo; [type]; [items])

geoContainsPoints(obj; args; ...)

geoNearbyPlaces(location; radius; [n_results]; [fields]; [keyword]; [type]; [rankby]; [opennow]; [minprice]; [maxprice]) G

geoFindPlaces2(query, [n_results]; [fields]; [type]; [location]; [radius]; [locationRestriction]; [rankby]; [openNow]; [priceLevels]; [minRating]; [language]; [region]; [strictTypeFiltering]; [evOptions]) G

geoFindPlaces(query; [n_results]; [fields]; [type]; [location]; [radius]; [language]) G

hsFindPlaces(query; location; [radius]; [n_results]; [fields]; [language]) H

geoBoundaries(place; level; [language]) B

geoFindWithinBoundaries IGB/H* [ Extensions - Maps for Sheets - Insert - Places - Find within boundaries ]

geoGetGrid(geo; radius; units; items)

geoGetGridPoints(geo; radius; units; items)

geoIsContain(geo1; geo2)

geoIsOverlap(geo1; geo2)

I Interactive function: call via UI menu

G Google API key required

B Bing API key required

H HERE API key required

Function Description

gsGetLatLng(place; [n_results]) 

Example

gsGetLatLng(string; number)

Description

A custom function that gets the latitude/longitude coordinates.

place

The address of a given place.

n_results

(optional) The limitation of returned results

bsGetLatLng(place; [n_results]) B

Example

bsGetLatLng(string; number)

Description

A custom function that gets the latitude/longitude coordinates.

place

The address of a given place.

n_results

(optional) The limitation of returned results.

hsGetLatLng(place; [n_results]) H

Example

hsGetLatLng(string; number)

Description

A custom function that gets the latitude/longitude coordinates.

place

The address of a given place.

n_results

(optional) The limitation of returned results.

gsParseLatLng(latlng)

Example

gsParseLatLng("38° 53′ 23″ N, 77° 00′ 32″ W")

Description

A custom function that parses the DMS and DMM latitude/longitude coordinate strings.

latlng

The string with latitude/longitude coordinates.

gsGetPoint(latitude; longitude) 

Example

gsGetPoint(51.5073509; -0.1277583)

Description

A custom function that gets a point (lat, lng).

latitude

The latitude of a given place.

longitude

The latitude of a given place.

gsGetAddress(latitude; longitude) 

Example

gsGetAddress(51.5073509; -0.1277583)

Description

A custom function that gets an address at a point with specific coordinates.
Use the gsParseLatLng function to parse the coordinate string for a point.

latitude

The latitude of a given place.

longitude

The latitude of a given place.

gsGetPlaceID(place; [n_results]) 

Example

gsGetPlaceID(string; number)

Description

A custom function that gets the Google Place ID. The place ID is a unique identifier that can be used with other Google APIs.

place

The address of a given place.

n_results

(optional) The limitation of returned results

gsGetPlaceUrl([placeId]; [address]) 

Example

gsGetPlaceUrl(string; string)

Description

A custom function that gets the URL on the place on map.

To show the URL in the cell use formula =HYPERLINK(). See https://support.google.com/docs/answer/3093313

placeId

(optional) The place ID of a given place.

address

(optional) The address of a given place.

gsGeocode(place; [n_results]; [fields]; [language]) 

Example

gsGeocode(string; number; array_of_strings; string)

Description

A custom function that converts addresses (like "1600 Amphitheatre Parkway, Mountain View, CA") into geographic coordinates (like latitude 37.423021 and longitude -122.083739)  by using Here Maps API.

place

The address of a given place.

n_results

(optional) The limitation of returned results

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/maps/documentation/geocoding/intro

Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport 

bsGeocode(place; [n_results]; [fields]; [language]; [entityTypes]) B

Example

hsGeocode(string; number; array_of_strings; string; string)

Description

A custom function that converts addresses (like "1600 Amphitheatre Parkway, Mountain View, CA") into geographic coordinates (like latitude 37.423021 and longitude -122.083739) by using Bing Maps API.

place

The address of a given place.

n_results

(optional) The limitation of returned results

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Language code must be provided according to RFC 4647 standard. Note that the plural form of the parameter (languages) is supported as well.

entityTypes

(optional) The entity type to return: Address, AdminDivision1, AdminDivision2, CountryRegion, Neighborhood, PopulatedPlace, Postcode1.

hsGeocode(place; [n_results]; [fields]; [language]) H

Example

hsGeocode(string; number; array_of_strings; string)

Description

A custom function that converts addresses (like "1600 Amphitheatre Parkway, Mountain View, CA") into geographic coordinates (like latitude 37.423021 and longitude -122.083739) by using Here Maps API.

place

The address of a given place.

n_results

(optional) The limitation of returned results

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developer.here.com/documentation/geocoder/dev_guide/topics/reading-geocoding-response.html

Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Language code must be provided according to RFC 4647 standard. Note that the plural form of the parameter (languages) is supported as well.

gsGetAddressComponents(place; [n_results]; [components]; [language]; [short_long])

Example

gsGetAddressComponents(string; number; string; string; string)

Description

Returns address components.

place

The address or coordinates of a given place.

n_results

(optional) The limitation of returned results

components

(optional) One or more fields, specifying the types of address components to return, or numbers of the components, separated by a comma.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

short_long

(optional) 'short_name' (default) to return short names; 'long_name' to return long names.

bsGetAddressComponents([place]; [n_results]; [components]; [language])

Example

bsGetAddressComponents(string; number; string; string)

Description

Returns address components.

place

The address of a given place.

n_results

(optional) The limitation of returned results

components

(optional) One or more fields, specifying the types of address components to return, separated by a comma. Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language.

hsGetAddressComponents([place]; [n_results]; [components]; [language])

Example

hsGetAddressComponents(string; number; string; string)

Description

Returns address components.

place

The address of a given place.

n_results

(optional) The limitation of returned results

components

(optional) One or more fields, specifying the types of address components to return, separated by a comma. Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language.

gsReverseGeocode(lat; lng; [n_results]; [fields]; [language]) 

Example

gsReverseGeocode(number; number; number; array_of_strings; string)

Description

A custom function translating a location on the map into a human-readable address.

lat

The latitude of a given place.

lng

The longitude of a given place.

n_results

(optional) The limitation of returned results

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/maps/documentation/geocoding/intro 

Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

bsReverseGeocode(lat; lng; entityTypes; [n_results]; [fields]; [language]) B

Example

bsReverseGeocode(latitude_or_string; longitude; string; number; array_of_strings; string)

Description

A custom function translating a location on the map into a human-readable address.

lat

(required) address/search text or latitude: [lat,lng][address].

lng

(required for latitude/longitude).

level

(optional) Target match level of the search result: one of [country, state, county, city, district, postalCode].

n_results

(optional) The limitation of returned results

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developer.here.com/documentation/geocoder/dev_guide/topics/reading-geocoding-response.html

Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

hsReverseGeocode(location; level; [n_results]; [fields]; [language])

Example

hsReverseGeocode(latlng_or_string; string; number; array_of_strings; string)

Description

A custom function translating a location on the map into a human-readable address.

location

(required) of a given place either lat/lng or address: [lat,lng][address].

level

(optional) Target match level of the search result: one of [country, state, county, city, district, postalCode].

n_results

(optional) The limitation of returned results

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developer.here.com/documentation/geocoder/dev_guide/topics/reading-geocoding-response.html

Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

gsBoundaries(place; level; [components]; [language])

Example

gsBoundaries(geojson_or_latlng_or_string; string; string; string)

Description

Returns the administrative area around a geo-point or areas inside a geo-shape. Important note: This function is very time consuming, therefore, to query a number of results, it is recommended to divide the query area into subareas, as well as use an interactive call via the Insert - Function menu. Some areas may not contain shape information.

place

(required) GeoJSON object, latlng or address string.

level

(required) Target match level of the search result: one of [Country, State, County, City, District, PostalCode].

components

(optional) One or more fields, specifying the types of address components to return, separated by a comma: [Label,Country,State,County,City,District,PostalCode].

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

gsPlusCode(place; [fields])

Example

gsPlusCode(string; array_of_strings)

Description

A custom function that converts addresses (like "1600 Amphitheatre Parkway, Mountain View, CA") into plus code (like 849VCWC8+X4).

place

The address or coordinates of a given place.

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. Leave blank to see all available fields.

gsFindPlaceFromText(input; inputtype; fields; locationbias; language; n_results) G

Example

gsFindPlaceFromText(string; string; array_of_strings; string; string; number)

Description

A Find Place request takes a text input and returns a place. The input can be any kind of Places text data, such as a name, address, or phone number. A Find Place request using non-string data such as a lat/lng coordinate or plus code generates an error.

input

(required) Text input that identifies the search target, such as a name, address, or phone number. The input must be a string. Non-string input such as a lat/lng coordinate or plus code generates an error.

inputtype

(optional) The type of input: textquery (default), phonenumber. Phone numbers must be in international format (prefixed by a plus sign ("+"), followed by the country code, then the phone number itself)

fields

(optional) Use the fields parameter to specify a comma-separated list of place data types to return. For example: fields=formatted_address,name,geometry. See https://developers.google.com/maps/documentation/places/web-service/search-find-place#fields

locationbias

(optional) Prefer results in a specified area, by specifying either a radius plus lat/lng, or two lat/lng pairs representing the points of a rectangle. See https://developers.google.com/maps/documentation/places/web-service/search-find-place#locationbias

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

n_results

(optional) The limitation of returned results: 1 by default (if missing).

gsNearbyPlaces2(location, [radius], [n_results], [fields], [includedTypes], [excludedTypes], [includedPrimaryTypes], [excludedPrimaryTypes], [rankby], [language], [region])

Example

gsNearbyPlaces2(string, number, number, array_of_strings, string, string, string, string, string, string, string)

Description

A custom function that finds nearby places.

location

(required) Prefer results in a specified area by specifying either lat/lng or address: [lat,lng][address].

radius

(required) Defines the distance (in meters) within which to return place results. The maximum allowed radius is 50 000 meters. Note that radius must not be included if rankby=distance. See https://developers.google.com/places/web-service/search

n_results

(optional) Specifies the maximum number of place results to return. Must be between 1 and 20 (default) inclusive.

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

includedTypes

(optional) A comma-separated list of the place types from Table A to search for. If this parameter is omitted, places of all types are returned. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#includedtypes

excludedTypes

(optional) A comma-separated list of place types from Table A to exclude from a search. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#excludedtypes

includedPrimaryTypes

(optional) A comma-separated list of primary place types from Table A to include in a search. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#includedprimarytypes

excludedPrimaryTypes

(optional) A comma-separated list of primary place types from Table A to exclude from a search. If there are any conflicting primary types, such as a type appearing in both includedPrimaryTypes and excludedPrimaryTypes, an INVALID_ARGUMENT error is returned. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#excludedprimarytypes

rankby

(optional) The type of ranking to use. If this parameter is omitted, results are ranked by popularity. May be one of the following: POPULARITY (default) Sorts results based on their popularity, DISTANCE Sorts results in ascending order by their distance from the specified location.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Google often updates the supported languages, so this list may not be exhaustive. See https://developers.google.com/maps/faq#languagesupport

region

(optional) The region code used to format the response, specified as a two-character CLDR code value. There is no default value. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#regioncode

gsNearbyPlaces(location; radius; n_results; fields; keyword; type; rankby; opennow; minprice; maxprice) G

Example

gsNearbyPlaces(string; number; number; array_of_strings; string; string; string; string; string; string)

Description

A custom function that finds nearby places.

location

(required) Prefer results in a specified area by specifying either lat/lng or address: [lat,lng][address].

radius

(required) Defines the distance (in meters) within which to return place results. The maximum allowed radius is 50 000 meters. Note that radius must not be included if rankby=distance. See https://developers.google.com/places/web-service/search 

n_results

(optional) The limitation of returned results: 20 by default (if missing or with trial Google API key).

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults 

Leave blank to see all available fields.

keyword

(optional) A term to be matched against all content that Google has indexed for this place, including but not limited to name, type, and address, as well as customer reviews and other third-party content.

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types 

rankby

(optional) Specifies the order in which results are listed. Note that rankby must not be included if radius is specified. Possible values are: prominence (default), distance. When distance is specified, one or more of keyword, name, or type is required.

opennow

(optional) Returns only those places that are open for business at the time the query is sent.

minprice

(optional) Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

maxprice

(optional) Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

gsFindPlaces2(query, n_results, fields, type, location, radius, locationRestriction, rankby, openNow, priceLevels, minRating, language, region, strictTypeFiltering, evOptions, strictGeometryFiltering) G

Description

A custom function that finds places.

Example

gsFindPlaces2(string, number, array_of_strings, string, string, number, string, string, string, string, string, string, string, string, string, string)

query

(required) A Find Place request takes a text input, and returns a place. The text input can be any kind of Places data, for example, a name or address.

n_results

(optional) The limitation of returned results: 20 by default (if missing). Use your own Google Key to get over 20 records. The maximum number of results returned by this API is 60. Use interactive function geoFindWithinBoundaries to get more results.

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. Use * to retrieve all fields. See https://developers.google.com/maps/documentation/places/web-service/text-search#fieldmask

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/maps/documentation/places/web-service/text-search#includedtype

location

(optional) Prefer results in a locationBias+radius specified area by specifying either lat and lng or address or geoJSON: ["lat,lng", "address"]. If this parameter is not specified, the API uses IP address biasing by default.

radius

(optional) Defines the distance (in meters) within which to bias place results. The maximum allowed radius is 50 000 meters. See https://developers.google.com/places/web-service/search

locationRestriction

(optional) Specify the region as a rectangular Viewport or address: A rectangle is a latitude-longitude viewport, represented as two diagonally opposite low and high points. You can specify locationRestriction or location+radius, but not both. You can only use geoJSON with a given location field. Think of locationRestriction as specifying the region which the results must be within, and location+radius as specifying the region that the results must be near but can be outside of the area. See https://developers.google.com/maps/documentation/places/web-service/text-search#location-restriction

rankby

(optional) The type of ranking to use. If this parameter is omitted, results are ranked by popularity. May be one of the following: POPULARITY (default) Sorts results based on their popularity, DISTANCE Sorts results in ascending order by their distance from the specified location. Not compatible with locationRestriction.

openNow

(optional) If true, return only those places that are open for business at the time the query is sent. If false, return all businesses regardless of open status. Places that don't specify opening hours in the Google Places database are returned if you set this parameter to false.

priceLevels

(optional) Restrict the search to places that are marked at certain price levels. The default is to select all price levels. See https://developers.google.com/maps/documentation/places/web-service/text-search#pricelevels

minRating

(optional) Restricts results to only those whose average user rating is greater than or equal to this limit. Values must be between 0.0 and 5.0 (inclusive) in increments of 0.5. For example: 0, 0.5, 1.0, ... , 5.0 inclusive. Values are rounded up to the nearest 0.5. For example, a value of 0.6 eliminates all results with a rating less than 1.0.

language

(optional) The language in which to return results. If languageCode is not supplied, the API defaults to en. If you specify an invalid language code, the API returns an INVALID_ARGUMENT error. See https://developers.google.com/maps/documentation/places/web-service/text-search#languagecode

region

(optional) The region code used to format the response, specified as a two-character CLDR code value. There is no default value. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#regioncode

strictTypeFiltering

(optional) Used with the includedType parameter. When set to true, only places that match the specified types specified by includeType are returned. When false, the default, the response can contain places that don't match the specified types.

evOptions

(optional) Specifies parameters for identifying available electric vehicle (EV) charging connectors and charging rates. See https://developers.google.com/maps/documentation/places/web-service/text-search#evoptions

strictGeometryFiltering

(optional) Used with the locationRestriction parameter in geoJSON format and fields containing location.

gsFindPlaces(query; [n_results]; [fields]; [type]; [location]; [radius]) G

Example

gsFindPlaces(string; number; array_of_strings; string; string; number)

Description

A custom function that finds places. The search response will include a list of places. In case of missing fields parameter the header row is also included.

query

(required) A Find Place request takes a text input, and returns a place. The text input can be any kind of Places data, for example, a name or address.

n_results

(optional) The limitation of returned results: 20 by default (if missing or with trial Google API key).

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

Leave blank to see all available fields.

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types

location

(optional) Prefer results in a specified area by specifying either lat/lng or address: [lat,lng][address]. If this parameter is not specified, the API uses IP address biasing by default.

radius

<optional> Defines the distance (in meters) within which to bias place results. The maximum allowed radius is 50 000 meters. See https://developers.google.com/places/web-service/search

hsFindPlaces(query; location; [radius]; [n_results]; [fields]; [language]) H

Example

hsFindPlaces(string; string; number; number; array_of_strings; string)

Description

A custom function that finds places.

query

(required) A Find Place request takes a text input, and returns a place. The text input can be any kind of Places data, for example, a name or address.

location

(optional) Prefer results in a specified area by specifying either lat/lng or address: [lat,lng][address]. If this parameter is not specified, the API uses IP address biasing by default.

radius

(optional) Defines the distance (in meters) within which to bias place results.

n_results

(optional) The limitation of returned results: 20 by default (if missing).

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. Leave blank to see all available fields.

language

(optional) BCP 47 language code, indicating in which language the results should be returned. If lang is not set, or if the result item has no translation in the requested language, the default language will be used.

gsGetPlaceDetails(place_id; [fields]; [language]) G

Example

gsGetPlaceDetails(string; array_of_strings; string)

Description

A custom function that gets details about a particular establishment or point of interest.

place_id

(required) A textual identifier that uniquely identifies a place.

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/details

Leave blank to see all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

gsGetPlaceDetailsJSON(place_id; [fields]; [language]) G

Example

gsGetPlaceDetailsJSON(string; array_of_strings; string)

Description

A custom function that gets details about a particular establishment or point of interest in JSON format.

place_id

(required) A textual identifier that uniquely identifies a place.

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/details

Leave blank to include all available fields.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

gsGetDistanceLatLng(lat1; lon1; lat2; lon2)

Example

gsGetDistanceLatLng(51.5073509; -0.1277583; 53.4083714; -2.9915726)

Description

A custom function that gets the linear distance between 2 points.

lat1

Latitude of of the first point

lon1

Longitude of of the first point

lat2

Latitude of of the second point

lon2

Longitude of of the second point

gsGetLinearDistance(waypoints; ...) 

Example

gsGetLinearDistance("london","53.480761, -2.242631","liverpool")

Description

A custom function that gets the linear distance between multiple addresses or points.

waypoints

Ordered list of two or more map waypoints

gsGetRouteDistance([mode]; [options]; [departure_time]; [arrival_time]; waypoints; ...) 

Example

gsGetRouteDistance(string; string; date_and_time; date_and_time; array_of_strings)

Description

A custom function that gets the route distance between multiple addresses or points.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

waypoints

Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

gsGetRouteTime(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...)

Example

gsGetRouteTime(string; string; date_and_time; date_and_time; array_of_strings)

Description

A custom function that gets the duration in seconds on this route.

mode

specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

waypoints

Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

gsGetRouteCost(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...) 

Example

gsGetRouteCost(string; string; date_and_time; date_and_time; array_of_strings)

Description

A custom function that gets the total fare (that is, the total ticket costs) on this route.

mode

specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

waypoints

Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

gsGetRouteUrl(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...) 

Example

gsGetRouteUrl(string; string; date_and_time; date_and_time; array_of_strings)

Description

A custom function that gets the URL on the route on map.

To show the URL in the cell use formula =HYPERLINK(). See https://support.google.com/docs/answer/3093313

mode

specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

waypoints

Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

gsGetRouteMap(mode; [options]; [departure_time]; [arrival_time]; waypoints; ...) G

Example

=IMAGE(gsGetRouteMap(string; string; date_and_time; date_and_time; array_of_strings))

Description

Use gsGetRouteMap to return the static map with the route calculated by using the waypoints in the order they are given.

This function to be used with formula =IMAGE(gsGetRouteMap(...))

See https://support.google.com/docs/answer/3093333

mode

specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

waypoints

Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

gsGetPointsMap(waypoints; ...) G

Example

=IMAGE(gsGetPointsMap(Object))

Description

Use gsGetPointsMap to return the static map with multiple points.

This function to be used with formula =IMAGE(gsGetPointsMap(...))
See https://support.google.com/docs/answer/3093333

waypoints

Specifies an ordered list of locations: addresses (limited to a maximum of 15 locations that requires geocoding) or latitude and longitude values (for example, "40.714728,-73.998672").

bsDirectionsDistance(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]) B

Example

bsDirectionsDistance(array_of_strings; string; string; date_and_time; string; string; string; string; string)

Description

A custom function that gets the route distance between multiple addresses or points.

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

mode

(optional) specifies what mode of transport to use when calculating directions. Valid values are: Driving (default), Walking, Transit, Truck

distanceUnit

(optional) The units to use for distance in the response. Valid values are: Kilometer or km (default), Mile or mi.

dateTime

Required when the travel mode is Transit. Optional for Driving. The dateTime parameter identifies the desired transit time, such as arrival time or departure time. The transit time type is specified by the timeType parameter. Example: 2013-07-04T17:00:00Z, 03/01/2011 05:42:00, 05:42:00 [assumes the current day], 03/01/2011 [assumes the current time]

timeType

Required when the travel mode is Transit Specifies how to interpret the date and transit time value that is specified by the dateTime parameter. Valid values are: Arrival, Departure, LastAvailable (The dateTime parameter contains the latest departure time available for a transit request).

avoid

(optional) Specifies the road types to minimize or avoid when a route is created for the driving travel mode. Valid values are: highways, tolls, ferry, minimizeHighways, minimizeTolls, borderCrossing.

optimize

(optional) Specifies what parameters to use to optimize the route. Valid values are: distance, time (default), timeWithTraffic, timeAvoidClosure.

optimizeWaypoints

(optional) Instructs the API to rearrange the route waypoints and reduce the route cost specified with the optimize parameter. The route first waypoint wp.0 and last waypoint wp.n order is not changed, their position is considered fixed. Valid values are: true or false.

options

(optional) Set extra parameters: "parameter1:value1&parameterN:valueN&". See https://docs.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-truck-route

bsDirectionsTime(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]) B

Example

bsDirectionsTime(array_of_strings; string; string; date_and_time; string; string; string; string; string)

Description

A custom function that gets the duration in seconds on this route.

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

mode

(optional) specifies what mode of transport to use when calculating directions. Valid values are: Driving (default), Walking, Transit, Truck

distanceUnit

(optional) The units to use for distance in the response. Valid values are: Kilometer or km (default), Mile or mi.

dateTime

Required when the travel mode is Transit. Optional for Driving. The dateTime parameter identifies the desired transit time, such as arrival time or departure time. The transit time type is specified by the timeType parameter. Example: 2013-07-04T17:00:00Z, 03/01/2011 05:42:00, 05:42:00 [assumes the current day], 03/01/2011 [assumes the current time]

timeType

Required when the travel mode is Transit Specifies how to interpret the date and transit time value that is specified by the dateTime parameter. Valid values are: Arrival, Departure, LastAvailable (The dateTime parameter contains the latest departure time available for a transit request).

avoid

(optional) Specifies the road types to minimize or avoid when a route is created for the driving travel mode. Valid values are: highways, tolls, ferry, minimizeHighways, minimizeTolls, borderCrossing.

optimize

(optional) Specifies what parameters to use to optimize the route. Valid values are: distance, time (default), timeWithTraffic, timeAvoidClosure.

optimizeWaypoints

(optional) Instructs the API to rearrange the route waypoints and reduce the route cost specified with the optimize parameter. The route first waypoint wp.0 and last waypoint wp.n order is not changed, their position is considered fixed. Valid values are: true or false.

options

(optional) Set extra parameters: "parameter1:value1&parameterN:valueN&". See https://docs.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-truck-route

bsDirectionsCost(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]) B

Example

bsDirectionsCost(array_of_strings; string; string; date_and_time; string; string; string; string; string)

Description

A custom function that gets the total fare (that is, the total ticket costs) on this route.

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

mode

(optional) specifies what mode of transport to use when calculating directions. Valid values are: Driving (default), Walking, Transit, Truck

distanceUnit

(optional) The units to use for distance in the response. Valid values are: Kilometer or km (default), Mile or mi.

dateTime

Required when the travel mode is Transit. Optional for Driving. The dateTime parameter identifies the desired transit time, such as arrival time or departure time. The transit time type is specified by the timeType parameter. Example: 2013-07-04T17:00:00Z, 03/01/2011 05:42:00, 05:42:00 [assumes the current day], 03/01/2011 [assumes the current time]

timeType

Required when the travel mode is Transit Specifies how to interpret the date and transit time value that is specified by the dateTime parameter. Valid values are: Arrival, Departure, LastAvailable (The dateTime parameter contains the latest departure time available for a transit request).

avoid

(optional) Specifies the road types to minimize or avoid when a route is created for the driving travel mode. Valid values are: highways, tolls, ferry, minimizeHighways, minimizeTolls, borderCrossing.

optimize

(optional) Specifies what parameters to use to optimize the route. Valid values are: distance, time (default), timeWithTraffic, timeAvoidClosure.

optimizeWaypoints

(optional) Instructs the API to rearrange the route waypoints and reduce the route cost specified with the optimize parameter. The route first waypoint wp.0 and last waypoint wp.n order is not changed, their position is considered fixed. Valid values are: true or false.

options

(optional) Set extra parameters: "parameter1:value1&parameterN:valueN&". See https://docs.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-truck-route

bsDirectionsRegionTravelSummary(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]; [fields]) B

Example

bsDirectionsRegionTravelSummary(array_of_strings; string; string; date_and_time; string; string; string; string; string; string)

Description

A custom function that gets the travel summary of distance, time, and toll road distance by two entity types: country (e.g. US, Canada) and administrative division or subregion (e.g. “state” in US and “province” in Canada).

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

mode

(optional) specifies what mode of transport to use when calculating directions. Valid values are: Driving (default), Walking, Transit, Truck

distanceUnit

(optional) The units to use for distance in the response. Valid values are: Kilometer or km (default), Mile or mi.

dateTime

Required when the travel mode is Transit. Optional for Driving. The dateTime parameter identifies the desired transit time, such as arrival time or departure time. The transit time type is specified by the timeType parameter. Example: 2013-07-04T17:00:00Z, 03/01/2011 05:42:00, 05:42:00 [assumes the current day], 03/01/2011 [assumes the current time]

timeType

Required when the travel mode is Transit Specifies how to interpret the date and transit time value that is specified by the dateTime parameter. Valid values are: Arrival, Departure, LastAvailable (The dateTime parameter contains the latest departure time available for a transit request).

avoid

(optional) Specifies the road types to minimize or avoid when a route is created for the driving travel mode. Valid values are: highways, tolls, ferry, minimizeHighways, minimizeTolls, borderCrossing.

optimize

(optional) Specifies what parameters to use to optimize the route. Valid values are: distance, time (default), timeWithTraffic, timeAvoidClosure.

optimizeWaypoints

(optional) Instructs the API to rearrange the route waypoints and reduce the route cost specified with the optimize parameter. The route first waypoint wp.0 and last waypoint wp.n order is not changed, their position is considered fixed. Valid values are: true or false.

options

(optional) Set extra parameters: "parameter1:value1&parameterN:valueN&". See https://docs.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-truck-route

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/details 

bsDirectionsMap(waypoints; [mode]; [distanceUnit]; [dateTime]; [timeType]; [avoid]; [optimize]; [optimizeWaypoints]; [options]; [imagerySet]) B

Example

bsDirectionsMap(array_of_strings; string; string; date_and_time; string; string; string; string; string; string)

Description

Use bsDirectionsMap to return the static map with the route calculated by using the waypoints in the order they are given.

This function to be used with formula =IMAGE(bsDirectionsMap(...)). See https://docs.microsoft.com/en-us/bingmaps/rest-services/imagery/get-a-static-map

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

mode

(optional) specifies what mode of transport to use when calculating directions. Valid values are: Driving (default), Walking, Transit, Truck

distanceUnit

(optional) The units to use for distance in the response. Valid values are: Kilometer or km (default), Mile or mi.

dateTime

Required when the travel mode is Transit. Optional for Driving. The dateTime parameter identifies the desired transit time, such as arrival time or departure time. The transit time type is specified by the timeType parameter. Example: 2013-07-04T17:00:00Z, 03/01/2011 05:42:00, 05:42:00 [assumes the current day], 03/01/2011 [assumes the current time]

timeType

Required when the travel mode is Transit Specifies how to interpret the date and transit time value that is specified by the dateTime parameter. Valid values are: Arrival, Departure, LastAvailable (The dateTime parameter contains the latest departure time available for a transit request).

avoid

(optional) Specifies the road types to minimize or avoid when a route is created for the driving travel mode. Valid values are: highways, tolls, ferry, minimizeHighways, minimizeTolls, borderCrossing.

optimize

(optional) Specifies what parameters to use to optimize the route. Valid values are: distance, time (default), timeWithTraffic, timeAvoidClosure.

optimizeWaypoints

(optional) Instructs the API to rearrange the route waypoints and reduce the route cost specified with the optimize parameter. The route first waypoint wp.0 and last waypoint wp.n order is not changed, their position is considered fixed. Valid values are: true or false.

options

(optional) Set extra parameters: "parameter1:value1&parameterN:valueN&". See https://docs.microsoft.com/en-us/bingmaps/rest-services/routes/calculate-a-truck-route

imagerySet

(optional) The type of imagery. Valid values are: Aerial, AerialWithLabels, AerialWithLabelsOnDemand, Streetside, BirdsEye, BirdsEyeWithLabels, Road (default), CanvasDark, CanvasLight, CanvasGray.

bsDirectionsUrl(waypoints; [mode]; [imagerySet]) B

Example

bsDirectionsUrl(array_of_strings; string; string)

Description

A custom function that gets the URL on the route on map.

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).

mode

(optional) specifies what mode of transport to use when calculating directions. Valid values are: Driving (default), Walking, Transit, Truck

imagerySet

(optional) The type of imagery. Valid values are: Aerial, AerialWithLabels, AerialWithLabelsOnDemand, Streetside, BirdsEye, BirdsEyeWithLabels, Road (default), CanvasDark, CanvasLight, CanvasGray.

gsGetPlacePhoto(place_id; [photo_n]; [maxwidth]; [maxheight]) G

Example

=IMAGE(gsGetPlacePhoto(string; number; number; number))

Description

Use gsGetPlacePhoto to return the place photo. This function to be used with formula =IMAGE(gsGetPlacePhoto(...)). See https://support.google.com/docs/answer/3093333

This function to be used with formula =IMAGE(gsGetPointsMap(...))
See https://support.google.com/docs/answer/3093333

place_id

(required) A textual identifier that uniquely identifies a place.

photo_n

(optional) (default is 1) indicates the number of image of the place. Accepted values are from 1 to 10.

maxwidth

(optional) (default is 500) specifies the maximum desired width, in pixels, of the image returned. Accept an integer between 1 and 1600.

maxheight

(optional) (default is 500) specifies the maximum desired height, in pixels, of the image returned. Accept an integer between 1 and 1600.

gsGetStreetView(location; [width]; [height]; [heading]; [fov]; [pitch]; [radius]; [source]) G

Example

gsGetStreetView(string; number; number; number; number; number; number; string)

Description

Use gsGetStreetView to return the static street view. 

This function to be used with formula =IMAGE(gsGetPointsMap(...))
See https://support.google.com/docs/answer/3093333

location

(optional) can be either a text string (such as Chagrin Falls, OH) or a lat/lng value (40.457375,-80.009353).

width

(optional) specifies the output width of the image in pixels.

height

(required) specifies the output height of the image in pixels.

heading

(optional) indicates the compass heading of the camera. Accepted values are from 0 to 360. If no heading is specified, a value will be calculated that directs the camera towards the specified location.

fov

(optional) (default is 90) determines the horizontal field of view of the image. The field of view is expressed in degrees, with a maximum allowed value of 120.

pitch

(optional) (default is 0) specifies the up or down angle of the camera relative to the Street View vehicle. This is often, but not always, flat horizontal.

radius

(optional) (default is 50) sets a radius, specified in meters, in which to search for a panorama, centered on the given latitude and longitude.

source

(optional) limits Street View searches to selected sources. Valid values are: (default, outdoor). See https://developers.google.com/maps/documentation/streetview/intro

gsGetIsolineMapFrom(origins; mode; type; ranges; [departure]; [options]; [show_mode]) GH

Example

=IMAGE(gsGetIsolineMapFrom(value_or_array; string; string; value_or_array; date_and_time; string; number))

Description

Use gsGetIsolineMapFrom to calculate the area that can be reached for a given time or distance from single or multiple origins.

This function to be used with formula =IMAGE(gsGetIsolineMapFrom(...))
See https://support.google.com/docs/answer/3093333

origins

The starting positions.

mode

The mode, which describes the type of route calculation and whether to optimize the routing for traffic conditions or not: [fastest,shortest,balanced];[car(default),carHOV,pedestrian,truck];[traffic:(disabled,enabled)]

type

of range (distance, time)

ranges

of route (distance in meters, time in seconds)

departure

(optional) time. Example: 2013-07-04T17:00:00+02:00

options

(optional) See https://developer.here.com/documentation/routing/topics/resource-calculate-isoline.html#resource-calculate-isoline

show_mode

(optional) Isoline area fill mode:

-1 - no polygons, no intersection, 

0 - all polygons, no intersection, 

1 - no polygons, intersection of N polygons, 

2 - no contours and polygons, fill intersection only, 

3 - add intersection markers, 

4 - hide polygons markers

gsGetIsolineMapTo(destinations; mode; type; ranges; [arrival]; [options]; [show_mode]) GH

Example

=IMAGE(gsGetIsolineMapTo(value_or_array; string; string; value_or_array; date_and_time; string; number))

Description

Use gsGetIsolineMapTo to calculate the area that can be reached for a given time or distance to single or multiple destinations.

This function to be used with formula  =IMAGE(gsGetIsolineMapTo(...))
See https://support.google.com/docs/answer/3093333

destinations

The ending positions.

mode

The mode, which describes the type of route calculation and whether to optimize the routing for traffic conditions or not: [fastest,shortest,balanced];[car,carHOV,pedestrian,truck];[traffic:(disabled,enabled)]

type

of range (distance, time)

ranges

of route (distance in meters, time in seconds)

arrival

(optional) time. Example: 2013-07-04T17:00:00+02:00

options

(optional) See https://developer.here.com/documentation/routing/topics/resource-calculate-isoline.html#resource-calculate-isoline

show_mode

(optional) Isoline area fill mode:

-1 - no polygons, no intersection, 

0 - all polygons, no intersection, 

1 - no polygons, intersection of N polygons, 

2 - no contours and polygons, fill intersection only, 

3 - add intersection markers, 

4 - hide polygons markers

gsCommuteDistance(origin; destination; [mode]; [options]; [departure_time]; [arrival_time]) 

Example

gsCommuteDistance(String; String; string; string; date_and_time; date_and_time)

Description

A custom function that gets the total distance of the route.

origin

(required) The starting address.

destination

(required) The ending address.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

gsCommuteTime(origin; destination; [mode]; [options]; [departure_time]; [arrival_time]) 

Example

gsCommuteTime(String; String; string; string; date_and_time; date_and_time)

Description

A custom function that gets the total duration of the route.

origin

(required) The starting address.

destination

(required) The ending address.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

gsCommuteCost(origin; destination; [mode]; [options]; [departure_time]; [arrival_time]) 

Example

gsCommuteCost(String; String; string; string; date_and_time; date_and_time)

Description

A custom function that gets the total fare (that is, the total ticket costs) on this route.

origin

(required) The starting address.

destination

(required) The ending address.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

gsCommuteURL(origin; destination; [mode]; [options]; [departure_time]; [arrival_time]) 

Example

gsCommuteURL(String; String; string; string; date_and_time; date_and_time)

Description

A custom function that gets the URL on the route on map.

To show the URL in the cell use formula =HYPERLINK(). See https://support.google.com/docs/answer/3093313

origin

(required) The starting address.

destination

(required) The ending address.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

gsCommuteRouteMap(origin; destination; [mode]; [options]; [departure_time]; [arrival_time]) G

Example

=IMAGE(gsCommuteRouteMap(String; String; string; string; date_and_time; date_and_time))

Description

Use gsCommuteRouteMapto to return the static map with the route from origin to destination.

This function to be used with formula =IMAGE(gsCommuteRouteMap(...))
See https://support.google.com/docs/answer/3093333

origin

(required) The starting address.

destination

(required) The ending address.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time).

For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

generateRgbaColors(amount; [saturation]; [lightness]) 

Example

generateRgbaColors(number; number; number)

Description

Generates an array of RGB colors. Assumes s and l are contained in the set [0, 1] and returns r, g, and b in the set [0, 255].

amount

(required) The number of colors to generate. Limited by 360.

saturation

(optional) The saturation

lightness

(optional) The lightness

hslToRgba(h; s; l) 

Example

hslToRgba(number; number; number)

Description

Converts an HSL color value to RGB. Conversion formula adapted from http://en.wikipedia.org/wiki/HSL_color_space. Assumes h, s, and l are contained in the set [0, 1] and returns r, g, and b in the set [0, 255].

h

The hue

s

The saturation

l

The lightness

highlightDuplicates([colIndex]; [sheet]) I

Example

highlightDuplicates(character or number; sheet)

Description

Highlight rows with duplicates.

colIndex

(optional) column name or zero-based index to lookup duplicates 

sheet

(optional) sheet for action

gsMultiNearbyPlaces(where; radius; [fields]; [n_results]; [what]; [type]; [rankby]; [opennow]; [minprice]; [maxprice]; [language]; [total_results]; [geo_factor]) IG

Example

gsMultiNearbyPlaces(string; number; array_of_strings; number; string; string; string; string; string; string; string; number; array_of_numbers)

Description

A custom function that finds nearby places.

where

(required) The 'where' input can be a set of addresses of Places.

radius

(required) Defines the distance (in meters) within which to bias place results. The maximum allowed radius is 50 000 meters. See https://developers.google.com/places/web-service/search

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

Leave blank to include all available fields.

n_results

(optional) The limitation of returned results: 20 by default (if missing).

what

(optional) The 'what' input can be a set of types or names of Places. For types see https://developers.google.com/places/web-service/supported_types

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types

rankby

(optional) Specifies the order in which results are listed. Note that rankby must not be included if radius is specified. Possible values are: prominence (default), distance. When distance is specified, one or more of keyword, name, or type is required.

opennow

(optional) Returns only those places that are open for business at the time the query is sent.

minprice

(optional) Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

maxprice

(optional) Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

total_results

(optional) Total limitation of returned results for multiple conditions.

geo_factor

<optional> For geoJSON object only. Values: 0 - get only places inside geoJSON; <10 - the coefficient to be applied to the search radius (circumcircle); other - the search radius (circumcircle) is increased by the value.

gsMultiFindPlaces(what; [where; [n_results]; [fields]; [type]; [location]; [radius]; [language]; [total_results]; [geo_factor]; [platform]; [rankby]; [openNow]; [priceLevels]; [minRating]; [region]; [strictTypeFiltering]; [evOptions]) IG

Example

gsMultiFindPlaces(string; string; number; array_of_strings; string; string; number; string; number; array_of_numbers; string; string; string; string; string; string; string; string; string)

Description

A custom function that finds places. The search response will include a list of places. In case of missing fields parameter the header row is also included. Call this interactive function via UI menu 

Extensions - Maps for Sheets - Insert - Places - Batch text search

what

(required/optional) The 'what' input can be a set of types or names of Places. For types see https://developers.google.com/places/web-service/supported_types

where

(optional/required) The 'where' input can be a set of addresses of Places.

n_results

(optional) The limitation of returned results: 20 by default (if missing).

fields

(required) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

Leave blank to see all available fields.

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types

location

(optional) Prefer results in a specified area by specifying either lat/lng or address: [lat,lng][address]. If this parameter is not specified, the API uses IP address biasing by default.

radius

<optional> Defines the distance (in meters) within which to bias place results. The maximum allowed radius is 50 000 meters. See https://developers.google.com/places/web-service/search

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

total_results

(optional) Total limitation of returned results for multiple conditions.

geo_factor

<optional> For geoJSON object only. Values: 0 - get only places inside geoJSON; <10 - the coefficient to be applied to the search radius (circumcircle); other - the search radius (circumcircle) is increased by the value.

platform

(optional) Maps API platform: 'google'(default), 'google2'(Places API New) or 'here'.

rankby

(optional) google2: The type of ranking to use. If this parameter is omitted, results are ranked by popularity. May be one of the following: POPULARITY (default) Sorts results based on their popularity, DISTANCE Sorts results in ascending order by their distance from the specified location. Not compatible with locationRestriction.

openNow

(optional) google2: If true, return only those places that are open for business at the time the query is sent. If false, return all businesses regardless of open status. Places that don't specify opening hours in the Google Places database are returned if you set this parameter to false.

priceLevels

(optional) google2: Restrict the search to places that are marked at certain price levels. The default is to select all price levels. See https://developers.google.com/maps/documentation/places/web-service/text-search#pricelevels

minRating

(optional) google2: Restricts results to only those whose average user rating is greater than or equal to this limit. Values must be between 0.0 and 5.0 (inclusive) in increments of 0.5. For example: 0, 0.5, 1.0, ... , 5.0 inclusive. Values are rounded up to the nearest 0.5. For example, a value of 0.6 eliminates all results with a rating less than 1.0.

region

(optional) google2: The region code used to format the response, specified as a two-character CLDR code value. There is no default value. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#regioncode

strictTypeFiltering

(optional) google2: Used with the includedType parameter. When set to true, only places that match the specified types specified by includeType are returned. When false, the default, the response can contain places that don't match the specified types.

evOptions

(optional) google2: Specifies parameters for identifying available electric vehicle (EV) charging connectors and charging rates. See https://developers.google.com/maps/documentation/places/web-service/text-search#evoptions

strictGeometryFiltering

(optional) google2: Used with the locationRestriction parameter in geoJSON format and fields containing location.

gsInsertHeatmap(insert_into; latitudes; longitudes; [weights]; [maptype]; [width]; [height]; [gradient]; [radius]; [opacity]; [maxIntensity]; [dissipating]) IG

Example

gsInsertHeatmap(string; array_of_numbers; array_of_numbers; array_of_numbers; string; number; number; array_of_colors; number; number; number; boolean)

Description

A custom function that returns the heatmap image. Call this interactive function via UI menu 

Extensions - Maps for Sheets - Insert - Heatmap - Heat map

insert_into

(required) The target cell where the heatmap will be inserted.

latitudes

(required) Latitudes of the points for the heatmap.

longitudes

(required) Longitudes of the points for the heatmap.

weights

(optional) Weights of the points for the heatmap.

maptype

(optional) Type of the map for building the heatmap. The following map types are available in the Maps JavaScript API: roadmap, satellite (default), hybrid, terrain. See https://developers.google.com/maps/documentation/javascript/maptypes

width

(optional) defines the width dimension of the map image.

height

(optional) defines the height dimension of the map image.

gradient

(optional) The color gradient of the heatmap, specified as an array of CSS color strings. All CSS3 colors — including RGBA — are supported except for extended named colors and HSL(A) values.

radius

(optional) The radius of influence for each data point, in pixels.

opacity

(optional) The opacity of the heatmap, expressed as a number between 0 and 1.

maxIntensity

(optional) The maximum intensity of the heatmap. By default, heatmap colors are dynamically scaled according to the greatest concentration of points at any particular pixel on the map. This property allows you to specify a fixed maximum. Setting the maximum intensity can be helpful when your dataset contains a few outliers with an unusually high intensity.

dissipating

(optional) Specifies whether heatmaps dissipate on zoom. When dissipating is false the radius of influence increases with zoom level to ensure that the color intensity is preserved at any given geographic location. Defaults to true.

geoPoint(points; [n_results]; [details]; [properties])

Example

geoPoint(array_of_points; number; value; Object)

Description

Creates Point GeoJSON object from point with coordinates or address.

points

(required) Latitude/longitude pair or address of the points.

n_results

(optional) The limitation of returned results.

details

(optional) One or more properties returned by geocode separated by a comma to assign to the object properties (non-zero values only). See https://developers.google.com/maps/documentation/geocoding/intro

properties

(optional) optional properties in JSON format: {“property1”: “value1”, “property2”: “value2” }

geoPolygon(points; [properties])

Example

geoPolygon(array_of_points; JSON_string)

Description

Takes GeoPoints, coordinates or addresses and creates Polygon GeoJSON object.

points

(required) geo-object, latitudes/longitudes or addresses of the polygon vertices.

properties

(optional) optional properties in JSON format: {“property1”: “value1”, “property2”: “value2” }

geoRectangle(points; width; height; [units]; [properties])

Example

geoRectangle(Object; number; number; string; JSON_string)

Description

Takes Point, coordinates or address and calculates the rectangle given size in meters (default), degrees, radians, miles, or kilometers.

points

(required) geo-object, latitudes/longitudes or address of the center point.

width

(required) width of the rectangle.

height

(required) height of the rectangle.

units

(optional) meters (default), miles, kilometers, degrees, or radians.

properties

(optional) optional properties in JSON format: {“property1”: “value1”, “property2”: “value2” }

geoCircle(points; radius; [units]; [steps]; [properties])

Example

geoCircle(Object; number; string; number; JSON_string)

Description

Takes Point, coordinates or address and calculates the circle polygon given a radius in meters (default), degrees, radians, miles, or kilometers; and steps for precision.

points

(required) geo-object, latitudes/longitudes or address of the center point.

radius

(required) Radius of the circle.

units

(optional) Meters (default), miles, kilometers, degrees, or radians.

steps

(optional) Number of steps. Default is 64.

properties

(optional) optional properties in JSON format: {“property1”: “value1”, “property2”: “value2”}

geoShape(searchtext; [n_results],[backoff])

Example

geoShape(string; number; number)

Description

Creates GeoJSON object from an address or search text. The returned data depends on the parameter 'backoff'.

searchtext

(required) address or search text.

n_results

(optional) The limitation of returned results.

backoff

(optional) If no shape data in the response then returns: -1 - nothing, 0 (default) - geoPoint, 1 - bounding box.

geoLineString(points, properties)

Example

geoLineString(array_of_points, string)

Description

Takes GeoPoints, coordinates or addresses and creates LineString Feature GeoJSON object.

points

(required) geo-object, latitudes/longitudes or addresses of the lineString points.

properties

(optional) optional properties in JSON format: {“property1”: “value1”, “property2”: “value2”}

geoBBox(geo)

Example

geoBBox(array_of_points)

Description

Takes GeoJSON objects, address or coordinates of points and calculates the bounding box of all input features, and returns an equivalent polygon.

geo

(required) geo-object, latitudes/longitudes or address of the points.

geoDraw([insert_into]; [geo]; [readOnly])

Example

geoDraw(string; geojson)

Description

Draw the shape interactively. Call this interactive function via UI menu.

insert_into

(optional) The target cell where the drawn shape will be inserted.

geo

(optional) GeoJSON shape to edit

readOnly

(optional) GeoJSON shape to display only

geoLoadJSON(url)

Example

geoLoadJSON(string)

Description

Loads GeoJSON object from the URL. Holes in polygons are not supported.

url

The URL of the GeoJSON object.

geoIsolineFrom(origin; mode; type; ranges; [departure]; [options])

Example

geoIsolineFrom(string; string; string; value_or_array; date_and_time; string)

Description

Calculates the region that can be reached for a given time or distance from given origin

origin

The starting position as geojson point or address/coordinates string.

mode

The mode, which describes the type of route calculation and whether to optimize the routing for traffic conditions or not: [fastest,shortest,balanced];[car(default),carHOV,pedestrian,truck];[traffic:(disabled,enabled)]

type

of range (distance, time)

ranges

of route (distance in meters, time in seconds)

departure

time. Example: 2013-07-04T17:00:00+02:00

options

See https://developer.here.com/documentation/routing/topics/resource-calculate-isoline.html#resource-calculate-isoline

geoIsolineTo(destination; mode; type; ranges; [arrival]; [options])

Example

geoIsolineTo(string; string; string; value_or_array; date_and_time; string)

Description

Calculates the region that can be reached from for a given time or distance to given destinations

destination

The ending position as geojson point or address/coordinates string.

mode

The mode, which describes the type of route calculation and whether to optimize the routing for traffic conditions or not: [fastest,shortest,balanced];[car,carHOV,pedestrian,truck];[traffic:(disabled,enabled)]

type

of range (distance, time)

ranges

of route (distance in meters, time in seconds)

arrival

time. Example: 2013-07-04T17:00:00+02:00

options

See https://developer.here.com/documentation/routing/topics/resource-calculate-isoline.html#resource-calculate-isoline

geoToMap(objects)

Example

geoToMap(geojson)

Description

Plots GeoGSON objects and returns the map image. This function to be used with formula =IMAGE(geoToMap(...)). See https://support.google.com/docs/answer/3093333

objects

input GeoJSON object or geoMapOptions object

geoMapOptions([width]; [height]; [mapType]; [language]; [zoom]; [center_lat]; [center_lng]; [style]; [tilt])

Example

geoMapOptions(number, number, string, string, number, string, string, array_of_strings, number)

Description

Creates options for GeoJSON objects plotting by geoToMap() function.

width

(optional) The width of the image in pixels.

height

(optional) The height of the image in pixels.

mapType

(optional) A constant value: ROADMAP(default), SATELLITE, TERRAIN, HYBRID.

language

(optional) A BCP-47 language identifier.

zoom

(optional) A value from zero to 21, inclusive.

center_lat

(optional) The address of the center or the latitude of the center.

center_lng

(optional) The longitude of the center.

style

(optional) One or more map style parameters. See https://developers.google.com/maps/documentation/maps-static/styling

tilt

(optional) Controls the automatic switching behavior for the angle of incidence of the map. The only allowed values are 0 and 45.

geoToKml(objects)

Example

geoToKml(geojson)

Description

Converts GeoGSON objects to KML.

objects

(required) input GeoJSON object(s) for conversion

gsDownload(arg; filename)

Example

gsDownload(range; string)

Description

Returns URL for data download from the selected cell(s).

arg

(required) cell(s) for downloading data from

filename

(required)

geoSetColor(geo; [color]; [strokeColor]; [strokeWeight])

Example

geoSetColor(Object; string; string; number)

Description

Sets the geo-object style.

geo

(required) geo-objects.

color

(optional) The fill color, a string in the format "0xrrggbb" or a constant value from https://developers.google.com/apps-script/reference/maps/color

strokeColor

(optional) The line color, a string in the format "0xrrggbb" or a constant value from https://developers.google.com/apps-script/reference/maps/color

strokeWeight

(optional) The width of lines in pixels.

geoSetMarkerIcon(geo; imageUrl)

Example

geoSetMarkerIcon(array_of_geo; string)

Description

Set the marker icon.

geo

(required) geo-points.

imageUrl

(required) Specifies a URL to use as the marker's custom icon. Images may be in PNG, JPEG or GIF formats, though PNG is recommended.

geoDistance(object1; object2; [units])

Example

geoDistance(Object; Object; string)

Description

Calculates minimal distance between two objects. Negative value means position inside the object.

object1

GeoJSON object

object2

GeoJSON object

units

(optional) can be meters (default), degrees, radians, miles, or kilometers

geoGetArea(geo)

Example

geoGetArea(Object)

Description

Takes one or more objects and returns their area in square meters.

geo

input GeoJSON object(s)

geoGetCentroid(geo)

Example

geoGetCentroid(Object)

Description

Takes GeoGSON object and returns the centroid using the mean of all vertices. This lessens the effect of small islands and artifacts when calculating the centroid of a set of polygons.

geo

input GeoJSON object

geoGetCenter(geo)

Example

geoGetCenter(Object)

Description

Takes GeoGSON object and returns the absolute center point of all features.

geo

input GeoJSON object

geoGetLatLng(geo)

Example

geoGetLatLng(Object)

Description

Takes GeoGSON object and unwrap the center coordinates.

geo

input GeoJSON object

geoGetLatLngString(geo)

Example

geoGetLatLng(Object)

Description

Takes GeoGSON object and returns the center coordinates as a string.

geo

input GeoJSON object

geoGetCoords(geo; [type])

Example

geoGetCoords(geojson; array_of_strings)

Description

Unwrap coordinates from a GeoJSON objects or an Array

geo

GeoJSON object to unwrap coordinates

type

(optional) One or more fields, specifying the types of geoJSON objects to return coordinates

geoProperties(input)

Example

geoProperties(Object)

Description

Return GeoGSON object properties.

input

GeoJSON objects to extract properties

geoSetProperty(geo; key; property)

Example

geoSetProperty(array_of_geo; string; string)

Description

Sets the geo-object property.

geo

(required) geo-objects.

key

(optional) Key for geo-object property.

property

(optional) Value for geo-object property. Use empty string to cleanup property.

geoSetProperties(geo; properties)

Example

geoSetProperties(array_of_geo; json_string)

Description

Sets the geo-object properties.

geo

(required) geo-objects.

properties

(optional) Add JSON object to geo-object properties: {“property1”: “value1”, “property2”: “value2” }. Use an empty object {} to cleanup properties.

geoGeometry(geo)

Example

geoGeometry(Object)

Description

Return GeoGSON object geometry.

geo

GeoJSON object to extract geometry

geoFlip(geo)

Example

geoFlip(geojson)

Description

Takes input GeoJSON objects and flips all of their coordinates from [x, y] to [y, x].

geo

GeoJSON object to flips coordinates

geoToTable(input; [fields])

Example

geoToTable(Object; Object)

Description

Output GeoGSON objects and returns the table with defined fields.

input

GeoJSON objects to output data

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma.

geoLinkContained(geo)

Example

geoLinkContained(Object)

Description

Checks if a geolink is contained in a GeoJSON object.

geo

GeoJSON object for check.

geoLinkResolve(geo)

Example

geoLinkResolve(Object)

Description

Resolves geolink contained in a GeoJSON object. Objects longer than the maximum allowed string length in the cell will cause an error.

geo

GeoJSON object to resolve link

geoIntersect(objects; ...)

Example

geoIntersect(geojson)

Description

Takes GeoGSON objects and finds their intersection.

objects

input GeoJSON object

geoUnion(geo; ...)

Example

geoUnion(geojson)

Description

Takes two or more GeoGSON polygons and returns a combined polygon.

geo

input GeoJSON object

geoSubtract(base; shapes; ...)

Description

geoSubtract(geojson; geojson)

Description

Takes two or more GeoGSON polygons and removes the area of a shape or set of shapes from a base shape. Holes in polygons are not supported by geoToMap.

base

GeoJSON object

shapes

GeoJSON objects for subtracting

geoGetChilds(geo; [type]; [items])

Example

geoGetChilds(Object; array_of_strings; number)

Description

Takes GeoGSON objects and returns child objects.

geo

input GeoJSON object

items

1-based indices of child objects to return

type

object to return like 'Point', 'Polygon'

geoContainsPoints(obj; args; ...)

Example

geoContainsPoints(Object; geojson)

Description

Finds and returns Points that fall within the GeoJSON object.

obj

Points must be within these GeoJSON object

args

Points as input search

geoNearbyPlaces(location; radius; [n_results]; [fields]; [keyword]; [type]; [rankby]; [opennow]; [minprice]; [maxprice])

Example

geoNearbyPlaces(string_or_geopoint; number; number; array_of_strings; string; string; string; string; string; string)

Description

Finds nearby places and returns GeoGSON points.

location

(required) Prefer results in a specified area by specifying either geopoint or lat/lng or address: [geojson][lat,lng][address].

radius

(required) Defines the distance (in meters) within which to return place results. The maximum allowed radius is 50 000 meters. Note that radius must not be included if rankby=distance. See https://developers.google.com/places/web-service/search

n_results

(optional) The limitation of returned results: 20 by default (if missing or with trial Google API key).

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

Leave blank to include all available fields.

keyword

(optional) A term to be matched against all content that Google has indexed for this place, including but not limited to name, type, and address, as well as customer reviews and other third-party content.

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types

rankby

(optional) Specifies the order in which results are listed. Note that rankby must not be included if radius is specified. Possible values are: prominence (default), distance. When distance is specified, one or more of keyword, name, or type is required.

opennow

(optional) Returns only those places that are open for business at the time the query is sent.

minprice

(optional) Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

maxprice

(optional) Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

geoFindPlaces2(query, [n_results]; [fields]; [type]; [location]; [radius]; [locationRestriction]; [rankby]; [openNow]; [priceLevels]; [minRating]; [language]; [region]; [strictTypeFiltering]; [evOptions])

Example

geoFindPlaces2(string, number, array_of_strings, string, string, number, string, string, string, string, string, string, string, string, string)

Description

A custom function that finds places.

query

(required) A Find Place request takes a text input, and returns a place. The text input can be any kind of Places data, for example, a name or address.

n_results

(optional) The limitation of returned results: 20 by default (if missing). Use your own Google Key to get over 20 records.

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. Use * to retrieve all fields. See https://developers.google.com/maps/documentation/places/web-service/text-search#fieldmask

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/maps/documentation/places/web-service/text-search#includedtype

location

(optional) Prefer results in a locationBias+radius specified area by specifying either lat and lng or address or geoJSON: ["lat,lng", "address"]. If this parameter is not specified, the API uses IP address biasing by default.

radius

(optional) Defines the distance (in meters) within which to bias place results. The maximum allowed radius is 50 000 meters. See https://developers.google.com/places/web-service/search

locationRestriction

(optional) Specify the region as a rectangular Viewport or address: A rectangle is a latitude-longitude viewport, represented as two diagonally opposite low and high points. You can specify locationRestriction or location+radius, but not both. You can only use geoJSON with a given location field. Think of locationRestriction as specifying the region which the results must be within, and location+radius as specifying the region that the results must be near but can be outside of the area. See https://developers.google.com/maps/documentation/places/web-service/text-search#location-restriction

rankby

(optional) The type of ranking to use. If this parameter is omitted, results are ranked by popularity. May be one of the following: POPULARITY (default) Sorts results based on their popularity, DISTANCE Sorts results in ascending order by their distance from the specified location. Not compatible with locationRestriction.

openNow

(optional) If true, return only those places that are open for business at the time the query is sent. If false, return all businesses regardless of open status. Places that don't specify opening hours in the Google Places database are returned if you set this parameter to false.

priceLevels

(optional) Restrict the search to places that are marked at certain price levels. The default is to select all price levels. See https://developers.google.com/maps/documentation/places/web-service/text-search#pricelevels

minRating

(optional) Restricts results to only those whose average user rating is greater than or equal to this limit. Values must be between 0.0 and 5.0 (inclusive) in increments of 0.5. For example: 0, 0.5, 1.0, ... , 5.0 inclusive. Values are rounded up to the nearest 0.5. For example, a value of 0.6 eliminates all results with a rating less than 1.0.

language

(optional) The language in which to return results. If languageCode is not supplied, the API defaults to en. If you specify an invalid language code, the API returns an INVALID_ARGUMENT error. See https://developers.google.com/maps/documentation/places/web-service/text-search#languagecode

region

(optional) The region code used to format the response, specified as a two-character CLDR code value. There is no default value. See https://developers.google.com/maps/documentation/places/web-service/nearby-search#regioncode

strictTypeFiltering

(optional) Used with the includedType parameter. When set to true, only places that match the specified types specified by includeType are returned. When false, the default, the response can contain places that don't match the specified types.

evOptions

(optional) Specifies parameters for identifying available electric vehicle (EV) charging connectors and charging rates. See https://developers.google.com/maps/documentation/places/web-service/text-search#evoptions

geoFindPlaces(query; [n_results]; [fields]; [type]; [location]; [radius]; [language])

Example

geoFindPlaces(string; number; array_of_strings; string; string; number; string)

Description

Finds places and returns GeoGSON points.

query

(required) A Find Place request takes a text input, and returns a place. The text input can be any kind of Places data, for example, a name or address.

n_results

(optional) The limitation of returned results: 20 by default (if missing or with trial Google API key).

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

Leave blank to include all available fields.

type

(optional) Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types

location

(optional) Prefer results in a specified area by specifying either lat/lng or address: [lat,lng][address]. If this parameter is not specified, the API uses IP address biasing by default.

radius

(optional) Defines the distance (in meters) within which to bias place results. The maximum allowed radius is 50 000 meters. See https://developers.google.com/places/web-service/search

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

geoBoundaries(place; level; [language])

Example

geoBoundaries(geojson_or_latlng_or_string; string; string)

Description

Returns GeoJSON objects for administrative area around a geo-point or areas inside a geo-shape. Important note: This function is very time consuming, therefore, to query a number of results, it is recommended to divide the query area into subareas, as well as use an interactive call via the Insert - Function menu. Some areas may not contain shape information.

place

(required) GeoJSON object, latlng or address string.

level

(required) Target match level of the search result: one of [Country, State, County, City, District, PostalCode].

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

geoFindWithinBoundaries(where; what; [fields]; [type]; [rankby]; [opennow]; [minprice]; [maxprice]; [language]; [total_results]; [geo_factor]; [grid_spacing]; [cell_results]; [platform]) IGH*

Example

geoFindWithinBoundaries(geojson_or_latlng_or_string; array_of_strings; array_of_strings; array_of_strings; number; string; string; string; string; number; number; number; string)

Description

A custom function that finds nearby places.

where

(required) The 'where' input can be a set of addresses of Places.

what

(required) The 'what' input can be a set of types or names of Places. For types see https://developers.google.com/places/web-service/supported_types

fields

(optional) One or more fields, specifying the types of place data to return, separated by a comma. See https://developers.google.com/places/web-service/search#PlaceSearchResults

Leave blank to include all available fields.

type

(optional) For Google Maps only. Restricts the results to places matching the specified type. Only one type may be specified (if more than one type is provided, all types following the first entry are ignored). See https://developers.google.com/places/web-service/supported_types

rankby

(optional) For Google Maps only. Specifies the order in which results are listed. Note that rankby must not be included if radius is specified. Possible values are: prominence (default), distance. When distance is specified, one or more of keyword, name, or type is required.

opennow

(optional) For Google Maps only. Returns only those places that are open for business at the time the query is sent.

minprice

(optional) For Google Maps only. Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

maxprice

(optional) For Google Maps only. Restricts results to only those places within the specified range. Valid values range between 0 (most affordable) to 4 (most expensive), inclusive.

language

(optional) The language code, indicating in which language the results should be returned, if possible. Note that some fields may not be available in the requested language. See https://developers.google.com/maps/faq#languagesupport

total_results

(optional) Total limitation of returned results for multiple conditions.

geo_factor

(optional) For geoJSON objects only. Values: 0 - get only places inside geoJSON; <10 - the coefficient to be applied to the search radius (circumcircle); other - the search radius (circumcircle) is increased by the value.

grid_spacing

(optional) Defines the grid spacing (in meters) to split the search area.

cell_results

(optional) Limiting returned results per cell: maximum value 60 (due to Google Maps API search limits)

platform

(optional) Maps API platform: 'google'(default), 'google2'(Places API New) or 'here'.

geoGetGrid(geo; radius; units; items)

Example

geoGetGrid(geojson; number; string; array)

Description

Returns an array of flat-topped hexagons

geo

(required) GeoJSON object

radius

(optional) length of the side of the hexagons or triangles, in units(default is 800).

units

(optional) meters (default), miles, kilometers, degrees, or radians.

items

(required) An array of element indices to return.

geoGetGridPoints(geo; [radius]; [units]; items)

Example

geoGetGridPoints(geojson; number; string; array)

Description

Returns an array of flat-topped hexagons

geo

(required) GeoJSON object

radius

(optional) length of the side of the hexagons or triangles, in units(default is 800).

units

(optional) meters (default), miles, kilometers, degrees, or radians.

items

(required) An array of element indices to return.

geoIsContain(geo1; geo2)

Example

geoIsContain(geojson; geojson)

Description

Returns True if the second geometry is completely contained by the first geometry

geo1

GeoJSON object

geo2

GeoJSON object

geoIsOverlap(geo1; geo2)

Example

geoIsOverlap(geojson; geojson)

Description

Compares two geometries and returns true if their intersection set results in a geometry different from both

geo1

GeoJSON object

geo2

GeoJSON object

geoCommuteLine(origin, destination, [mode], [options], [departure_time], [arrival_time])

Example

geoCommuteLine(String, String, string, string, date_and_time, date_and_time)

Description

A custom function that gets the route between 2 addresses or points as a lineString heoJSON object.

origin

(required) The starting address.

destination

(required) The ending address.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time). For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

geoRouteLine([mode], [options], [departure_time], [arrival_time], waypoints)

Example

geoRouteLine(string, string, date_and_time, date_and_time, array_of_strings)

Description

A custom function that gets the route between multiple addresses or points as a lineString heoJSON object.

mode

(optional) specifies what mode of transport to use when calculating directions (DRIVING,BICYCLING,TRANSIT,WALKING)

options

(optional) Set "optimize:true" to optimize the provided route by rearranging the waypoints in a more efficient order. See https://developers.google.com/maps/documentation/directions/intro

departure_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit: You can optionally specify one of departure_time or arrival_time. If neither time is specified, the departure_time defaults to now (that is, the departure time defaults to the current time). For requests where the travel mode is driving: You can specify the departure_time to receive a route and trip duration (response field: duration_in_traffic) that take traffic conditions into account. The departure_time must be set to the current time or some time in the future. It cannot be in the past.

arrival_time

(optional) Example: 2013-07-04T17:00:00+02:00 For requests where the travel mode is transit. You can specify either departure_time or arrival_time, but not both. Traffic conditions are not taken into account with this parameter.

waypoints

(required) Specifies an ordered list of origin and destination points and intermediate locations to include along the route as pass through or stopover locations. Waypoints alter a route by directing it through the specified location(s).