英文:
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("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")
}
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("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);
}
-
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论