Reputation: 4636
Building script code to get data from Google Places API back to Google Sheets
I'm trying to build a function for Google Sheets to get the complete address from certain places out of Google Places API.
As far as I understood, for that I need to:
a) Build the query to be shot on Google Places API (example: https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=cordoaria%20sao%20leopoldo%brasil&inputtype=textquery&key=AI***)
{
"candidates" : [
{
"place_id" : "ChIJ49bv_6lpGZURk4Hl8w31LRQ"
}
],
"debug_log" : {
"line" : []
},
"status" : "OK"
}
b) Shoot that query in Google Places API
c) Get the place id (it returned me this place id ChIJ49bv_6lpGZURk4Hl8w31LRQ)
d) Search it again in Google Places API (example: https://maps.googleapis.com/maps/api/place/details/json?placeid=ChIJ49bv_6lpGZURk4Hl8w31LRQ&key=AI****)
{
"html_attributions" : [],
"result" : {
"address_components" : [
{
"long_name" : "10",
"short_name" : "10",
"types" : [ "street_number" ]
},
{
"long_name" : "Avenida Padre Santini",
"short_name" : "Av. Padre Santini",
"types" : [ "route" ]
},
{
"long_name" : "Jardim America",
"short_name" : "Jardim America",
"types" : [ "sublocality_level_1", "sublocality", "political" ]
},
{
"long_name" : "São Leopoldo",
"short_name" : "São Leopoldo",
"types" : [ "administrative_area_level_2", "political" ]
},
{
"long_name" : "Rio Grande do Sul",
"short_name" : "RS",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "Brazil",
"short_name" : "BR",
"types" : [ "country", "political" ]
},
{
"long_name" : "93035-280",
"short_name" : "93035-280",
"types" : [ "postal_code" ]
}
],
"adr_address" : "\u003cspan class=\"street-address\"\u003eAv. Padre Santini, 10\u003c/span\u003e - \u003cspan class=\"extended-address\"\u003eJardim America\u003c/span\u003e, \u003cspan class=\"locality\"\u003eSão Leopoldo\u003c/span\u003e - \u003cspan class=\"region\"\u003eRS\u003c/span\u003e, \u003cspan class=\"postal-code\"\u003e93035-280\u003c/span\u003e, \u003cspan class=\"country-name\"\u003eBrazil\u003c/span\u003e",
"formatted_address" : "Av. Padre Santini, 10 - Jardim America, São Leopoldo - RS, 93035-280, Brazil",
"formatted_phone_number" : "(51) 3588-4411",
"geometry" : {
"location" : {
"lat" : -29.7828115,
"lng" : -51.125631
},
"viewport" : {
"northeast" : {
"lat" : -29.7814548697085,
"lng" : -51.1243209697085
},
"southwest" : {
"lat" : -29.78415283029149,
"lng" : -51.1270189302915
}
}
},
"icon" : "https://maps.gstatic.com/mapfiles/place_api/icons/generic_business-71.png",
"id" : "e210f5742b71e37687d16c2c83b3aa8ee34aba3d",
"international_phone_number" : "+55 51 3588-4411",
"name" : "Cordoaria São Leopoldo",
"opening_hours" : {
"open_now" : false,
"periods" : [
{
"close" : {
"day" : 1,
"time" : "1800"
},
"open" : {
"day" : 1,
"time" : "0730"
}
},
{
"close" : {
"day" : 2,
"time" : "1800"
},
"open" : {
"day" : 2,
"time" : "0730"
}
},
{
"close" : {
"day" : 3,
"time" : "1800"
},
"open" : {
"day" : 3,
"time" : "0730"
}
},
{
"close" : {
"day" : 4,
"time" : "1800"
},
"open" : {
"day" : 4,
"time" : "0730"
}
},
{
"close" : {
"day" : 5,
"time" : "1300"
},
"open" : {
"day" : 5,
"time" : "0730"
}
}
],
"weekday_text" : [
"Monday: 7:30 AM – 6:00 PM",
"Tuesday: 7:30 AM – 6:00 PM",
"Wednesday: 7:30 AM – 6:00 PM",
"Thursday: 7:30 AM – 6:00 PM",
"Friday: 7:30 AM – 1:00 PM",
"Saturday: Closed",
"Sunday: Closed"
]
},
"photos" : [
{
"height" : 4128,
"html_attributions" : [
"\u003ca href=\"https://maps.google.com/maps/contrib/108991705824884881816/photos\"\u003ePISOS LAMINADOS\u003c/a\u003e"
],
"photo_reference" : "CmRaAAAAM095blfyk5OxPChfdEee6IrF_BlCwb_d0sZgU0ocBOta23gu36ilzv45cvAFiREMP5KNxquCc8fvYEJPpIzq0Ov15l3_Nvh6HwmL-y5u1fMcXWoVmWMksrJbFCqhYzGZEhBuC-8VA5sNpe9OyXr4SENIGhSTo6qjN3EYqmV1JhlZQt_8dbnUTw",
"width" : 2322
}
],
"place_id" : "ChIJ49bv_6lpGZURk4Hl8w31LRQ",
"plus_code" : {
"compound_code" : "6V8F+VP São Leopoldo, State of Rio Grande do Sul, Brazil",
"global_code" : "582C6V8F+VP"
},
"rating" : 4.2,
"reference" : "ChIJ49bv_6lpGZURk4Hl8w31LRQ",
"reviews" : [
{
"author_name" : "Isaías Campos",
"author_url" : "https://www.google.com/maps/contrib/102714935576413122283/reviews",
"language" : "pt",
"profile_photo_url" : "https://lh6.googleusercontent.com/-PorBdnT8hWU/AAAAAAAAAAI/AAAAAAAAABs/4IwNQqXvBV4/s128-c0x00000000-cc-rp-mo/photo.jpg",
"rating" : 5,
"relative_time_description" : "a year ago",
"text" : "Lugar com segurança,e promove a mesma aos clientes, pessoas agradáveis,e o atendimento ótimo. ",
"time" : 1498601517
},
{
"author_name" : "Cristiana Roseli",
"author_url" : "https://www.google.com/maps/contrib/112730753282117419678/reviews",
"language" : "pt",
"profile_photo_url" : "https://lh4.googleusercontent.com/-JWM4X8ZBeiw/AAAAAAAAAAI/AAAAAAAAAAA/ABtNlbDmCkeuXnzP3ijjziLnUF4X7fEBAQ/s128-c0x00000000-cc-rp-mo/photo.jpg",
"rating" : 5,
"relative_time_description" : "4 years ago",
"text" : "fui funcionária da empresa cordoaria e tive o maior prazer em colaborar na produção de cabos e cordas .trabalhei apenas dois anos ,mas todos os dias que colaborei foi com imenso prazer por saber que é uma empresa de qualidade. uma verdadeira família ,juntos pelo mesmo objetivo: sempre fazer com que a cordoaria seja a referencia em tudo que produz. obrigada pela oportunidade e que cada dia seja de sucesso e prosperidade! abraços ! CRISTIANA FREITAS",
"time" : 1385663486
},
{
"author_name" : "Alexandre Voese",
"author_url" : "https://www.google.com/maps/contrib/103847514278555760957/reviews",
"language" : "pt",
"profile_photo_url" : "https://lh4.googleusercontent.com/-C0pc4NWTUWo/AAAAAAAAAAI/AAAAAAAAAB8/pgsnwrg6sPI/s128-c0x00000000-cc-rp-mo-ba4/photo.jpg",
"rating" : 5,
"relative_time_description" : "a year ago",
"text" : "Otimo , porem o espaço é um pouco pequeno",
"time" : 1482523930
},
{
"author_name" : "Joao Vitor Ferraz",
"author_url" : "https://www.google.com/maps/contrib/105277596827941636339/reviews",
"language" : "pt",
"profile_photo_url" : "https://lh4.googleusercontent.com/-_URdT97HheM/AAAAAAAAAAI/AAAAAAAAT-8/WSvZMBCBgPo/s128-c0x00000000-cc-rp-mo/photo.jpg",
"rating" : 5,
"relative_time_description" : "a year ago",
"text" : "Bom\n",
"time" : 1489669563
},
{
"author_name" : "Cleber Aguilhera Prusch",
"author_url" : "https://www.google.com/maps/contrib/111913700410068395411/reviews",
"profile_photo_url" : "https://lh5.googleusercontent.com/-I0UgUbchFNA/AAAAAAAAAAI/AAAAAAAALA0/prcvFWMLAeY/s128-c0x00000000-cc-rp-mo/photo.jpg",
"rating" : 1,
"relative_time_description" : "a month ago",
"text" : "",
"time" : 1537310447
}
],
"scope" : "GOOGLE",
"types" : [ "point_of_interest", "establishment" ],
"url" : "https://maps.google.com/?cid=1454087694985822611",
"utc_offset" : -180,
"vicinity" : "Avenida Padre Santini, 10 - Jardim America, São Leopoldo",
"website" : "http://www.csl.com.br/"
},
"status" : "OK"
}
The first query I'm building out of Google Sheets (here is an example: https://docs.google.com/spreadsheets/d/1w-dw82S23J-EVl1JHOS1ymhqxTNS5j2c5woFLEVqgAY/edit#gid=0).
My difficulty is to get the Place ID and run the query again, so I'm using script editor. I'm basing myself in this post to build my script: https://matthewbilyeu.com/blog/using-the-google-places-api-in-google-sheets/
Since I'm creating the query in sheets, I don't need to use that function locUrlToQueryUrl(locationUrl), I need to run the GET_LOC using my queryUrl as input and send the data to my sheet. I'm using this code right now:
I'm not very familiar with JavaScript so it's not very simple to me. The code below is where I got till now but it doesn't bring me the data I need back to my sheet.
function GET_LOC(queryUrl) {
if (queryUrl == '') {
return 'Give me a Google Places queryURL...';
}
var response = UrlFetchApp.fetch(queryUrl);
var json = response.getContentText();
var place = JSON.parse(json).results[0];
var place_types = place.types.join(", ");
return [[ place.name,
place.formatted_address,
place_types,
place.rating,
]];
}
When in my Google Sheets I run: GET_LOC(https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=cordoaria%20sao%20leopoldo%brasil&inputtype=textquery&key=AI**) I'm getting #ERROR
Edit
I'm strangely getting two types of errors.
When I run GET_LOC(https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=cordoaria%20sao%20leopoldo%brasil&inputtype=textquery&key=AI**)
I get:
Erro
Argumento inválido: (*Translation: Invalid argument)
https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=cordoaria%20sao%20leopoldo%brasil&inputtype=textquery&key=AI** (linha 6).
I get:
Erro
TypeError: Não é possível ler a propriedade "types" de undefined. (linha 9).
(*Translation: TypeError: Not possible to read types property in undefined)
Upvotes: 1
Views: 4566
Answers (2)
Reputation: 840
It's me again. I reviewed again your question and I think I better understand what you want to do... You want to enter a text in a cell in google sheets, use the text search API to retrieve a place id, then use the Places API to retrieve data about that specific ID
Please take a look at this worksheet [removed link as it no longer exists]
In cell B2, we retrieve the location id, and in cell c2 we retrieve the fields from google places API.
Also the macro code below
function locId(text) {
var API_KEY = 'AIXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
var baseUrl = 'https://maps.googleapis.com/maps/api/place/findplacefromtext/json';
var queryUrl = baseUrl + '?input=' + text + '&inputtype=textquery&key=' + API_KEY;
var response = UrlFetchApp.fetch(queryUrl);
var json = response.getContentText();
var placeId = JSON.parse(json);
return placeId.candidates[0].place_id;
}
function GET_DETAILS(id) {
var API_KEY = 'AIXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
var fields = 'name,rating,formatted_phone_number,formatted_address,photo';
var baseUrl = 'https://maps.googleapis.com/maps/api/place/details/json?placeid=';
var queryUrl = baseUrl + id + '&fields=' + fields + '&key='+ API_KEY;
if (id == '') {
return 'Give me a Google Places URL...';
}
var response = UrlFetchApp.fetch(queryUrl);
var json = response.getContentText();
var place = JSON.parse(json).result;
return [[ place.name,
place.formatted_phone_number,
place.rating,
place.formatted_address,
place.photo
]];
}
Please note that a complete list of all the available fields you can find here, https://developers.google.com/places/web-service/details#fields and you can add them in the macro code(I think you are smart enough to figure where)
Don't forget to replace the API key with your own.
Upvotes: 7
Reputation: 840
The problem wasn't from my result/results
Please check this google sheets [removed link as it no longer exists]
And the project code is this one below (I will remove my key soon)
function locUrlToQueryUrl(locationUrl) {
var API_KEY = 'AIXXXXXXXXXXXXXXXXXXXXXXXXXXXX';
var matches = locationUrl.match(/maps\/place\/(.*)\/@(.*),/);
var name = matches[1];
var latLon = matches[2];
var baseUrl = 'https://maps.googleapis.com/maps/api/place/textsearch/json';
var queryUrl = baseUrl + '?query=' + name + '&location=' + latLon + '&radius=500&key=' + API_KEY;
return queryUrl;
}
function GET_LOC(locationUrl) {
if (locationUrl == '') {
return 'Give me a Google Places URL...';
}
var queryUrl = locUrlToQueryUrl(locationUrl);
var response = UrlFetchApp.fetch(queryUrl);
var json = response.getContentText();
var place = JSON.parse(json).results[0];
var place_types = place.types.join(", ");
var price_level = [];
for (var i = 0; i < place.price_level; i++) { price_level.push('$'); }
price_level = price_level.join('')
return [[ place.name,
place.formatted_address,
place_types,
place.rating,
price_level ]];
}
I think the main takeout here is that in cell A1 you must have a GOOGLE MAPS URL. Meaning, any url that you get when you click on some place in google maps.
Let me know if that helped you in any way.
Google sheets and Appscript is a great product to use, do not let yourself discouraged by small drawbacks.
Upvotes: 1
Related Questions
- Trying to get data from this site into google sheets using app script
- Using Google Places API in Google Sheets
- Can't get data from API on my Google Spreadsheet
- Pulling data from an API using Google App Script
- Fetching data from external API to Google Sheets using Google Script
- Fetch data from Google Spreadsheet
- How to pull in Google Places data into Google sheets
- Adding google place API to a google sheet
- Simple Google Script using Sheets API
- Getting data from Google Spreadsheets