I'm parsing this JSON with the aim of exporting the list of events row by row to the sheet. This JSON has a structure that I consider not simple, there are arrays within arrays.
I expect something like this
03/07/21 Rugby League NRL Canterbury-Bankstown Manly Ended 0 66
03/07/21 Rugby League NRL Canberra Gold Coast Ended 6 44
03/07/21 Rugby League NRL Newcastle North Queensland Ended 0 38
03/07/21 Rugby Union International Friendlie Maori All Blacks Samoa Ended 38 21
... further events
This has been my approach
function jSON() {
var destSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("jsonTest");
var response = UrlFetchApp.fetch("https://lsc.fn.sportradar.com/common/en/Europe:London/gismo/sport_matches/12/2021-07-03");
var dataRes = JSON.parse(response.getContentText());
var dataSet = dataRes.doc[0].data.sport.realcategories;
var output = []
for (var i = 0; i < dataSet.length; i++) {
data = dataSet[i];
var val = [];
val = [
data.tournaments[0].matches[i]._dt.date
,data.name
,data.tournaments[0].name
,data.tournaments[0].matches[i].teams.home.name
,data.tournaments[0].matches[i].teams.away.name
,data.tournaments[0].matches[i].status.name
,data.tournaments[0].matches[i].result.home
,data.tournaments[0].matches[i].result.away
];
output.push(val);
Logger.log(output);
}
destSheet.getRange(2,1,output.length,output[0].length).setValues(output);
}
I got this output
03/07/21 Rugby League NRL Canterbury-Bankstown Manly Ended 0 66
03/07/21 Rugby Union International Friendlies New Zealand Tonga Ended 102 0
I'd like to try to catch all events and use the correct approach.
Basically you need more loops; one for each nested array to get into it.
There are a few issues; watch your code indenting it makes it hard to read and easy to make mistakes. Also you are using i which is the realcategories counter on matches (matches[i]) meaning you are only looking at match 0 of category 0 and match 1 of category one...
Try this: the other answer is more modern, but this is closer to where you're starting; so hopefully makes sense...?
for (var i = 0; i < dataSet.length; i++) {
data = dataSet[i];
for (var j = 0; j < data.tournaments.length; j++) {
for (var k = 0; k < data.tournaments[j].matches.length; k++) {
output.push([
data.tournaments[j].matches[k]._dt.date
,data.name
,data.tournaments[j].name
,data.tournaments[j].matches[k].teams.home.name
,data.tournaments[j].matches[k].teams.away.name
,data.tournaments[j].matches[k].status.name
,data.tournaments[j].matches[k].result.home
,data.tournaments[j].matches[k].result.away
]);
}
}
}
You need to iterate over categories, tournaments and matches. Maybe something like this:
var response = UrlFetchApp.fetch("https://lsc.fn.sportradar.com/common/en/Europe:London/gismo/sport_matches/12/2021-07-03");
var dataRes = JSON.parse(response.getContentText());
var dataSet = dataRes.doc[0].data.sport.realcategories;
dataSet.reduce((prev, category) => {
return [
...prev,
...category.tournaments
.map((tournament) => {
return tournament.matches.map((match) => {
return [
match._dt.date,
category.name,
tournament.name,
match.teams.home.name,
match.teams.away.name,
match.status.name,
match.result.home,
match.result.away,
];
});
})
.flat(),
];
}, []);