How to validate addresses in Google Sheets?

Question

I'm trying to validate a list of addresses in a google sheet column, and create from it a well formatted new column(s). I came across the Google Maps Address validation API but I'm not sure how to use it or if there is another better solution out there.

Answer 1

After more digging, I came to the following solution. I don't usually code with JS so there's probably lots to improve here...

In Sheets, Extensions / Apps Script, add:

/**
 * Format text address using Google Maps Places API.
 * @param {string} input The unformatted address to format.
 * @return The formatted address.
 * @customfunction
*/
function FORMATADDRESS(input) {

  String.prototype.addQuery = function (obj) {
    return this + "?" + Object.entries(obj).flatMap(([k, v]) => Array.isArray(v) ? v.map(e => `${k}=${encodeURIComponent(e)}`) : `${k}=${encodeURIComponent(v)}`).join("&");
  }
  var API_KEY = "YOUR_API_KEY" // need to create a gcloud account to get that, it's free within certain limits
  var options = {
    input: input,
    inputtype: 'textquery',
    fields: 'formatted_address', // comma-separated, e.g., 'address_components,formatted_address' //address_components doesn't work...
    key: API_KEY,
  }
  var url = 'https://maps.googleapis.com/maps/api/place/findplacefromtext/json'
  var full_url = url.addQuery(options)
  console.log(full_url)

  var response = UrlFetchApp.fetch(full_url);
  var out = JSON.parse(response.getContentText())
  console.log(out);
  if (out['candidates'].length != 1) {
    console.log("Not 1 place found but", out['candidates'].length)
    return NaN // Sheets doesn't handle Error object
  }
  var address = out['candidates'][0]
  var formatted_address = address['formatted_address']
  console.log(formatted_address);
  return formatted_address
}

After saving, in the spreadsheet, use with =FORMATADDRESS(your_cell/text).

BUT... this is not dummy proof!! Not handling addresses that don't correspond to unique address, and it can modify an address in weird ways. It'd be better to have incorporated address validation in the Google Forms that filled that in the first place, so that the input user would do this work.

Voila, hope this helps nonetheless!

---

addQuery function from: https://gist.github.com/tanaikech/70503e0ea6998083fcb05c6d2a857107