google-apps-script - 如何使用不断增长的 Google 表格使用自定义 UI 按钮正确调用函数？

Question

我从这个项目开始希望能够让 Google Places API 自动查找特定的业务和地点信息。当我开始使用单个功能时，我发现这会产生过多的请求，这些请求会耗尽我每月免费的 Google Cloud 信用额度。这是因为我编写了在任何情况下每次打开工作表时都会调用的函数。

相反，我只想让函数在我使用自定义 UI 按钮调用它时运行。但是，我如何才能指定它只应在地方尚未在工作表中填充其信息时运行？

我会在 A 列中输入一个地点的名称，在 B - F 列中，我会让函数找到请求的信息。我的脚本查看 A 列中的地点名称并找到 Google 地点 ID。从那里，它为该 Google Places 条目格式化一个 URL，并从连接的 URL 中提取请求的信息。

这是我当前的代码：

// 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 + '\r\n' );
  } );

  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();
  
}

我在另一篇文章中收到了帮助，该文章建议我使用另一个函数进行调用COMBINED2，但我不确定这是否仍然适用于我的计划更改。

// 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);
}

如果它有所作为，我的计划将是在自定义 UI 中有两个按钮。一个人将致力于对地点数据进行初步查找。第二个将进行刷新。如果检测到更改并且单元格被更改/更新，那么它将以某种方式突出显示，以便我可以记下这一点。

该项目是我旅行方式的一部分。我会经常制作推荐和审查的名胜古迹、酒吧和餐馆的运行 Google 表格列表，以便我可以将表格导入 Google MyMap 以供我们实际访问时参考。随着时间的推移，这些表格/MyMaps 往往会随着变化（尤其是 COVID）而过时。我希望这有助于它们在未来得到验证，并使它们更容易更新。

标签： google-apps-scriptgoogle-sheetsgoogle-places-api

Answer 1

脚本中的onOpen触发器仅用于在工作表中添加自定义菜单。只有当用户在菜单中选择了与该功能关联的项目时，该功能才会执行。在您的示例中，单击Get place info将执行该COMBINED2功能。

此外，仅当工作表中不存在地点信息时才执行脚本是不可能的，您必须运行脚本以获取地点的标识符并将其与工作表中的数据进行比较。在您的示例中， place.url 可以用作标识符。您唯一能做的就是阻止脚本填充工作表。

在这里，我通过更改与 to 关联的函数来更新您的Get place info脚本writeToSheet()。writeToSheet()将调用COMBINED2(text)以获取地点信息并使用TextFinder来检查工作表中是否存在地点 url。如果 TextFinder 的结果为 0，它将填充工作表。

// 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();
}

输出：

示例 1：

点击自定义菜单后：

示例 2：

点击自定义菜单后：

注意：由于我们使用 lastRow，新条目必须插入到 A 列最后一行的下方。否则它将覆盖最后一个条目。

参考：

• 自定义菜单
• 地点详情