I'm learning how to query the YouTube API with apps script by following this tutorial.
Using the tutorial, I've built the below code. The issue that I have is that for some of the search queries in my arrSearchItems array, some rows get skipped when outputting to Google Sheets.
function myFunction() {
var ss = SpreadsheetApp.openById(<spreadsheet_ID>);
var activeSheet = ss.getSheetByName("YouTube Results");
let executionTime = Utilities.formatDate(new Date(), "GMT+1", "yyyy/MM/dd HH:mm:ss");
var arrSearchItems = ["attack on titan", "mob psycho 100", "demon slayer", "vinland saga", "goodnight punpun"];
activeSheet.getRange("A2:H").clearContent();
for (let i=0; i < arrSearchItems.length; i++) {
let search = arrSearchItems[i]
Logger.log((i+1) + ') ' + search);
var sr = YouTube.Search.list("snippet,id", { q : search, maxResults : 50, order : "viewCount"});
var srVidsOnly = sr.items.filter( function (res) { return res.id.kind === "youtube#video"} );
var modRes = srVidsOnly.map( function (v) { return [v.id.videoId, v.snippet.title, v.snippet.publishedAt]; } );
var ids = modRes.map( function (res) { return res[0]; } ).join(",");
for (let j=0; j < modRes.length; j++) {
modRes[j].unshift(search.toUpperCase()); // add the search query to the front of the array
}
for (let k=0; k < modRes.length; k++) {
modRes[k][1] = "https://www.youtube.com/watch?v=" + modRes[k][1]; // update the video ID to a video link
}
var stats = YouTube.Videos.list("statistics", {id : ids});
var vidsStats = stats.items.map ( function (res) { return [res.statistics.viewCount, res.statistics.likeCount, executionTime] } );
var rowStart = (i*50) + 2 // row start for the next search query when outputting to GSheets
activeSheet.getRange(rowStart, 1, modRes.length, modRes[0].length).setValues(modRes);
activeSheet.getRange(rowStart, 5, vidsStats.length, vidsStats[0].length).setValues(vidsStats);
Logger.log('row start = ' + ((i*50)+2))
Logger.log(modRes)
Logger.log(vidsStats)
}
//Logger.log(sr.items)
}
For example, in the image rows 198 - 201 have missing values when they should be populated.
I'm not sure why this is happening. Any ideas?




