Cleanse addresses in Google Sheets with =DATAVASK()

Add a custom function =DATAVASK(A2) to Google Sheets with Apps Script that cleanses addresses, caches the answers and can write fixed values back.

4 min Level: Beginner ✓ Code tested
google-sheetsapps-scriptdatavaskregneark

With a small Apps Script your Google spreadsheet gets a new function, =DATAVASK(A2), that looks up the address in A2 and returns the cleansed address. You can also ask for a single field, such as postcode or municipality. The script caches answers, so the same address does not cost a new call every time the sheet recalculates.

1. Store the key in the script properties

  1. Create a key under Keys.
  2. Open the spreadsheet and choose Extensions → Apps Script.
  3. Click the cog (Project Settings), scroll to Script Properties, and add the property DANADRESSE_API_KEY with the key as its value.

The key then lives outside the code. If you share the spreadsheet, other editors can still open the script and read the properties, so use a key you can revoke. A domain-locked browser key does not work here, because Google's servers send no Origin.

2. Paste the script

Delete the contents of Code.gs, paste the code and save.

JavaScript#apps-script
/**
 * Cleanses a Danish address with Danadresse.
 * @param {string} adresse The full address, e.g. "Strandvejen 100, 2900 Hellerup".
 * @param {string} felt Optional: kategori, id, vejnavn, husnr, postnr, by, kommune, kommunekode, x or y.
 * @return The cleansed address, the chosen field or an error text.
 * @customfunction
 */
function DATAVASK(adresse, felt) {
  if (Array.isArray(adresse)) {
    return adresse.map((raekke) => [DATAVASK(raekke[0], felt)]);
  }
  if (!adresse) return "";
  const r = vask_(String(adresse).trim());
  if (r.fejl) return r.fejl;
  if (!felt) return `${r.vejnavn} ${r.husnr}, ${r.postnr} ${r.by}`;
  return r[felt] ?? "";
}

function vask_(betegnelse) {
  const cache = CacheService.getScriptCache();
  const digest = Utilities.computeDigest(
    Utilities.DigestAlgorithm.MD5, betegnelse, Utilities.Charset.UTF_8);
  const cacheNoegle = "dv:" + Utilities.base64EncodeWebSafe(digest);
  const gemt = cache.get(cacheNoegle);
  if (gemt) return JSON.parse(gemt);

  const apiKey = PropertiesService.getScriptProperties().getProperty("DANADRESSE_API_KEY");
  if (!apiKey) return { fejl: "#NO KEY" };

  const svar = hent_("https://api.danadresse.dk/datavask/adgangsadresser?betegnelse="
    + encodeURIComponent(betegnelse), apiKey);
  if (svar.fejl) return svar; // Errors are not cached: try again later.
  const data = svar.data;
  const top = data.resultater.length ? data.resultater[0].adresse : null;
  let resultat = { fejl: "#NOT FOUND" };
  if (top) {
    // The cleanse answer has no municipality or coordinates: one more call for them.
    const adg = hent_("https://api.danadresse.dk/adgangsadresser/" + top.id, apiKey).data || {};
    const punkt = (adg.adgangspunkt && adg.adgangspunkt.koordinater) || [];
    resultat = {
      kategori: data.kategori,
      id: top.id,
      vejnavn: top.vejnavn,
      husnr: top.husnr,
      postnr: top.postnr,
      by: top.postnrnavn,
      kommune: adg.kommune && adg.kommune.navn,
      kommunekode: adg.kommune && adg.kommune.kode,
      x: punkt[0], // longitude
      y: punkt[1], // latitude
    };
  }
  cache.put(cacheNoegle, JSON.stringify(resultat), 21600); // 6 hours, the most Google allows
  return resultat;
}

