Geocoding addresses in Google Sheets and Excel

This page adds latitude, longitude, and the matched address next to a column of addresses, in Google Sheets with a short Apps Script, or in Excel with Power Query. Both call Mapricot's search once for each address.

Each address is one request. The first 250,000 every 30 days are free, then they cost $0.10 per 1,000. See pricing. For a file of many thousands of addresses, batch geocoding a CSV file is faster.

Get a key

Create a server key with only the places scope in the dashboard. Anyone who can edit the spreadsheet or workbook can read the key, so don't give it other scopes. Set a cap on its use in the dashboard too.

Google Sheets

The script adds a Mapricot menu to the sheet. Choosing Geocode selected addresses fills the three columns to the right of the selection with the latitude, longitude, and matched address.

  1. Open the sheet, then choose Extensions → Apps Script.
  2. Replace the code in Code.gs with the script below, and save.
  3. Open Project Settings, the gear icon, and under Script Properties add a property named MAPRICOT_KEY with your key as its value.
  4. Reload the sheet. The Mapricot menu appears after a few seconds.
  5. Select the cells with the addresses, then choose Mapricot → Geocode selected addresses. The first time, Google asks you to authorize the script to reach an outside service.
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Mapricot")
    .addItem("Geocode selected addresses", "geocodeSelection")
    .addToUi();
}

function geocodeSelection() {
  const key = PropertiesService.getScriptProperties().getProperty("MAPRICOT_KEY");
  const range = SpreadsheetApp.getActiveRange();
  range.getValues().forEach(([address], index) => {
    if (!String(address).trim()) return;
    const response = UrlFetchApp.fetch(
      `https://api.mapricot.com/v1/search?limit=1&q=${encodeURIComponent(address)}`,
      { headers: { Authorization: `Bearer ${key}` } },
    );
    const match = JSON.parse(response.getContentText()).results[0];
    const row = match ? [match.lat, match.lon, match.label] : ["", "", "No match"];
    range.offset(index, 1, 1, 3).setValues([row]);
  });
}

The script writes plain values, not formulas, so the sheet doesn't geocode again when it recalculates, and you pay for each address once. The three columns to the right of the selection are overwritten, so leave them empty.

Apps Script stops a run after six minutes, and an error from the API, such as an invalid key, stops it too. Rows already done keep their results, so select the rest of the column and run it again.

Excel

Excel's WEBSERVICE function can't send the key in a header, so this uses Power Query, which is built into Excel. It makes a Geocode function, then adds its results as new columns of a table.

  1. Put the addresses in a table with an address column: select them, then choose Insert → Table.

  2. Choose Data → Get Data → From Other Sources → Blank Query. In the Power Query Editor, choose Advanced Editor, replace the query with the one below, and put your key in place of mapricot_sk_…. Choose Done, and rename the query Geocode.

  3. Select a cell in your table, and choose Data → From Table/Range.

  4. Choose Add Column → Invoke Custom Function. Pick Geocode, and the address column for its argument.

  5. When Power Query asks how to connect to https://api.mapricot.com, choose Anonymous. The key travels in the query's header, and Power Query only sends that header on anonymous connections.

    If it asks about privacy levels, give the workbook and https://api.mapricot.com the same level, such as Organizational. The addresses are sent to Mapricot to geocode them.

  6. The new column holds a record for each row. Choose the expand button in its header, tick lat, lon, and label, and choose OK.

  7. Choose Home → Close & Load to put the results in a new sheet.

(address) =>
let
    query = if address = null then "" else Text.Trim(Text.From(address)),
    Response = Json.Document(Web.Contents("https://api.mapricot.com", [
        RelativePath = "v1/search",
        Query = [q = query, limit = "1"],
        Headers = [Authorization = "Bearer mapricot_sk_…"]
    ])),
    Results = Response[results]
in
    if query = "" or List.IsEmpty(Results) then null else Results{0}

Power Query geocodes every row again each time the query refreshes, and each refresh is billed. Once the results look right, copy them and paste them as values, or don't refresh the query.

Check the matches

Geocoding works best on complete addresses with a city and country, such as 128 SW 3rd Ave, Portland, OR. Compare the matched address with yours to spot a wrong match. A match for only a town or a street puts the point in the middle of it, not at the building. Geocoding explains the match types.

Questions

Is there a geocoding formula for Google Sheets? Sheets has no built-in geocoding formula. A custom function, such as =GEOCODE(A2), is possible in Apps Script, but Sheets can run it again whenever the sheet recalculates, and each run is a billed request. The menu above writes plain values instead.

Can I geocode in Excel without Power Query? Not with a key in a header, which Mapricot needs. Another option is to save the sheet as CSV, run the batch geocoding script, and open the result in Excel.

Can I keep the coordinates? Yes. Mapricot's results are built on open data, and you can store them permanently. Show the data credits if you display them: see geocoding.