I have

Then, I want to be able to run the function with the custom menu feature. If TextFinder does not find the Place URL for the given place, it will look up the data and write it to the Sheet.

I wanted to limit the number of API calls with this and to make sure the data was written to the Sheet so that it does not need to be pulled each time the Sheet is reopened.

Finished Product

Big thanks to Lamblichus for sticking this out with me. I hope this helps other people some day.

Here is the finished 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 = "ENTER_GPS_COORDINATES_HERE"; // e.g. "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 = '';
  if (place.opening_hours && place.opening_hours.weekday_text) {
    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() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const FIRST_ROW = 2;
  const sourceData = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW 1, 6)
                          .getValues().filter(row => String(row[0]));
  for (let i = 0; i < sourceData.length; i  ) {
    const sourceRow = sourceData[i];
    if (sourceRow[4] === "") {
      const text = sourceRow[0];
      const data = COMBINED2(text);
      sheet.getRange(FIRST_ROW i, 2, 1, data.length).setValues([data]);
    }
  }
}

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

CodePudding user response：

Desired goal:

If I understand you correctly, for each value in column A, you want to retrieve some related data from Maps API and paste it to columns B-F, if column E is not currently populated.

Issues:

• You are only providing the last value from column A to COMBINED2, but you want to loop through all values in column A and fetch the desired information for all of them (as long as the Place URL -column E- is not already populated).
• If you want to avoid calling Maps API if the Place URL is not populated, using TextFinder after calling Maps API doesn't make sense; you don't limit your calls to the API if you do that. If you just want to check whether the Place URL column is populated, I'd suggest checking whether the cell is empty or not, and calling Maps API if it's empty.

Proposed workflow:

• Retrieve all values from the sheet, including not just column A but also E (for practical purposes, all 6 columns are fetched in the sample below, since it can be done in one call), using Range.getValues().
• Iterate through the rows (for example, using for), and for each row, check that the cell in E is populated.
• If the cell in E (Place URL) is empty, use the value in A as the parameter for COMBINED2 and write the resulting data to columns B-F, as you are currently doing.

Code sample:

function writeToSheet() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const FIRST_ROW = 2;
  const sourceData = sheet.getRange(FIRST_ROW, 1, sheet.getLastRow()-FIRST_ROW 1, 6)
                          .getValues().filter(row => String(row[0]));
  for (let i = 0; i < sourceData.length; i  ) {
    const sourceRow = sourceData[i];
    if (sourceRow[4] === "") {
      const text = sourceRow[0];
      const data = COMBINED2(text);
      sheet.getRange(FIRST_ROW i, 2, 1, data.length).setValues([data]);
    }
  }
}

Update:

For names in which Places API doesn't return opening_hours, consider checking if this exists first:

function COMBINED2(text) {

  // ... REST OF YOUR FUNCTION ...

  var weekdays = '';
  if (place.opening_hours && place.opening_hours.weekday_text) {
    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;
}

CodePudding user response：

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