Retrieving location address based on place name and city in Google Spreadsheet

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.

score 5 · Accepted Answer · edited Jan 18 '16 at 21:23

5

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).

edited Jan 18 '16 at 21:23

Max Ghenis

14,783
16
84
132

answered Jan 03 '16 at 03:01

Ed Nelson

10,015
2
27
29

Thanks, this does work for the White House but it seems I picked a too-special example, as it fails for other place names. For example, searching for MOMA NY (spelled out) only give New York, NY (no address): http://maps.google.com/maps/api/geocode/json?address=museum%20of%20modern%20art%20new%20york,%20ny I've clarified my question to request coverage of this case. – Max Ghenis Jan 03 '16 at 19:11
1

Try MOMA or MoMA not spelled out. You also need either the city and/or state. That will return the address. – Ed Nelson Jan 03 '16 at 20:09
I see, indeed the API looks to be pretty finicky, more so than Google search. This approach still doesn't work for other addresses like Google Mountain View CA, so I've clarified again. Perhaps this is a limitation of Google Maps, so I wonder if other APIs would be better suited. http://maps.google.com/maps/api/geocode/json?address=google%20mountain%20view%20ca – Max Ghenis Jan 04 '16 at 00:55
1

@Max Ghenis. I think I may have found what you are looking for. The Google Places API. I have changes the URL in the above code to use it. It does require an API Key (Google Places API Web Services). It is free. It returns the exact addresses you listed above.. I hope this works for you. – Ed Nelson Jan 06 '16 at 16:55
1

No longer appears to work. Gives error on formatted_address. – Russ Dec 13 '18 at 00:40

score 1 · Answer 2 · answered Jul 21 '21 at 21:29

To avoid using the Maps API, you can use the Maps Geocoder class:

const GOOGLEMAPS_ADDRESS = (address) => {
   const { results: [data = {}] = [] } = Maps.newGeocoder().geocode(address);
   return data.formatted_address;
};

You can then use:

=GOOGLEMAPS_ADDRESS("White House")

and it will return:

1600 Pennsylvania Avenue NW, Washington, DC 20500, USA

Anything that is a specific enough search for Google Maps should work with this.

Even using:

=GOOGLEMAPS_ADDRESS("MOMA")

returns:

11 W 53rd St, New York, NY 10019, USA

This also works for cell references.

score 0 · Answer 3 · answered Sep 02 '19 at 12:08

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;
}

score -1 · Answer 4 · answered Sep 22 '16 at 22:24

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.

1

Site is broken now – Tom Söderlund Dec 30 '22 at 12:53

Retrieving location address based on place name and city in Google Spreadsheet

4 Answers4

Linked