0

I want to get the postcode of a UK address (e.g 9 Mallinson Road, London) inside Google Sheets (e.g B1). I am using the below code in C1 =mapAddress(B1) to get the full postal address from Google (39 Mallinson Road, London, SW11 3BW). It works well. However, I want a script that just gets the postcode for the address and a separate script that gets just the lat and long for a postcode. I can't use a splitting formula as the position of the postcode within a full address isn't always the same. Can you help?

function mapAddress(place, city, state) {
  var API_KEY = 'AIzaSyBE49ssuchansampleapihere';
  var url = 'https://maps.googleapis.com/maps/api/place/textsearch/json?query=' +
    place + ' ' + city + ' ' + state + '&key=' + API_KEY;
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  obj = JSON.parse(json);
  addr = obj.results[0].formatted_address;
  return addr;
}
player0
  • 124,011
  • 12
  • 67
  • 124
zakl
  • 1
  • 2
  • Try using a regular expression to extract the well-defined postcode format from the input address string. Review regexp tutorials, and the javascript `string#match` function in your preferred javascript developer reference website. – tehhowch Mar 15 '19 at 23:50
  • Hey, many thanks. What do you mean by 'well-defined'? – zakl Mar 20 '19 at 15:23
  • Exactly that - there is a specification that is adhered to by design, i.e. `/\d{5}([ \-]\d{4})?/` matches a US zipcode, either the 5 or 9 digit format. See also https://stackoverflow.com/questions/164979/uk-postcode-regex-comprehensive and https://stackoverflow.com/questions/578406/what-is-the-ultimate-postal-code-and-zip-regex – tehhowch Mar 20 '19 at 16:25
  • Ah. Not sure that's going to work. It's UK postcodes, less well defined, length isn't fixed, found in different places within a formatted address. – zakl Mar 21 '19 at 18:48

0 Answers0