如何让Apps Script批量数组遇TypeError时插入空单元格而非终止导入？

云原生阿岚

2026-7-10

修复Google Sheets批量JSON解析中缺失字段导致的代码终止问题

我在Google Sheets中处理大规模JSON链接数据集，需要将其转换为结构化数据。当前使用的批量数组代码能正常处理大部分数据，但存在一个问题：当某条数据不存在abbreviation字段时，会触发错误TypeError: Cannot read properties of undefined (reading 'abbreviation')，导致整个代码终止执行。我希望代码遇到此类缺失值时，插入空单元格并继续处理后续数据（就像代码自动处理jersey、debutYear等变量的方式一样）。

我尝试过精简代码、更换Position字段的取值、参考同类可行代码，都没解决问题。之后按建议用了Try/Catch机制，但处理完所有批次后还是出现相同错误，没达到预期效果。

原代码

var scriptProperties = PropertiesService.getScriptProperties();

function dataImport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base JSON Import");
  var exportSheet = ss.getSheetByName("Base Data");
  var reqs = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().reduce((ar, [url]) => {
    if (url) {
      ar.push({ url, muteHttpExceptions: true });
    }
    return ar;
  }, []);

  //Storage of current data
  var bucket = [];
  var batchSize = 200;
  var batches = batchArray(reqs, batchSize);
  var startingBatch = scriptProperties.getProperty("batchNumber") == null ? 0 : parseInt(scriptProperties.getProperty("batchNumber"));
  var processedBatches = scriptProperties.getProperty("processedBatches") == null ? 0 : parseInt(scriptProperties.getProperty("processedBatches"));

  console.log(`Total: ${reqs.length}.\n${batches.length} batches.`)

  if (processedBatches >= (batches.length - 1)) {
    console.log('All data has been processed already.');
  } else {
    //Start from the very last batch that stopped that needs to be processed.
    for (let i = startingBatch; i < batches.length; i++) {
      console.log(`Processing batch index #${parseInt(i)}`);
      try {
        var responses = UrlFetchApp.fetchAll(batches[i]);
        bucket.push(responses);
        //Remove previous batch index number
        scriptProperties.deleteProperty("processedBatches");
        //Store latest sucessful batch index number
        scriptProperties.setProperty("processedBatches", parseInt(i));
      }
      //Catch the last batch index number where it stopped due to URL fetch exception
      catch (e) {
        //Remove the old batch number to be replaced with new batch number.
        scriptProperties.deleteProperty("batchNumber");
        //Remember the last batch that encountered and error to be processed again in the next call.
        scriptProperties.setProperty("batchNumber", parseInt(i));
        console.log(`Batch index #${parseInt(i)} stopped`);
        break;
      }
    }

    const initialRes = [].concat.apply([], bucket);

    var temp = initialRes.reduce((ar, r) => {
      if (r.getResponseCode() == 200) {
        var { id, firstName, lastName, fullName, displayName, shortName, weight, height, position: { abbreviation }, dateOfBirth, hand: { displayValue }, jersey, debutYear, birthPlace: { city }, birthPlace: { state, country }, experience: { years }, active } = JSON.parse(r.getContentText());
        ar.push([id, firstName, lastName, fullName, displayName, shortName, weight, height, abbreviation, dateOfBirth, displayValue, jersey, debutYear, city, state, country, years, active]);
      }
      return ar;
    }, []);
    var res = [...temp];

    //Add table headers
    exportSheet.getLastRow() == 0 && exportSheet.appendRow(['IDs', 'First Name', 'Last Name', 'Full Name', 'Display Name', 'Short Name', 'Weight', 'Height', 'Position', 'DOB', 'Hand', 'Jersey', 'Debut Year', 'City', 'State', 'Country', 'Years', 'Active']);

    //Add table data
    var result = () => {
      return temp.length != 0 && exportSheet.getRange(exportSheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
    }
    result() && console.log(`Processed: ${res.length}.`);
  }
}

//Function to chunk the request data based on batch sizes
function batchArray(arr, batchSize) {
  var batches = [];

  for (var i = 0; i < arr.length; i += batchSize) {
    batches.push(arr.slice(i, i + batchSize));
  }

  return batches;
}

尝试修改后的代码

function getBaseJson() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base JSON Import");
  var countRow = 1;
  var page = 1;
  var url = "https://sports.core.api.espn.com/v2/sports/hockey/leagues/nhl/athletes?limit=1000&page=";
  var response = UrlFetchApp.fetch(url + page);
  var data = response.getContentText();
  var result = JSON.parse(data);
  var { items, pageCount } = result;
  items = items.map(e => [e["$ref"]])
  var reqs = []
  for (var p = 2; p <= pageCount; 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["$ref"]]) : []);
  var res = [['JSONs'], ...items, ...temp];
  sheet.getRange(countRow, 1, res.length).setValues(res);
}

var scriptProperties = PropertiesService.getScriptProperties();