function hent_(url, apiKey) {
  const valg = { headers: { "X-Api-Key": apiKey }, muteHttpExceptions: true };
  let res = UrlFetchApp.fetch(url, valg);
  // 429 rate_limit_per_minute carries Retry-After (1-60 s). Wait and try once more,
  // but only briefly: a custom function may run for at most 30 seconds.
  // 429 quota_exceeded has no Retry-After: the monthly quota is used up, waiting will not help.
  const vent = Number(header_(res, "retry-after"));
  if (res.getResponseCode() === 429 && vent > 0 && vent <= 20) {
    Utilities.sleep(vent * 1000);
    res = UrlFetchApp.fetch(url, valg);
  }
  const status = res.getResponseCode();
  if (status === 429) return { fejl: "#LIMIT REACHED" };
  if (status !== 200) return { fejl: "#ERROR " + status };
  return { data: JSON.parse(res.getContentText()) };
}

/** Header names may arrive in lower or mixed case. */
function header_(res, navn) {
  const h = res.getHeaders();
  const k = Object.keys(h).find((x) => x.toLowerCase() === navn);
  return k ? h[k] : null;
}

/** Menu to cleanse the selected cells and write fixed values next to them. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("Danadresse")
    .addItem("Cleanse selected addresses", "vaskMarkerede")
    .addToUi();
}

function vaskMarkerede() {
  const omraade = SpreadsheetApp.getActiveRange();
  const ud = omraade.getValues().map(([adresse]) => {
    if (!adresse) return ["", "", "", "", ""];
    const r = vask_(String(adresse).trim());
    return [r.fejl || r.kategori, r.id || "", r.postnr || "", r.by || "", r.kommune || ""];
  });
  omraade.offset(0, omraade.getNumColumns(), ud.length, 5).setValues(ud);
}

3. Use the function

Type in a cell:

FormulaResult
=DATAVASK(A2)Strandvejen 100, 2900 Hellerup
=DATAVASK(A2, "kategori")A
=DATAVASK(A2, "kommune")Gentofte
=DATAVASK(A2, "id")The access address UUID
=DATAVASK(A2:A50, "postnr")One postcode per row

With an English locale, arguments are separated by commas. With a Danish locale you use semicolons.

Category A is an exact match. B is one safe match with small spelling differences: use it, and keep the official text the function returns. C is uncertain (several candidates or larger differences): check C rows by hand and never accept them automatically. #NOT FOUND means the API found no candidate. #ERROR 401 or #ERROR 403 means the key is wrong or blocked.

The function cleanses at entrance level with /datavask/adgangsadresser, so every flat in an entrance gives the same result. For the exact flat, change the path to /datavask/adresser: the parser reads floor and door from the text (Strandvejen 100, 2. tv, 2900 Hellerup), and the result also holds etage, dør and adgangsadresseid. The second call still works with that id.

An address found in the cache costs nothing. One that is not costs two calls: the cleanse and the lookup of municipality and coordinates.

4. Formula or menu?

A formula recalculates when its argument changes, and the cache only lasts 6 hours. For a list you clean once, the menu is better: reload the sheet, select the address column, and choose Danadresse → Cleanse selected addresses. The script writes category, id, postcode, city and municipality into the five columns to the right as fixed values. The first time, Google asks you to authorise the script.

Google lets a custom function run for at most 30 seconds. Use small ranges such as A2:A50, or use the menu for long lists.

5. Usage

Each address not in the cache is two calls. On the free plan the account has 2,000 calls per month, plus a grace of 20 % before calls are refused. When that is used up too, the cells show #LIMIT REACHED until the 1st of next month. Refused calls are not counted. Check your usage under Usage and the rules in Limits, quotas and 429. With thousands of rows, upload a CSV under Bulk cleansing instead.

Test the call without a spreadsheet

Shell#test-call
curl -s "https://api.danadresse.dk/datavask/adgangsadresser?betegnelse=Strandvejen%20100%2C%202900%20Hellerup" \
  -H "X-Api-Key: $DANADRESSE_API_KEY"

Next steps

Related guides

Cleanse addresses in Excel with Power Query

4 minNo-code & spreadsheets

Clean a CSV export from your CRM

5 minTasks

Limits, quotas and 429

6 minGetting started

Markdown source Spotted an error? Tell us API-reference (Swagger)

Try it with a free key

2,000 calls/month on the free plan. No credit card.

Create account →