Google表格Apps Script计算Geohash报错：加载失败/执行超时

问题背景 #

我在Google表格中尝试计算经纬度对的Geohash，用Apps Script编写了自定义GEOHASH函数，但调用GEOHASH([纬度单元格],[经度单元格],10)时，频繁出现“Error loading data”或“Exceeded maximum execution time (line 0)”错误。原始代码如下：

var base32 = "0123456789bcdefghjkmnpqrstuvwxyz";

function GEOHASH(lat, lon, len) {
  if (len === undefined) {
    len = 9; 
  }
  
  var geohash = [];
  
  lat = Number(lat);
  lon = Number(lon);

  var minLat = -90, maxLat = 90;
  var minLon = -180, maxLon = 180;

  var mid;
  var bits = 0;
  var evenBit = true;

  while (geohash.length < len) {
    if (evenBit) {
      mid = (minLon + maxLon) / 2;
      if (lon > mid) {
        minLon = mid;
        bits |= 1<<bits;
      } else { 
        maxLon = mid; 
      }
    } else {
      mid = (minLat + maxLat) / 2;
      if (lat > mid) { 
        minLat = mid;
        bits |= 1<<bits;  
      } else {
        maxLat = mid;
      }
    }
    evenBit = !evenBit;
    if (bits == parseInt("1".repeat(geohash.length), 2)) {  
      geohash.push(base32.charAt(bits));
      bits = 0; 
    }
  }
  return geohash.join("");  
}

使用的数据为多行列的纬度、经度数值，需批量生成对应Geohash。

代码错误分析 #

报错的核心原因是死循环，而非单纯执行超时，代码存在两处关键逻辑错误：

1. bit位累加逻辑错误：bits |= 1<<bits完全不符合Geohash的位计算规则。Geohash每轮生成1个bit（交替处理经度、纬度），需按顺序将bit累加到bits的对应位置（从高位到低位，每凑够5个bit转一个base32字符），原代码的移位操作会导致bits数值异常增长，无法触发后续字符生成条件。
2. 字符生成条件错误：if (bits == parseInt("1".repeat(geohash.length), 2))的判断逻辑完全错误，该条件几乎永远无法满足，导致geohash数组长度无法增长，while循环持续执行，最终触发超时错误。

修复后的代码 #

修正位计算和字符生成逻辑，确保每5个bit生成一个base32字符，循环正常退出：

var base32 = "0123456789bcdefghjkmnpqrstuvwxyz";

function GEOHASH(lat, lon, len) {
  if (len === undefined) {
    len = 9; 
  }
  // 输入合法性校验
  if (isNaN(lat) || isNaN(lon) || lat < -90 || lat > 90 || lon < -180 || lon > 180) {
    return "无效经纬度";
  }
  
  var geohash = [];
  lat = Number(lat);
  lon = Number(lon);

  var minLat = -90, maxLat = 90;
  var minLon = -180, maxLon = 180;

  var mid;
  var bits = 0;
  var bitCount = 0; // 记录当前已累计的bit数
  var evenBit = true;

  while (geohash.length < len) {
    if (evenBit) {
      // 处理经度
      mid = (minLon + maxLon) / 2;
      if (lon > mid) {
        minLon = mid;
        bits = (bits << 1) | 1; // 左移一位，当前bit设为1
      } else {
        maxLon = mid;
        bits = (bits << 1) | 0; // 左移一位，当前bit设为0
      }
    } else {
      // 处理纬度
      mid = (minLat + maxLat) / 2;
      if (lat > mid) {
        minLat = mid;
        bits = (bits << 1) | 1;
      } else {
        maxLat = mid;
        bits = (bits << 1) | 0;
      }
    }
    evenBit = !evenBit;
    bitCount++;
    
    // 每累计5个bit，转换为base32字符
    if (bitCount === 5) {
      geohash.push(base32.charAt(bits));
      bits = 0;
      bitCount = 0;
    }
  }
  return geohash.join("");  
}

优化与替代方案 #

如果需要批量处理大量数据，单个单元格调用自定义函数效率较低，可改用批量处理函数，一次性处理整列数据：

function BATCH_GEOHASH(latRange, lonRange, len = 9) {
  const latValues = latRange.flat();
  const lonValues = lonRange.flat();
  return latValues.map((lat, index) => {
    const lon = lonValues[index];
    if (isNaN(lat) || isNaN(lon) || lat < -90 || lat > 90 || lon < -180 || lon > 180) {
      return "无效经纬度";
    }
    // 复用修复后的Geohash计算逻辑
    let geohash = [];
    let minLat = -90, maxLat = 90;
    let minLon = -180, maxLon = 180;
    let mid, bits = 0, bitCount = 0, evenBit = true;
    while (geohash.length < len) {
      if (evenBit) {
        mid = (minLon + maxLon) / 2;
        bits = (bits << 1) | (lon > mid ? 1 : 0);
        lon > mid ? minLon = mid : maxLon = mid;
      } else {
        mid = (minLat + maxLat) / 2;
        bits = (bits << 1) | (lat > mid ? 1 : 0);
        lat > mid ? minLat = mid : maxLat = mid;
      }
      evenBit = !evenBit;
      if (++bitCount === 5) {
        geohash.push(base32.charAt(bits));
        bits = 0;
        bitCount = 0;
      }
    }
    return geohash.join("");
  });
}

使用时在表格中输入=BATCH_GEOHASH(A2:A100, B2:B100, 10)，即可一次性生成A2:A100纬度、B2:B100经度对应的Geohash。

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