构建脚本代码以将数据从Google Places API返回到Google Sheet [英] Building script code to get data from Google Places API back to Google Sheets
本文介绍了构建脚本代码以将数据从Google Places API返回到Google Sheet的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试为Google Sheet构建一个函数，以便从Google Places API获取某些地方的完整地址。
据我所知，为此我需要：
a)在Google Places API上构建要拍摄的查询(示例：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"
}
c)获取位置id(返回此位置id chIJ49bv_6lpGZURk4Hl8w31LRQ)
d)在Google Places API中重新搜索(示例：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, 10u003c/spanu003e - u003cspan class="extended-address"u003eJardim Americau003c/spanu003e, u003cspan class="locality"u003eSão Leopoldou003c/spanu003e - u003cspan class="region"u003eRSu003c/spanu003e, u003cspan class="postal-code"u003e93035-280u003c/spanu003e, u003cspan class="country-name"u003eBrazilu003c/spanu003e",
"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 LAMINADOSu003c/au003e"
],
"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
",
"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"
}
我用Google Sheet构建的第一个查询(这里有个例子：https://docs.google.com/spreadsheets/d/1w-dw82S23J-EVl1JHOS1ymhqxTNS5j2c5woFLEVqgAY/edit#gid=0)。
我的困难在于获取位置ID并再次运行查询，因此我使用脚本编辑器。我以这篇文章为基础来构建我的脚本：https://matthewbilyeu.com/blog/using-the-google-places-api-in-google-sheets/
因为我在工作表中创建查询，所以不需要使用function locUrlToQueryUrl(locationUrl)，我需要使用queryUrl作为输入运行Get_LOC，并将数据发送到工作表。我现在正在使用此代码：
我对JavaScript不是很熟悉，所以它对我来说不是很简单。下面的代码是我到目前为止获得的代码，但它没有将我需要的数据带回我的工作表。
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,
]];
}
当我在Google工作表中运行时：GET_LOC(<[3-0]>**)我收到#Error
编辑
我奇怪地收到两种类型的错误。
当我跑步时 GET_LOC(https://maps.googleapis.com/maps/api/place/findplacefromtext/json?input=cordoaria%20sao%20leopoldo%brasil&inputtype=textquery&key=AI**)
我得到：
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).
我得到：
Erro
TypeError: Não é possível ler a propriedade "types" de undefined. (linha 9).
(*Translation: TypeError: Not possible to read types property in undefined)
推荐答案
又是我。我再次审阅了你的问题，我想我更了解你想要做什么。您希望在Google Sheets的单元格中输入文本，使用文本搜索API检索位置ID，然后使用Places API检索有关该特定ID的数据
请看一下这份工作表 [链接已删除，因为它不再存在]在单元格B2中，我们检索位置id，在单元格c2中，我们从Google Places API检索字段。
也是下面的宏代码
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
]];
}
请注意，您可以在此处找到所有可用字段的完整列表，https://developers.google.com/places/web-service/details#fields您可以在宏代码中添加它们(我认为您足够聪明，可以计算出它们的位置)
不要忘记用您自己的API密钥替换。
这篇关于构建脚本代码以将数据从Google Places API返回到Google Sheet的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
