Google表格单元格链接导入API遇TypeError：无法读取map属性

问题概述 #

正在基于2万余个API链接构建数据库，自定义IMPORTAPI函数无法满足需求，因此编写Google Apps Script脚本实现批量数据导入以避免频繁追加行，但全量处理时速度慢，且运行时报错：

TypeError: Cannot read properties of undefined (reading 'map')

同时存在字段错误处理逻辑不完善、链接遍历机制错误等问题。

现有代码 #

function dataImport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Import1");
  var countRow = 1;
  var exportSheet = ss.getSheetByName("Data1");
  var url = sheet.getRange("A2:A").getRichTextValue().getLinkUrl();
  var playerCount = sheet.getRange("A2:A").getValues().filter(String).length

  var response = UrlFetchApp.fetch(url);
  var data = response.getContentText();
  var playerResult = JSON.parse(data);

  var id = playerResult.id
  var firstName = playerResult.firstName
  var lastName = playerResult.lastName
  var fullName = playerResult.fullName
  var displayName = playerResult.displayName
  var shortName = playerResult.shortName
  var weight = playerResult.weight;
    if (weight.error){
      return null
    }
  var height = playerResult.height;
    if (height.error){
      return null
    }  
  var position = playerResult.position.abbreviation;
    if (position.error){
      return null
    }      
  var teamUrl = playerResult.team.$ref;
    if (teamUrl.error){
      return null
    }  
  var city = playerResult.birthPlace.city;
    if (city.error){
      return null
    }  
  var state = playerResult.birthPlace.state;
    if (state.error){
      return null
    }  
  var country = playerResult.birthPlace.country;
    if (country.error){
      return null
    }  
  var years = playerResult.experience.years;
    if (years.error){
      return null
    }  
  var displayClass = playerResult.experience.displayValue;
    if (displayClass.error){
      return null
    }  
  var jersey = playerResult.jersey;
    if (jersey.error){
      return null
    }  
  var active = playerResult.active 

  var { items, playerCount } = playerResult;
  items = items.map(e => [e[id,firstName,lastName,fullName,displayName,shortName,weight,height,position,teamUrl,city,state,country,years,displayClass,jersey,active]])
  var reqs = []
  for (var p = 1; p <= playerCount; p++) {
    reqs.push(url + p)
  }
  var responses = UrlFetchApp.fetchAll(reqs);
  var temp = responses.flatMap(r => r.getResponseCode() == 200 ? JSON.parse(r.getContentText()).items.map(e => [e[id,firstName,lastName,fullName,displayName,shortName,weight,height,position,teamUrl,city,state,country,years,displayClass,jersey,active]]) : []);
  var res = [['IDs','First Name','Last Name','Full Name','Display Name','Short Name','Weight','Height','Position','Team URL','City','State','Country','Years','Class','Active'], ...items, ...temp];
  exportSheet.getRange(countRow, 1, res.length).setValues(res);
}

错误分析与修复方案 #

1. 核心报错原因 #

测试API返回的是单个球员的完整数据对象，不存在items数组，代码中var { items, playerCount } = playerResult;会导致items为undefined，调用map时触发报错。

2. 其他关键问题 #

• 链接读取错误：sheet.getRange("A2:A").getRichTextValue().getLinkUrl()仅读取A列第一个单元格的链接，无法获取所有API地址
• 字段处理逻辑错误：
  • 直接访问weight.error会在weight不存在时触发报错，需先判断字段是否存在
  • e[id,firstName,...]是无效的逗号表达式，无法正确提取对象属性
• 请求生成逻辑错误：循环拼接url + p是错误的，应直接使用A列的所有API链接

3. 修复后的代码 #

function dataImport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const importSheet = ss.getSheetByName("Import1");
  const exportSheet = ss.getSheetByName("Data1");
  const startRow = 2;

  // 读取A列所有非空链接
  const range = importSheet.getRange(startRow, 1, importSheet.getLastRow() - startRow + 1);
  const allLinks = range.getRichTextValues().flat().map(cell => cell.getLinkUrl()).filter(url => url);

  if (allLinks.length === 0) {
    SpreadsheetApp.getUi().alert("未找到有效API链接");
    return;
  }

  // 批量请求所有API
  const responses = UrlFetchApp.fetchAll(allLinks);
  const playerData = [];

  // 处理每个API响应
  responses.forEach((response, index) => {
    if (response.getResponseCode() !== 200) {
      console.log(`请求失败：${allLinks[index]}，状态码：${response.getResponseCode()}`);
      return;
    }

    try {
      const result = JSON.parse(response.getContentText());
      // 安全提取字段，处理可能的缺失或错误
      const playerRow = [
        result.id || "",
        result.firstName || "",
        result.lastName || "",
        result.fullName || "",
        result.displayName || "",
        result.shortName || "",
        result.weight?.value || "", // 处理weight可能不存在或有error的情况
        result.height?.value || "",
        result.position?.abbreviation || "",
        result.team?.$ref || "",
        result.birthPlace?.city || "",
        result.birthPlace?.state || "",
        result.birthPlace?.country || "",
        result.experience?.years || "",
        result.experience?.displayValue || "",
        result.jersey || "",
        result.active !== undefined ? result.active : ""
      ];
      playerData.push(playerRow);
    } catch (e) {
      console.log(`解析失败：${allLinks[index]}，错误：${e.message}`);
    }
  });

  // 准备输出数据（含表头）
  const header = ['IDs','First Name','Last Name','Full Name','Display Name','Short Name','Weight','Height','Position','Team URL','City','State','Country','Years','Class','Active'];
  const outputData = [header, ...playerData];

  // 清空输出表旧数据并写入新数据
  exportSheet.clearContents();
  exportSheet.getRange(1, 1, outputData.length, outputData[0].length).setValues(outputData);
  
  SpreadsheetApp.getUi().alert(`数据导入完成，共处理${playerData.length}条有效数据`);
}

4. 关键优化点 #

• 批量读取链接：正确提取A列所有非空API链接
• 安全字段提取：使用可选链操作符?.和空值合并||处理缺失字段，避免报错
• 批量请求：用UrlFetchApp.fetchAll替代循环请求，大幅提升效率
• 异常处理：增加请求失败、JSON解析失败的日志记录，避免脚本崩溃
• 高效写入：清空旧数据后一次性写入所有数据，减少Spreadsheet操作次数

全量数据处理建议 #

针对2万+API的场景，建议：

• 分批处理：将链接分成每500-1000条一批，避免单次fetchAll请求过多导致超时
• 缓存机制：记录已成功请求的API，避免重复请求
• 时间触发器：使用Google Apps Script的时间触发器分时段执行，避开高峰

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