Apps script: Missing rows in output

Question

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?

Answer 1

Try changing:

var rowStart = (i*50)+2

To:

var rowStart = activeSheet.getLastRow()+1

Result:

Explanation:

For the rowStart you are multiplying the index by 50 but some results being returned for the items in your array are not exactly 50, therefore causing these blank rows. So changing this to getLastRow() will make it work to whatever the size of your array will be.

MOB PSYCHO 101 = 48 items

VINLAND SAGA = 46 items

Total = 243 items You'll have 7 blank rows if you multiply by 50