Retrieving location address based on place name an

Question

I'd like to retrieve a place's location based on the name and address in Google Sheets, something like:

=PlaceAddress("White House, Washington, DC")
=PlaceAddress("Moma, New York, NY")
=PlaceAddress("Google, Mountain View, CA")

which would return results as they (respectively) appear in Google search, i.e.:

1600 Pennsylvania Ave NW, Washington, DC 20500

11 W 53rd St, New York, NY 10019

1600 Amphitheatre Pkwy, Mountain View, CA 94043

The Maps Apps Script service has a geocoder, but this seems to require address, not place name. I'm guessing the solution involves either some functionality I missed from Apps Script, or something like the ImportData function to get structured data from Google search or another service.

Answer 1

The Google Places API can do this (request a key here). Set this up:

B1 = White House

B2 = Washington

B3 = DC

A1:A3 Can be place, city, state

enter the user defined formula in any cell as follows:

=mapAddress(B1, B2, B3)

In the text editor copy and paste:

function mapAddress(place, city, state) {
  var API_KEY = 'yourapikeyhere';
  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;
}

The Places API has limits of 1,000 requests per day, but this can be increased to 150k per day by verifying your identity (still free).

Answer 2

Instead of Google Places API you can also use Google Maps Geocoding API. The code from above just needs to be adjusted a bit:

function mapAddress(address) {
  var API_KEY = 'yourapikeyhere';
  var url = 'https://maps.googleapis.com/maps/api/geocode/json?address=' + address + '&lang=en&key=' + API_KEY;
  var response = UrlFetchApp.fetch(url);
  var json = response.getContentText();
  obj = JSON.parse(json);
  addr = obj.results[0].formatted_address;
  return addr;
}

Answer 3

You can do this using a free add-on called GeoSheets. Once you install the add-on, you can use the following function in your Google Spreadsheet:

=GEO_ADDRESS("White House, Washington, DC")

Which will return the address formatted like:

White House, 600 17th St NW, Washington, District of Columbia 20006, United States

Check the docs if you want to extract only part of the address like the city, region, postcode, country or geocoded coordinates. It's also easy to plot these locations on a map using the =GEO_MAP function.