CODEWITHSUNDEEP
google-mapsgoogle-sheetsgoogle-sheets-apiinsightly
Greg Witkamp
Greg Witkamp

Reputation: 1

How to pull in Google Places data into Google sheets

I'm a very novice programmer, so please be gentle.

What I'm trying to do is pull in a locations formatted_phone_number and website from Google maps/places based on the coordinates exported from Google maps. The KML file exported from google maps only contains the name of the location and it's latitude and longitude.

I'm referring to Google Places API - Place Details. Here is the Sheet with the coordinates of restaurants that I want to get the phones numbers and website. This list will then be imported into Insightly as a csv file.

I found this google sheets script to get the address based on the geocode, which works:

var geocoder = Maps.newGeocoder(),
    cache = CacheService.getScriptCache();

function reverseGeocodeLatLngTest() {
  Logger.log(reverseGeocodeLatLng(51.70055389, 5.318333149));
}

function reverseGeocodeLatLng(latitude, longitude) {
//  var latitude = 51.69048,
//      longitude = 5.29362,
  var cacheKey = latitude + "+" + longitude,
    cached = cache.get(cacheKey);
  if(cached !== null) {
    return JSON.parse(cached);
  }
  Utilities.sleep(2000);

  var result = geocoder.reverseGeocode(latitude, longitude),
  //First response is the most specific
      response = result.results[0];
  if(response === undefined) {
    return "??";
  }
  function g(fieldName) {
    for (var i = 0; i < response.address_components.length; i++) {
      if(response.address_components[i].types.indexOf(fieldName) != -1) {
        return response.address_components[i];
      }
    }
    return {"long_name": "??"};
  }
  rValue = [[g("route").long_name, g("street_number").long_name, g("postal_code").long_name, g("locality").long_name, g('country').long_name, g('website').long_name]];
  cache.put(cacheKey, JSON.stringify(rValue));
  return rValue;
}

I figure that the script should be something close to this to get the place details of each location.

Thanks for any help.

Upvotes: 0

Views: 2421

Answers (1)

user41056
user41056

Reputation: 11

I just put together something for exactly this purpose - adding phone number to a list where I had the name of the company and the address.

You will need a Google API key. It's free, just takes a couple minutes to set up.

Hope this helps!

function phoneLookup(place, city, state) {
    var API_KEY = YOUR_API_KEY;
    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);
    placeId = obj.results[0].place_id;
    var url = 'https://maps.googleapis.com/maps/api/place/details/json?' +
      'placeid='+ placeId + '&fields=name,formatted_phone_number,website&key=' + 
      API_KEY;
    var response = UrlFetchApp.fetch(url);
    var json = response.getContentText();
    obj2 = JSON.parse(json);
    phoneNo = obj2.result.formatted_phone_number;
    web = obj2.result.website;
    phoneAndWeb = phoneNo + ', ' + web
    return phoneAndWeb
}

Upvotes: 1

Related Questions