json 如何让Apps Script中的数组批处理在TypeError时插入一个空白单元格，而不是停止数据导入？

g9icjywg  于 2023-10-21  发布在  其他
关注(0)|答案(1)|浏览(99)

因此，我一直在使用Google Sheets中的JSON链接的大规模数据集，我希望将其转换为数据，并且我已经成功地使用了这个数组批处理代码：

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;
}

我遇到了一个问题，每当“abbreviation”变量在批处理中的一个条目中找不到时，它就会停止整个代码的工作，我得到以下错误：TypeError: Cannot read properties of undefined (reading 'abbreviation').我希望代码做的是，如果它找到了其中一个空值，它只需要在它的位置插入一个空单元格，然后继续前进（就像它自己处理代码中的其他变量一样，比如jersey和debutYear变量）。随附的是一份测试表，其中包含较小样本量的数据。第一个批处理应该可以正常工作，然后下一个批处理不会导入，并且会看到错误消息。
我试着减少这段代码，以消除许多我不需要的数据片段，这些数据片段会导致问题，我试着改变JSON链接的位置部分的哪一部分，并与我的类似代码进行交叉引用，这些代码一直没有问题，但都无济于事。我也对这个主题进行了大量的搜索，但我还没有找到适用于我正在使用的代码的东西。
编辑：正如在评论中所建议的，我已经尝试做了一个尝试/捕捉系统，让代码工作;然而，我认为我在前面也做错了什么，因为所有这些都是一次性处理所有批次，然后在最后给我同样的错误。以下是我尝试过但未成功的完整代码：

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;
}

任何帮助我可以得到帮助解决这个问题将不胜感激！
测试表：https://docs.google.com/spreadsheets/d/1eHjUYwApc4boUF8CSoWT56CIYfwWDKQZESfB8FUi0W8/edit?usp=sharing

JSON

1条答案

按热度按时间
vxf3dgd4

vxf3dgd41#

虽然我不确定我是否能正确理解你的预期结果，从你的错误消息TypeError: Cannot read properties of undefined (reading 'abbreviation')，下面的修改怎么样？
在此修改中，您的显示第一个脚本被修改。

发件人：

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());

收件人：

var { id, firstName, lastName, fullName, displayName, shortName, weight, height, position, dateOfBirth, hand, jersey, debutYear, birthPlace, experience, active } = JSON.parse(r.getContentText());
var { abbreviation = null } = position || {};
var { displayValue = null } = hand || {};
var { city = null, state = null, country = null } = birthPlace || {};
var { years = null } = experience || {};
  • 通过该修改，当发生像TypeError: Cannot read properties of undefined (reading 'abbreviation')的情况时，使用空值。
赞(0）分享  回复(0）举报  2023-10-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com