CODEWITHSUNDEEP
google-apps-script
Vinay Joseph
Vinay Joseph

Reputation: 5655

Appending rows in a Loop - Google Spreadsheet

I have an app script which uses the YouTube API to get videos from YouTube for a channel. YouTube only returns 50 results at a time.

How do I append rows in the spreadsheet each time I get the next set of results

The essence of my question is, how does one update rows on the spreadsheet in a For Loop.

I tried. sheet.appendRows(object []) and that does not work.

Any ideas ?

My Script

function searchbySafety(safety) {

  var first_response = YouTube.Search.list('id,snippet',{
        channelId: 'UCpZG4Vl2tqg5cIfGMocI2Ag' ,
        maxResults: 50,
        safeSearch: safety
  });

  Create_headers();
  for (var i = 0; i < first_response.items.length; i++) {
      var item = first_response.items[i];
      Logger.log('[%s] Title: %s', item.id.videoId, item.snippet.title);
      gen_results(first_response, safety);
      write_range(data);
  }

  var nextPageToken = first_response.nextPageToken;
  while(nextPageToken != null) {
      var videoresponse = YouTube.Search.list('id,snippet', {
        channelId: 'UCpZG4Vl2tqg5cIfGMocI2Ag' ,
        maxResults:50,
        pageToken: nextPageToken,
        safeSearch: safety
      });


      for (var i = 0; i < videoresponse.items.length; i++) {
          var item = videoresponse.items[i];
          Logger.log('[%s] Title: %s', item.id.videoId, item.snippet.title);
          //gen_results(videoresponse, safety);
          //write_rest_data(data);
      }
      nextPageToken = videoresponse.nextPageToken;
  }

}

function gen_results(response, safety){ 
  for (var i = 0; i < response.items.length; i ++)
  {
     var item = response.items[i];

     row = [item.snippet.title, "https://www.youtube.com/watch?v=" + item.id.videoId, safety];
     data[i] = row;
  }
  return data;
}

function write_range(data){
  var range = report_sheet.getRange(2,1,50,3);
  range.setValues(data);
}

function Create_headers(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheets = ss.getSheets();
  var sheet = sheets[3];
  Logger.log(sheet.getName());
  var headers = [
    ["Title","URL","Safety"]
  ];
  var range = sheet.getRange("A1:C1");
  range.setValues(headers);
  report_sheet = sheet;
}

Upvotes: 0

Views: 2832

Answers (1)

Serge insas
Serge insas

Reputation: 46794

Since you are using a separate function to write data array to the sheet this is very simple and straightforward :

Instead of using a hard coded start row value use the result of the getLastRow() method, see documentation here.

If we use getLastRow()+1 the next block of data will be written right below the last existing row.

Btw, I changed also the height and width definitions in your function to make it more "universal" : it takes the dimensions from the array dimensions so that the match is always perfect even if, for some reason, you change the array size.

code :

function write_range(data){
  var lastRow = report_sheet.getLastRow()+1;
  var range = report_sheet.getRange(lastRow,1,data.length,data[0].length);
  range.setValues(data);
}

Upvotes: 1

Related Questions