1

I would like to know how to retrieve an address based on name and city of a place in Google Sheets? I have read the answer to this question and followed the instructions, but the code from the answer in that post generated the following error for me:

TypeError: Cannot read property "formatted_address" from undefined. (line 8, file "Code")

Going to the linked question above will show you the code in the answer, but here is the short version:

After obtaining a key for the Google Places API, enter the user defined formula in any cell in Google Sheets 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;
}

I do not have a high enough score to post comments on that question, which is why I must ask a new question. Is there a Google Sheets template or add-in that achieves this?

Community
  • 1
  • 1
graymoment
  • 11
  • 3

1 Answers1

0

You need to enable the Google Places API Web Service in your Google Apps Script project in the Developers Console, follow the steps below:

  1. In the Script Editor, click menu Resources > Developers Console Project.
  2. Click the link named something like: YourProjectName - project-id-sveeczngccmxxerwwzx
  3. In the left menu click Library and search under the Google Maps APIs the option Google Places API Web Service
  4. At the top of the page click ENABLE and run the script again.
ocordova
  • 1,788
  • 2
  • 13
  • 20
  • Yes, I followed the instructions for enabling the Google Places API Web Service, but had trouble getting this to work for me. I did eventually get it to work after deleting the entire column and starting with a new column. Possibly there was something odd in the formatting of one of the cells. – graymoment Sep 01 '16 at 01:11
  • Maybe something wrong with value of the place, city or state (maybe some extra spaces?). Your code was fine, the only reason I thought to get that error was if the API wasn't enabled, for example if you log the `response` without enabling the API with `Logger.log(response)` you get a request denied [(image)](http://i.stack.imgur.com/upMin.png) and inevitable throws the [error](http://i.stack.imgur.com/zZLrl.png) you mentioned because `response` becomes `undefined` and you can't access to the property `formatted_address`. But I'm glad it now works now (: – ocordova Sep 01 '16 at 16:49
  • Remember you can always test your function by running it directly from the Script Editor just by defining the parameters inside the function like this ([image](http://i.stack.imgur.com/iVbwr.png)) – ocordova Sep 01 '16 at 16:59