Google Sheets反向地理编码自定义函数适配数组公式求助

问题背景 #

我编写了一个Google Sheets自定义函数用于反向地理编码，可查询美国的州和郡信息，单独调用时正常工作，但使用ArrayFormula批量调用时失效。

原函数代码：

/**
 * Return the closest, human-readable address type based on the the latitude and longitude values specified.
 *
 * @param   {"locality"}  addressType Address type. Examples of address types include
 *                                    a street address, a country, or a political entity.
 * @param   {"52.379219"} lat         Latitude
 * @param   {"4.900174"}  lng         Longitude
 * @customfunction
 */
function reverseGeocode(addressType, lat, lng) {
Utilities.sleep(1500);

if (typeof addressType != 'string') {
    throw new Error("addressType should be a string.");
}

if (typeof lat != 'number') {
    throw new Error("lat should be a number");
}

if (typeof lng != 'number') {
    throw new Error("lng should be a number");
}

var response = Maps.newGeocoder().reverseGeocode(lat, lng),
    key      = 'KEY';
response.results.some(function (result) {
    result.address_components.some(function (address_component) {
        return address_component.types.some(function (type) {
            if (type == addressType) {
                key = address_component.long_name;
                return true;
            }
        });
    });
});
return key;
}

单独调用公式（正常工作）：
=iferror(reverseGeocode("administrative_area_level_2", A1, B1),"Not Supplied")

批量调用尝试（失效）：
=arrayformula(iferror(reverseGeocode("administrative_area_level_2", A1:A, B1:B),"Not Supplied"))

问题原因 #

原函数仅支持单个数值类型的经纬度输入，当ArrayFormula传入数组（如A1:A）时，typeof lat会返回object而非number，触发错误判断逻辑导致函数直接抛出错误，无法批量处理。

解决方案：修改函数支持数组输入 #

修改后的函数新增数组处理逻辑，同时封装单条经纬度的处理逻辑，避免重复代码，还优化了错误处理（直接返回提示文本而非抛出错误，防止整列失效）：

/**
 * Return the closest, human-readable address type based on latitude and longitude values.
 * Supports both single values and arrays (for use with ARRAYFORMULA).
 *
 * @param   {"locality"}  addressType Address type (e.g., administrative_area_level_2 for US counties)
 * @param   {52.379219 or A1:A} lat   Latitude (single number or range/array)
 * @param   {4.900174 or B1:B} lng   Longitude (single number or range/array)
 * @customfunction
 */
function reverseGeocode(addressType, lat, lng) {
  // 处理数组输入：遍历每一行/单元格
  if (Array.isArray(lat) && Array.isArray(lng)) {
    return lat.map((latRow, rowIndex) => {
      const lngRow = lng[rowIndex];
      // 处理二维数组（多行多列）
      if (Array.isArray(latRow)) {
        return latRow.map((latVal, colIndex) => {
          const lngVal = lngRow[colIndex];
          return processSingleGeocode(addressType, latVal, lngVal);
        });
      } else {
        // 处理一维数组（单行）
        return processSingleGeocode(addressType, latRow, lngRow);
      }
    });
  } else {
    // 单个值输入，按原逻辑处理
    return processSingleGeocode(addressType, lat, lng);
  }
}

// 封装单个经纬度的反向地理编码逻辑
function processSingleGeocode(addressType, lat, lng) {
  // 参数验证
  if (typeof addressType !== 'string') {
    return "Invalid addressType";
  }
  if (typeof lat !== 'number' || typeof lng !== 'number') {
    return "Not Supplied";
  }

  // 控制API调用频率，避免触发配额限制
  Utilities.sleep(1000);

  try {
    const response = Maps.newGeocoder().reverseGeocode(lat, lng);
    let resultName = "Not Supplied";

    // 遍历地址组件查找目标类型
    response.results.some(result => {
      return result.address_components.some(component => {
        return component.types.some(type => {
          if (type === addressType) {
            resultName = component.long_name;
            return true; // 找到目标后终止遍历
          }
        });
      });
    });

    return resultName;
  } catch (error) {
    return "Error: " + error.message;
  }
}

使用方法 #

现在可以直接用ArrayFormula批量调用，无需额外嵌套iferror：
=arrayformula(reverseGeocode("administrative_area_level_2", A1:A, B1:B))

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