Day: July 2, 2023

Bob Canning writes: I have a Google Spreadsheet with postal addresses in column A. Each week, a real estate agent copies a section of those addresses to a “upcoming tour” tab on our website. The tab is shared with other real estate agents so they can see the addresses in the order they will be viewed on the tour. I would like to make all of the addresses clickable so that people can easily navigate to the next location on the tour. Is this possible?

Make Addresses Clickable in Google Sheets

We can use custom functions in Google Sheets with the built-in HYPERLINK function to make any location clickable in the spreadsheet. And unlike other Google Maps functions, this approach doesn’t make any Maps API calls so there’s no restriction on the number of links that you can generate in a sheet.

Assuming that your postal addresses are in column A from row 2 to row 11, go to column B and paste the custom function. The first parameter refers to the cell, or range of cells, that contain the location that needs to be hyperlinked. You can set the second ‘satellite’ parameter to TRUE if you would like to link the map to the aerial view instead of the regular map view of Google Maps.

=GOOGLEMAPSLINK(A2:A11, FALSE)

The Google Maps Link function is obviously not part of Google Sheets but we can easily integrate it with the help of Google Apps Script.

Generate Maps URL with Apps Script

Open your Google Sheets spreadsheet. Click on “Extensions” in the top menu, then select “Apps Script.” In the Apps Script editor that opens, replace any existing code with the following function:

/**
 * Generate a Google Maps Link for any address
 *
 * @param {string} address - The postal address
 * @param {boolean} satellite - Show aerial view (TRUE or FALSE)
 * @returns {string} The Google Maps URL
 * @customFunction
 */

function GOOGLEMAPSLINK(address, satellite) {
  function createLink(query) {
    const baseUrl = "https://maps.google.com/?q=" + encodeURIComponent(query);
    const mapsUrl = baseUrl + (satellite ? "&t=k" : "");
    return mapsUrl;
  }

  return Array.isArray(address)
    ? address.map(createLink)
    : createLink(address);
}

The GOOGLEMAPSLINK function can generate map links for addresses in a single cell as well as a range of cells.

We can also add another column to the sheet that will create a clickable link with the address text. Paste the following ArrayFormula function in cell C1. See demo sheet.

=ArrayFormula(HYPERLINK(B2:B11,A2:A11))

The hyperlinked postal addresses can also be copied and pasted directly into Word, or any rich text editor, including HTML Mail for Gmail.