How can I make my Google Apps Script function work on cell input?

Question

I have a project that I've been working on for a bit. I've received some excellent help here, and I think I'm almost done and just need one more bit of help to get it working.

The script looks at a Google Sheet and takes a place name entered in Column A and uses the Google Places API to find requested information about it (address, phone number, etc.)

The last bit of help that I need will be able to implement the cell input component. The last user to help me said that

function writeToSheet(){
  var ss = SpreadsheetApp.getActiveSheet();
  var data = COMBINED2("Food");
  var placeCid = data[4];
  var findText = ss.createTextFinder(placeCid).findAll();
  if(findText.length == 0){
    ss.getRange(ss.getLastRow()+1,1,1, data.length).setValues([data])
  }
}

would be able use TextFinder to check if the place url exists in the Sheet. If the result of TextFinder is 0, it will call COMBINED2() to get the place information and populate the Sheet with writeToSheet()

They noted that

You can use a cell input in your COMBINED2 by using ss.getRange(range).getValue()

Not having a coding background, I have been able to stitch most of this together on my own, but I could use a bit of help in adding that capability to my code. Any help or guidance would be great.

Here is the code in full:

// 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 = "40.74516247433546, -73.98621366765816"; // e.g. "37.7644856,-122.4472203"

function COMBINED2(text) {
  var API_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxx';
  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 writeToSheet(){
  var ss = SpreadsheetApp.getActiveSheet();
  var data = COMBINED2("Food");
  var placeCid = data[4];
  var findText = ss.createTextFinder(placeCid).findAll();
  if(findText.length == 0){
    ss.getRange(ss.getLastRow()+1,1,1, data.length).setValues([data])
  }
}

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

So you want the parameter text in COMBINED2(text) to refer to a value from a certain cell? Do you know which cell from which sheet would that be? Maybe it would be the currently selected cell? — Iamblichus

jcommando jcommando · Accepted Answer · 2021-10-04T13:08:56

By using the event trigger function...

function onEdit(e){
SpreadsheetApp.getActiveSheet().getRange(insert your range in A1 format).setValue("anything you want to add into the cell")
}

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSheet();
  var data = COMBINED2("Food");
  var placeCid = data[4];
  var findText = ss.createTextFinder(placeCid).findAll();
  if(findText.length == 0){
    ss.getRange(ss.getLastRow()+1,1,1, data.length).setValues([data])
  }
}

u need to specifically tell google apps script that the function is as such so that your function will execute when a event object known as e has happened. You can read more about it on Simple Triggers

How can I make my Google Apps Script function work on cell input?

1 Answers