如何在持续更新的Google Sheet中通过自定义UI按钮调用函数仅填充空白数据

你当前的额度浪费核心原因是将COMBINED2作为单元格自定义函数使用，Google Sheets会在表格打开、内容变动时自动重算所有自定义函数，产生大量无效请求。修改后方案完全通过菜单按钮触发执行，仅在你主动操作时发起API请求，同时支持仅填充空数据、全量刷新高亮两个功能，符合你的需求。

完整修改后代码 #

// 搜索坐标锚点，改为你常用的城市坐标即可
const LOC_BASIS_LAT_LON = "37.7644856,-122.4472203";
// 替换为你自己的Google Places API密钥
const API_KEY = 'AxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxQ';

// 底层查询函数，保留原逻辑，新增错误处理
function COMBINED2(text) {
  try {
    const baseUrl = 'https://maps.googleapis.com/maps/api/place/findplacefromtext/json';
    const queryUrl = baseUrl + '?input=' + encodeURIComponent(text) + '&inputtype=textquery&key=' + API_KEY + "&locationbias=point:" + LOC_BASIS_LAT_LON;
    const response = UrlFetchApp.fetch(queryUrl, {muteHttpExceptions: true});
    const json = JSON.parse(response.getContentText());
    if (json.candidates.length === 0) return [['未找到地点', '', '', '', '']];
    const ID = json.candidates[0].place_id;

    const fields = 'name,formatted_address,formatted_phone_number,website,url,types,opening_hours';
    const baseUrl2 = 'https://maps.googleapis.com/maps/api/place/details/json?placeid=';
    const queryUrl2 = baseUrl2 + ID + '&fields=' + fields + '&key='+ API_KEY;

    const response2 = UrlFetchApp.fetch(queryUrl2, {muteHttpExceptions: true});
    const json2 = JSON.parse(response2.getContentText());
    const place = json2.result;
    if (!place) return [['查询详情失败', '', '', '', '']];

    let weekdays = '';
    if (place.opening_hours?.weekday_text) {
      place.opening_hours.weekday_text.forEach((weekdayText) => {
        weekdays += ( weekdayText + '\r\n' );
      });
    }

    return [[
      place.formatted_address || '',
      place.formatted_phone_number || '',
      place.website || '',
      place.url || '',
      weekdays.trim() || ''
    ]];
  } catch (e) {
    return [['查询出错:' + e.message, '', '', '', '']];
  }
}

// 功能1：仅查询未填充信息的地点（B列为空的行），适合首次批量导入使用
function getNewPlaceInfo() {
  const ss = SpreadsheetApp.getActiveSheet();
  // 获取所有行数据，从第2行开始（跳过表头）
  const allRows = ss.getDataRange().getValues();
  for (let i = 1; i < allRows.length; i++) {
    const row = allRows[i];
    const placeName = row[0]; // A列是地点名
    const existingAddress = row[1]; // B列为空说明没查询过
    if (!placeName || existingAddress) continue; // 跳过空行和已有数据的行
    const data = COMBINED2(placeName);
    // 写入B到F列，行号是i+1（因为数组从0开始，表格行从1开始）
    ss.getRange(i+1, 2, 1, 5).setValues(data);
    // 避免请求太快触发API限流
    Utilities.sleep(200);
  }
  SpreadsheetApp.getUi().alert('未填充地点查询完成');
}

// 功能2：刷新全部地点信息，有变动的单元格标黄高亮
function refreshAllPlaceInfo() {
  const ss = SpreadsheetApp.getActiveSheet();
  const allRows = ss.getDataRange().getValues();
  const yellow = '#ffff00';
  for (let i = 1; i < allRows.length; i++) {
    const placeName = allRows[i][0];
    if (!placeName) continue;
    const oldData = allRows[i].slice(1,6); // 原B-F列数据
    const newData = COMBINED2(placeName)[0]; // 新查询到的数据
    // 逐个单元格对比，有变动就标黄
    for (let j = 0; j < 5; j++) {
      const cell = ss.getRange(i+1, j+2);
      if (String(oldData[j]).trim() !== String(newData[j]).trim()) {
        cell.setBackground(yellow);
      } else {
        cell.setBackground(null); // 无变动清除高亮
      }
    }
    // 写入新数据
    ss.getRange(i+1, 2, 1, 5).setValues([newData]);
    Utilities.sleep(200);
  }
  SpreadsheetApp.getUi().alert('全量刷新完成，变动项已高亮');
}

// 自定义菜单，新增两个功能入口
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("地点查询工具")
      .addItem("批量查询未填充地点","getNewPlaceInfo")
      .addItem("刷新全部地点并高亮变动","refreshAllPlaceInfo")
      .addToUi();
}

使用说明 #

1. 把上述代码替换掉你原来的脚本代码，替换API_KEY和LOC_BASIS_LAT_LON为你自己的配置，保存后刷新表格就能看到新的「地点查询工具」菜单
2. 首次使用时A列填完所有地点名称，点击「批量查询未填充地点」，只会处理B列没有内容的行，不会重复请求已有数据，不会浪费API额度
3. 后续需要更新全量数据时，点击「刷新全部地点并高亮变动」，所有信息会重新查询，和原有内容不一致的单元格会自动标黄，方便你核对变动
4. 注意不要在单元格里手动写=COMBINED2()公式，所有查询都通过菜单触发，就能避免自动重算产生的无效请求

内容的提问来源于stack exchange，提问作者Jeff Abrahams