Addrova

Geocode addresses in Google Sheets

You can turn a column of addresses into latitude and longitude with a small Apps Script. This guide uses the Addrova API, which is not live yet, so the base URL below is the planned one. [À VÉRIFIER : URL de production et disponibilité de la clé]

1. Store the key safely

In the Sheet, open Extensions, Apps Script, then Project Settings, Script properties, and add ADDROVA_API_KEY. Do not paste the key in a cell or in the code: anyone who can see the script can read it.

2. The script

function GEOCODE(address, country) {
  if (!address) return "";
  const key = PropertiesService.getScriptProperties().getProperty("ADDROVA_API_KEY");
  const res = UrlFetchApp.fetch("https://api.addrova.eu/v1/geocode", {
    method: "post",
    contentType: "application/json",
    headers: { "X-API-Key": key },
    payload: JSON.stringify({ q: String(address), country: country || undefined, limit: 1 }),
    muteHttpExceptions: true,
  });
  if (res.getResponseCode() !== 200) return "error " + res.getResponseCode();
  const r = JSON.parse(res.getContentText()).results[0];
  return r ? [[r.location.lat, r.location.lon, r.confidence_level]] : [["", "", "not found"]];
}

In a cell, =GEOCODE(A2, "FR") returns three columns: latitude, longitude, confidence level.

3. Limits to know

  • Google caps the number of UrlFetchApp calls per day and the run time of a script; the limits depend on your account type. Check the current Apps Script quotas before running thousands of rows. [À VÉRIFIER : quotas Apps Script en vigueur]
  • A custom function must finish quickly (Google applies a short time limit to each call). [À VÉRIFIER : limite de durée en vigueur]
  • A custom function recalculates when the sheet reloads. For large lists, copy the values and paste them as values once done, so that you do not pay for the same addresses twice.
  • Each call is one request on your plan. For more than a few hundred rows, a file upload is cheaper and faster: see batch geocode a CSV.

4. Messy addresses

Look at the third column. Rows with a low confidence level deserve a look; POST /v1/repair corrects typos and abbreviations and returns what it changed, billed on success only.

5. Privacy

The addresses in your Sheet are sent to the API from Google's servers. Addrova writes no address to its logs (claim ZL-10, see the compliance page), but the Sheet and the script run in Google's environment: that part is yours to assess.

FAQ

Can I store the results in the Sheet? Yes. Keep the attribution that the API returns.

Is there an add-on? An add-on is planned; this script is the do-it-yourself version. [À VÉRIFIER]

Checked by hand: this code runs inside Google Sheets or Excel, so the automated example tests do not execute it. [À VÉRIFIER : essai sur le service réel]