[Solved] How can I properly call a function with a custom UI button with a growing Google Sheet?

by


The onOpen trigger in your script is only for adding the Custom Menu in your Sheet. The function will only get executed when the user selected an Item in the menu that is associated with the function. In your example, clicking Get place info will execute the COMBINED2 function.

enter image description here

Also, executing the script only when the place information is not present in the sheet is not possible, you have to run the script to get the identifier of the place and compare it to the data in the Sheet. In your example, place.url can be used as identifier. The only thing you can do is to prevent the script from populating the Sheet.

Here I updated your script by changing the function associated to the Get place info to writeToSheet(). writeToSheet() will call COMBINED2(text) to get the place information and use TextFinder to check if the place url exists in the Sheet. If the result of TextFinder is 0, it will populate the Sheet.

// const LOC_BASIS_LAT_LON = "40.74516247433546, -73.98621366765811"; // e.g. "37.7644856,-122.4472203"
const LOC_BASIS_LAT_LON = "37.7644856,-122.4472203";

function COMBINED2(text) {
  var API_KEY = 'enter api key here';
  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 weekdays="";
  place.opening_hours.weekday_text.forEach((weekdayText) => {
    weekdays += ( weekdayText + '\r\n' );
  } );

  var data = [
    place.name,
    place.formatted_address,
    place.formatted_phone_number,
    place.website,
    place.url,
    weekdays.trim()
  ];

  return data;
}

function getColumnLastRow(range){
  var ss = SpreadsheetApp.getActiveSheet();
  var inputs = ss.getRange(range).getValues();
  return inputs.filter(String).length;
}

function writeToSheet(){
  var ss = SpreadsheetApp.getActiveSheet();
  var lastRow = getColumnLastRow("A1:A");
  var text = ss.getRange("A"+lastRow).getValue();
  var data = COMBINED2(text);
  var placeCid = data[4];
  var findText = ss.createTextFinder(placeCid).findAll();
  if(findText.length == 0){
    ss.getRange(lastRow,2,1, data.length).setValues([data])
  }
}

function onOpen() {
  const ui = SpreadsheetApp.getUi();  
  ui.createMenu("Custom Menu")
      .addItem("Get place info","writeToSheet")
      .addToUi();
}

Output:

Example 1:

enter image description here

After clicking custom menu:

enter image description here

Example 2:

enter image description here

After clicking custom menu:

enter image description here

Note: Since we are using lastRow, new entry must be inserted below the last row of column A. Otherwise it will overwrite the last entry.

Reference:

6

solved How can I properly call a function with a custom UI button with a growing Google Sheet?