Custom Functions in Google Sheets
Hereās a quick use case: Ā you have a list of addresses in a google spreadsheet (sheets) and you want to know their distance (mileage) from one location to another.
You can write a custom google sheets function, GET_DISTANCE(), that will take in two addresses and return the distance from an origin to a destination in miles. Ā This function will reach out to the Google Maps Distance Matrix API with a server API Key you create (and tied to your google account).
If you are new to programming and scripting, the good news is that this is super easy. Lets walk through this.
Step 1: Create the Google Sheet
Create a new Google Sheet, and populate one of the columns with some addresses. Lets assume your origin address is the list of addresses you have in this column, and the destination address never changes (for example, your work office location).
Create a new page in the same google sheet document, and in any of the cells type in your destination (office location) address. While we are at it, lets name this cell using named ranges too. Right-click the destination address cell, and select "define named range". Give it a name, we can use this name to reference this cell later.
Step 2: Add a script
In the Google Sheet, click on Tools > Script Editor. Select "Custom Functions in Sheets". Delete everything on the page, and copy/paste this function in:
/** * @OnlyCurrentDoc Limits the script to only accessing the current spreadsheet. */ /** * Take two string addresses, and use the google matrix distance API to * fetch the mileage (distance) between the two. * * @param {String} origin An Address that resolves in Google Maps. The Origin. * @param {String} destination Another address that varies from the first and * resolves in Google Maps. The Destination. * @param {String} apiKey The API Key to the Google Matrix API, see: https://developers.google.com/maps/documentation/distancematrix/intro * @return {String} the mileage text distance between both addresses. */ function GET_DISTANCE(origin, destination, apiKey) { Logger.log("Fetching distance between " + origin + " and " + destination); var matrixHost = "https://maps.googleapis.com/maps/api/distancematrix/json?origins="; var encodedOrigin = encodeURIComponent(origin); var encodedDestination = encodeURIComponent(destination); var requestUrl = matrixHost + encodedOrigin + "&destinations=" + encodedDestination + "&key=" + apiKey + "&units=imperial"; var resp = UrlFetchApp.fetch(requestUrl); Logger.log(resp); var jsonData = JSON.parse(resp); return jsonData.rows[0].elements[0].distance.text; }
What this function does is take in 2 addresses an an API Key. It fetches from the distancematrix API the distance, and returns the text of the distance in imperial units "12 mi".
Step 3: Use the script
In your Google Sheets, go to a cell, and type in: =DISTANCE(ORIGIN,DEST,API_KEY) and you'll make a call out to the custom google sheets function that then returns the distance text and populates that cell with it.
The possibilities with Google Sheets and custom functions are endless. Between the vast treasure trove of Google APIs, and the fact you can extend Sheets with custom scripts, makes Sheets that much more powerful as an every-day tool.
