Google Sheets地理编码脚本报Exceeded maximum execution time错误求解

核心优化逻辑 #

• 替换单次API请求为批量请求，大幅降低网络交互耗时
• 替换逐行读写单元格为批量读写，减少表格IO开销
• 新增执行时长校验，接近Apps Script 6分钟执行上限时自动保存进度，下次运行自动续跑，避免报错丢失进度

优化后完整脚本 #

function getGeocodingRegion() {
    return PropertiesService.getDocumentProperties().getProperty('GEOCODING_REGION') || 'au';
}

// 存储处理进度
function saveProgress(lastRow, rangeA1Notation) {
  const props = PropertiesService.getDocumentProperties();
  props.setProperties({
    LAST_PROCESSED_ROW: lastRow.toString(),
    PROCESSING_RANGE: rangeA1Notation
  });
}

// 读取处理进度
function getProgress() {
  const props = PropertiesService.getDocumentProperties();
  return {
    lastRow: parseInt(props.getProperty('LAST_PROCESSED_ROW') || '0'),
    processingRange: props.getProperty('PROCESSING_RANGE')
  };
}

// 清除进度
function clearProgress() {
  const props = PropertiesService.getDocumentProperties();
  props.deleteAllProperties();
}

function addressToPosition() {
    const API_KEY = "xxx"; // 替换为自己的API密钥
    const sheet = SpreadsheetApp.getActiveSheet();
    const startTime = Date.now();
    const TIME_LIMIT = 5 * 60 * 1000; // 预留1分钟缓冲，5分钟时停止
    
    // 读取进度
    const progress = getProgress();
    let cells;
    let startRow = 1;
    
    if (progress.processingRange && progress.lastRow > 0) {
      cells = sheet.getRange(progress.processingRange);
      startRow = progress.lastRow + 1;
    } else {
      cells = sheet.getActiveRange();
      saveProgress(0, cells.getA1Notation());
    }

    const addressColumn = 1;
    const latColumn = addressColumn + 1;
    const lngColumn = addressColumn + 2;
    const totalRows = cells.getNumRows();
    
    // 批量读取所有地址
    const allAddresses = cells.getValues();
    const output = cells.getValues(); // 复用原有值结构，避免覆盖已有数据

    const requests = [];
    const rowIndexes = [];

    for (let addressRow = startRow; addressRow <= totalRows; addressRow++) {
      // 检查执行时间
      if (Date.now() - startTime > TIME_LIMIT) {
        saveProgress(addressRow - 1, cells.getA1Notation());
        // 写入已处理的结果
        cells.setValues(output);
        SpreadsheetApp.getUi().alert(`已处理到第${addressRow-1}行，接近执行时间上限，请再次运行菜单继续处理剩余数据`);
        return;
      }
      
      const address = allAddresses[addressRow - 1][addressColumn - 1];
      if (!address || output[addressRow - 1][latColumn - 1] || output[addressRow - 1][lngColumn - 1]) {
        continue; // 跳过空地址和已有坐标的行
      }
      
      const serviceUrl = "https://maps.googleapis.com/maps/api/geocode/json?address=" + encodeURIComponent(address) + "&key=" + API_KEY + "&region=" + getGeocodingRegion();
      requests.push({
        url: serviceUrl,
        muteHttpExceptions: true,
        contentType: "application/json"
      });
      rowIndexes.push(addressRow - 1);
      
      // 每20个请求批量处理一次，避免单次请求过大
      if (requests.length === 20 || addressRow === totalRows) {
        const responses = UrlFetchApp.fetchAll(requests);
        responses.forEach((response, idx) => {
          const rowIdx = rowIndexes[idx];
          if (response.getResponseCode() == 200) {
            const location = JSON.parse(response.getContentText());
            if (location["status"] == "OK") {
              const lat = location["results"][0]["geometry"]["location"]["lat"];
              const lng = location["results"][0]["geometry"]["location"]["lng"];
              output[rowIdx][latColumn - 1] = lat;
              output[rowIdx][lngColumn - 1] = lng;
            }
          }
        });
        // 清空请求队列
        requests.length = 0;
        rowIndexes.length = 0;
      }
    }

    // 全部处理完成写入结果
    cells.setValues(output);
    clearProgress();
    SpreadsheetApp.getUi().alert("所有地址坐标转换完成");
};

function positionToAddress() {
    const sheet = SpreadsheetApp.getActiveSheet();
    const startTime = Date.now();
    const TIME_LIMIT = 5 * 60 * 1000; // 5分钟时间缓冲
    
    // 读取进度
    const progress = getProgress();
    let cells;
    let startRow = 1;
    
    if (progress.processingRange && progress.lastRow > 0) {
      cells = sheet.getRange(progress.processingRange);
      startRow = progress.lastRow + 1;
    } else {
      cells = sheet.getActiveRange();
      if (cells.getNumColumns() != 3) {
        SpreadsheetApp.getUi().alert("必须选择3列：地址、纬度、经度列");
        return;
      }
      saveProgress(0, cells.getA1Notation());
    }

    const addressColumn = 1;
    const latColumn = addressColumn + 1;
    const lngColumn = addressColumn + 2;
    const totalRows = cells.getNumRows();
    const allValues = cells.getValues();
    const output = allValues;
    const geocoder = Maps.newGeocoder().setRegion(getGeocodingRegion());

    for (let addressRow = startRow; addressRow <= totalRows; addressRow++) {
      // 检查执行时间
      if (Date.now() - startTime > TIME_LIMIT) {
        saveProgress(addressRow - 1, cells.getA1Notation());
        cells.setValues(output);
        SpreadsheetApp.getUi().alert(`已处理到第${addressRow-1}行，接近执行时间上限，请再次运行菜单继续处理剩余数据`);
        return;
      }
      
      const lat = allValues[addressRow - 1][latColumn - 1];
      const lng = allValues[addressRow - 1][lngColumn - 1];
      const existingAddress = output[addressRow - 1][addressColumn - 1];
      
      if (!lat || !lng || existingAddress) continue; // 跳过空坐标和已有地址的行
      
      const location = geocoder.reverseGeocode(lat, lng);
      if (location.status == 'OK') {
        const address = location["results"][0]["formatted_address"];
        output[addressRow - 1][addressColumn - 1] = address;
      }
    }

    cells.setValues(output);
    clearProgress();
    SpreadsheetApp.getUi().alert("所有坐标转地址完成");
};


function generateMenu() {
    var entries = [{
        name: "地址转经纬度",
        functionName: "addressToPosition"
    }, {
        name: "经纬度转地址",
        functionName: "positionToAddress"
    }];

    return entries;
}

function updateMenu() {
    SpreadsheetApp.getActiveSpreadsheet().updateMenu('地理编码', generateMenu())
};

function onOpen() {
    SpreadsheetApp.getActiveSpreadsheet().addMenu('地理编码', generateMenu());
};

使用说明 #

• 替换addressToPosition函数内的API_KEY为你自己的Google Maps Geocoding API密钥
• 原有使用逻辑不变，选中需要处理的列后点击对应菜单即可运行
• 单次处理数据量过大时，脚本会自动提示续跑，重新点击对应菜单即可从上次中断位置继续处理

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