I started with this project hoping to be able to have the Google Places API automate the look up of specific business and place information. While I started with individual functions, I see that this created an inordinate amount of requests that blew through my free monthly Google Cloud credit. This was because I wrote functions which were being recalled every time the Sheet was opened in any instance.
Instead, I want to only have the functions run when I use the custom UI button to call it. But how can I specify that it should only run when places do not have their information already populated in the Sheet?
I would have entered the name of a place in Column A, and in Columns B - F, I will have the function find the requested information. My script looks at the name of the place in Column A and finds the Google Place ID. From there, it formats a URL for that Google Places entry and pulls in the requested information from the concatenated URL.
Here is my current code:
// This location basis is used to narrow the search -- e.g. if you were
// building a sheet of bars in NYC, you would want to set it to coordinates
// in NYC.
// You can get this from the url of a Google Maps search.
const LOC_BASIS_LAT_LON = "37.7644856,-122.4472203"; // e.g. "37.7644856,-122.4472203"
function COMBINED2(text) {
var API_KEY = 'AxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxQ';
var baseUrl = 'https://maps.googleapis.com/maps/api/place/findplacefromtext/json';
var queryUrl = baseUrl + '?input=' + text + '&inputtype=textquery&key=' + API_KEY + "&locationbias=point:" + LOC_BASIS_LAT_LON;
var response = UrlFetchApp.fetch(queryUrl);
var json = response.getContentText();
var placeId = JSON.parse(json);
var ID = placeId.candidates[0].place_id;
var fields = 'name,formatted_address,formatted_phone_number,website,url,types,opening_hours';
var baseUrl2 = 'https://maps.googleapis.com/maps/api/place/details/json?placeid=';
var queryUrl2 = baseUrl2 + ID + '&fields=' + fields + '&key='+ API_KEY + "&locationbias=point:" + LOC_BASIS_LAT_LON;
if (ID == '') {
return 'Give me a Google Places URL...';
}
var response2 = UrlFetchApp.fetch(queryUrl2);
var json2 = response2.getContentText();
var place = JSON.parse(json2).result;
var placeAddress = place.formatted_address;
var placePhoneNumber = place.formatted_phone_number;
var placeWebsite = place.website;
var placeURL = place.url;
var weekdays = '';
place.opening_hours.weekday_text.forEach((weekdayText) => {
weekdays += ( weekdayText + '
' );
} );
var data = [ [
place.formatted_address,
place.formatted_phone_number,
place.website,
place.url,
weekdays.trim()
] ];
return data;
}
// add menu
// onOpen is a special function
// runs when your Sheet opens
function onOpen() {
const ui = SpreadsheetApp.getUi();
ui.createMenu("Custom Menu")
.addItem("Get place info","COMBINED2")
.addToUi();
}
I received help on a separate post which advised that I use another function to call COMBINED2
but I am not sure whether that still applies with my change of plans.
// this function calls COMBINED2()
function call_COMBINED2() {
var ss = SpreadsheetApp.getActiveSheet();
var text = ss.getRange("A2").getValue();
var data = COMBINED2(text);
var dest = ss.getRange("B2:F2");
dest.setValues(data);
}
Should it make a difference, my plan for down the road will be to have two buttons in the custom UI. One will work to do the initial lookup of place data. The second will do a refresh. If a change is detected and a cell is changed/updated, then it will highlight in some fashion so that I can make note of this.
The project is part of how I travel. I will often make running Google Sheet lists of recommended and vetted places of interest, bars, and restaurants so that I can import the Sheet into a Google MyMap for reference when we're actually visiting. Over time, these Sheets/MyMaps tend to become obsolete with changes (especially with COVID). I hope this serves to future-proof them and make updating them easier.
See Question&Answers more detail:
os