function dataImport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Base JSON Import");
  var exportSheet = ss.getSheetByName("Base Data");
  var reqs = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().reduce((ar, [url]) => {
    if (url) {
      ar.push({ url, muteHttpExceptions: true });
    }
    return ar;
  }, []);

  //Storage of current data
  var bucket = [];
  var batchSize = 200;
  var batches = batchArray(reqs, batchSize);
  var startingBatch = scriptProperties.getProperty("batchNumber") == null ? 0 : parseInt(scriptProperties.getProperty("batchNumber"));
  var processedBatches = scriptProperties.getProperty("processedBatches") == null ? 0 : parseInt(scriptProperties.getProperty("processedBatches"));

  console.log(`Total: ${reqs.length}.\n${batches.length} batches.`)

  if (processedBatches >= (batches.length - 1)) {
    console.log('All data has been processed already.');
  } else {
    //Start from the very last batch that stopped that needs to be processed.
    for (let i = startingBatch; i < batches.length; i++) {
      console.log(`Processing batch index #${parseInt(i)}`);
      try {
        var responses = UrlFetchApp.fetchAll(batches[i]);
        bucket.push(responses);
        //Remove previous batch index number
        scriptProperties.deleteProperty("processedBatches");
        //Store latest sucessful batch index number
        scriptProperties.setProperty("processedBatches", parseInt(i));
      }
      //Catch the last batch index number where it stopped due to URL fetch exception
      catch (e) {
        //Remove the old batch number to be replaced with new batch number.
        scriptProperties.deleteProperty("batchNumber");
        //Remember the last batch that encountered and error to be processed again in the next call.
        scriptProperties.setProperty("batchNumber", parseInt(i));
        console.log(`Batch index #${parseInt(i)} stopped`);
        break;
      }
    }

    const initialRes = [].concat.apply([], bucket);

    var temp = initialRes.reduce((ar, r) => {
      if (r.getResponseCode() == 200) {
        var { id, firstName, lastName, fullName, displayName, shortName, weight, height, dateOfBirth, hand: { displayValue }, jersey, debutYear, birthPlace: { city }, birthPlace: { state, country }, experience: { years }, active } = JSON.parse(r.getContentText());
        try {
          var { position: { abbreviation } } = JSON.parse(r.getContentText());
        }
        catch(err) {
          message.innerHTML = "Error: " + err + ".";
        }
        finally {
          var { position: { abbreviation } } = "None"
        }
        ar.push([id, firstName, lastName, fullName, displayName, shortName, weight, height, abbreviation, dateOfBirth, displayValue, jersey, debutYear, city, state, country, years, active]);
      }
      return ar;
    }, []);
    var res = [...temp];

    //Add table headers
    exportSheet.getLastRow() == 0 && exportSheet.appendRow(['IDs', 'First Name', 'Last Name', 'Full Name', 'Display Name', 'Short Name', 'Weight', 'Height', 'Position', 'DOB', 'Hand', 'Jersey', 'Debut Year', 'City', 'State', 'Country', 'Years', 'Active']);

    //Add table data
    var result = () => {
      return temp.length != 0 && exportSheet.getRange(exportSheet.getLastRow() + 1, 1, res.length, res[0].length).setValues(res);
    }
    result() && console.log(`Processed: ${res.length}.`);
  }
}

//Function to chunk the request data based on batch sizes
function batchArray(arr, batchSize) {
  var batches = [];

  for (var i = 0; i < arr.length; i += batchSize) {
    batches.push(arr.slice(i, i + batchSize));
  }

  return batches;
}

解决方案

问题出在嵌套字段的解构逻辑上：直接解构position: { abbreviation }会在position不存在时抛出错误，且你尝试的Try/Catch用法存在无效操作（比如message.innerHTML在Google Apps Script环境中不存在，finally块的赋值逻辑错误）。正确的做法是用可选链操作符结合默认值安全获取嵌套字段，无需Try/Catch即可处理缺失情况。

核心修改部分

替换原代码中解析JSON并生成数组的temp逻辑：

var temp = initialRes.reduce((ar, r) => {
  if (r.getResponseCode() == 200) {
    const data = JSON.parse(r.getContentText());
    // 用可选链+默认值安全获取每个字段，缺失时返回空字符串
    const id = data.id || '';
    const firstName = data.firstName || '';
    const lastName = data.lastName || '';
    const fullName = data.fullName || '';
    const displayName = data.displayName || '';
    const shortName = data.shortName || '';
    const weight = data.weight || '';
    const height = data.height || '';
    // 处理position.abbreviation：position不存在或abbreviation缺失时返回空
    const abbreviation = data.position?.abbreviation || '';
    const dateOfBirth = data.dateOfBirth || '';
    const displayValue = data.hand?.displayValue || '';
    const jersey = data.jersey || '';
    const debutYear = data.debutYear || '';
    const city = data.birthPlace?.city || '';
    const state = data.birthPlace?.state || '';
    const country = data.birthPlace?.country || '';
    const years = data.experience?.years || '';
    const active = data.active || '';

    ar.push([id, firstName, lastName, fullName, displayName, shortName, weight, height, abbreviation, dateOfBirth, displayValue, jersey, debutYear, city, state, country, years, active]);
  }
  return ar;
}, []);

关键修改点

可选链操作符?.：访问嵌套对象属性时，若上层对象不存在（如data.position为undefined），不会抛出错误，直接返回undefined。
默认值|| ''：将undefined转换为空字符串，确保插入表格时显示为空单元格。
移除无效的Try/Catch块和message.innerHTML操作，简化逻辑同时保证稳定性。

替换后，无论哪个嵌套字段缺失，代码都会自动插入空单元格并继续处理后续数据，不会终止执行。

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

如何让Apps Script批量数组遇TypeError时插入空单元格而非终止导入？

原代码 #

尝试修改后的代码 #

解决方案 #

核心修改部分 #

关键修改点 #

原代码

尝试修改后的代码

解决方案

核心修改部分

关键修改点