如何循环导入具有多个页面的API?

huangapple go评论110阅读模式
英文:

How to loop an API import with multiple pages

问题

所以我一直在寻找通过AppScript将JSON链接导入到Google表格中,然后在单独的公式中解析这些链接。然而,这个数据库中有多个页面需要导入,所以我想知道是否有一种方法可以循环执行此操作,将这些页面合并到一个表格中。这是我正在使用的导入代码:

function getBaseJson() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName("Import1")

  var tempHeaderArray = new Array
  var headerRow = ['JSONs']
  tempHeaderArray.push(headerRow)
  var countRow = 1
  var cellRange = sheet.getRange(countRow,1,tempHeaderArray.length,1)
  cellRange.setValues(tempHeaderArray)
  Logger.log("DEBUG: Saved header")

  var url = "https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=1000&active=true&page=1"
  var response = UrlFetchApp.fetch(url)
  var data = response.getContentText()
  var result = JSON.parse(data)

  var myItems = result.items
  var tempItemDataArray = new Array
  for (var i=0;i<myItems.length;i++){
    for (var [key, value] of Object.entries(myItems[i])) {
      tempItemDataArray.push([value])
    }
  }
  countRow++
  var cellRange = sheet.getRange(countRow,1,tempItemDataArray.length,1)
  cellRange.setValues(tempItemDataArray)
  Logger.log("DEBUG: saved items")
}

我尝试在myItems部分之前放置一个for循环,但无论我把它放在哪里,它似乎要么停止导入数组,要么不改变导入。我知道如何从JSON中获取页面数量,但在这种情况下,我知道我需要循环遍历28个页面。如果我能得到这方面的帮助,那就太好了!

英文:

So I have been looking to import the JSON links into a Google sheet via AppScript and then parse those links in a separate formula. However, there are multiple pages in this database that I need to import, so I am wondering if there is a way that I can loop this to combine the pages into one sheet. This is the import code I am using:

function getBaseJson() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet()
  var sheet = ss.getSheetByName(&quot;Import1&quot;)

  var tempHeaderArray = new Array
  var headerRow = [&#39;JSONs&#39;]
  tempHeaderArray.push(headerRow)
  var countRow = 1
  var cellRange = sheet.getRange(countRow,1,tempHeaderArray.length,1)
  cellRange.setValues(tempHeaderArray)
  Logger.log(&quot;DEBUG: Saved header&quot;)

  var url = &quot;https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=1000&amp;active=true&amp;page=1&quot;
  var response = UrlFetchApp.fetch(url)
  var data = response.getContentText()
  var result = JSON.parse(data)

  var myItems = result.items
  var tempItemDataArray = new Array
  for (var i=0;i&lt;myItems.length;i++){
    for (var [key, value] of Object.entries(myItems[i])) {
      tempItemDataArray.push([value])
    }
  }
  countRow++
  var cellRange = sheet.getRange(countRow,1,tempItemDataArray.length,1)
  cellRange.setValues(tempItemDataArray)
  Logger.log(&quot;DEBUG: saved items&quot;)
}

I tried putting a for loop in before the MyItems section, but it seemed like no matter where I put it, it would either stop the array from importing or wouldn't change the import. I know how to get it to pull the number of pages from the JSON, but in this case, I know I need it to loop through 28 pages. If I could get help on this, that would be great!

答案1

得分: 1

在你的情况下,以下是修改后的代码:

function getBaseJson() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Import1");
  var countRow = 1;
  var page = 1;
  var url = "https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=1000&active=true&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);
}
  • 在这个修改中,首次请求中获取了pageCount。然后创建了用于在循环中从每个页面检索值的请求。然后,使用fetchAll方法运行所有请求。然后,将所有值放入工作表中。

  • fetchAll方法是使用异步进程运行的。参考(作者:我)因此,在你的情况下使用它时,我认为处理成本可能会稍微降低。

  • 运行此脚本时,将27865个项目放入“Import1”工作表中。

  • 如果出现与并发HTTP请求相关的错误,可以考虑使用UrlFetchApp.fetch而不是UrlFetchApp.fetchAll

参考资料:

英文:

In your situation, how about the following modification?

Modified script:

function getBaseJson() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(&quot;Import1&quot;);
  var countRow = 1;
  var page = 1;
  var url = &quot;https://sports.core.api.espn.com/v2/sports/football/leagues/college-football/athletes?limit=1000&amp;active=true&amp;page=&quot;;
  var response = UrlFetchApp.fetch(url + page);
  var data = response.getContentText();
  var result = JSON.parse(data);
  var { items, pageCount } = result;
  items = items.map(e =&gt; [e[&quot;$ref&quot;]])
  var reqs = []
  for (var p = 2; p &lt;= pageCount; p++) {
    reqs.push(url + p)
  }
  var responses = UrlFetchApp.fetchAll(reqs);
  var temp = responses.flatMap(r =&gt; r.getResponseCode() == 200 ? JSON.parse(r.getContentText()).items.map(e =&gt; [e[&quot;$ref&quot;]]) : []);
  var res = [[&#39;JSONs&#39;], ...items, ...temp];
  sheet.getRange(countRow, 1, res.length).setValues(res);
}
  • In this modification, pageCount is retrieved at 1st request. And, the requests for retrieving the values from each page in a loop are created. And then, all requests are run with the fetchAll method. And, all values are put into the sheet.

  • The method of fetchAll is run with an asynchronous process. Ref (Author: me) So, when this is used in your situation, I thought that the process cost might be able to be reduced a little.

  • When this script is run, 27865 items are put into the "Import1" sheet.

  • If an error related to the concurrent HTTP requests occurs, UrlFetchApp.fetch might be suitable instead of UrlFetchApp.fetchAll.

References:

huangapple
  • 本文由 发表于 2023年8月9日 03:16:16
  • 转载请务必保留本文链接:https://go.coder-hub.com/76862610.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定