CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetspaginationyoutube-apigoogle-apis-explorer
Ernesto Figueroa
Ernesto Figueroa

Reputation: 25

How to get more than 50 records on Google Sheets through apps script?

I am trying to collect YouTube metadata. However, I am unable to paginate. I only get 50 records but I would like to get as many instances as possible.

How do I get more results?

This my code so far.

function YoutubeScraper2() {
  var search = YouTube.Search.list("snippet, id", { 
    q: "pizza", 
    maxResults: 50 
  });
    var last_row = 0;
    var spreadSheet = SpreadsheetApp.getActiveSpreadsheet()
    var activeSheet = spreadSheet.getActiveSheet();

    for (var i = 0; i < search.items.length; i++){
        var results = search.items.map((item) => [item.id.channelId, item.id.videoId, item.snippet.title, item.snippet.publishedAt, item.snippet.thumbnails.default.url]);
        
        // Retrieve channel IDs and video IDs.
        var {channelIds, videoIds} = results.reduce((o, [channelId, videoId]) => {
          o.channelIds.push(channelId);
          o.videoIds.push(videoId);
          return o;
        }, {channelIds: [], videoIds: []});

        // Retrieve YouTube.Videos.list.
        var videoList = YouTube.Videos.list("statistics", {id: videoIds.join(",")});
        var videoStats = videoList.items.map((item) => [item.id, item.statistics.viewCount, item.statistics.likeCount, item.statistics.dislikeCount]).reduce((o, [id, ...v]) => Object.assign(o, {[id]: v}), {});
      

        // Retrieve YouTube.Channels.list
        var channelList = YouTube.Channels.list("statistics", { id: channelIds.join(",") });
        var channelStats = channelList.items.map((item) => [item.id, item.statistics.viewCount, item.statistics.subscriberCount]).reduce((o, [id, ...v]) => Object.assign(o, {[id]: v}), {});

        // Create an array for putting values and put the values to Spreadsheet.
        results = results.map(e => channelStats[e[0]] ? e.concat(channelStats[e[0]]) : e.concat(Array(2).fill("")));
        results = results.map(e => videoStats[e[1]] ? e.concat(videoStats[e[1]]) : e.concat(Array(3).fill("")));
        
        
        last_row = activeSheet.getLastRow() + 1;
        activeSheet.getRange(1, 1, results.length, results[0].length).setValues(results);
        last_row++;
    }
}

Upvotes: 1

Views: 518

Answers (1)

Tanaike
Tanaike

Reputation: 201378

I believe your goal as follows.

  • You want to retrieve more than 50 items by modifying your script.

Modification points:

  • In this case, I think that it is required to retrieve the data using pageToken.
  • And, at YouTube.Videos.list and YouTube.Channels.list, 50 ids can be used for one API call. So it is required to request several times for these methods.

When above points are reflected to your script, it becomes as follows.

Modified script:

function YoutubeScraper2() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet()
  var activeSheet = spreadSheet.getActiveSheet();
  var numberOfData = 200; // When 0 is set, all values are retrieved.

  // Retrieve YouTube.Search.list.
  var data = [];
  var pageToken = "";
  do {
    var search = YouTube.Search.list("snippet", { q: "pizza", maxResults: 50, pageToken: pageToken });
    data = data.concat(search.items);
    pageToken = search.nextPageToken;
    if (numberOfData != 0 && data.length > numberOfData) break;
  } while (pageToken);
  if (numberOfData != 0) data = data.splice(0, numberOfData);

  var results = data.map((item) => [item.snippet.channelId, item.id.videoId, item.snippet.title, item.snippet.publishedAt, item.snippet.thumbnails.default.url]);

  // Retrieve channel IDs and video IDs.
  var { channelIds, videoIds } = results.reduce((o, [channelId, videoId]) => {
    o.channelIds.push(channelId);
    o.videoIds.push(videoId);
    return o;
  }, { channelIds: [], videoIds: [] });

  // Retrieve YouTube.Videos.list.
  var videoList = [];
  while (videoIds.length > 0) {
    var temp = YouTube.Videos.list("statistics", { id: videoIds.splice(0, 50).join(",") });
    videoList = videoList.concat(temp.items);
  }
  var videoStats = videoList.map((item) => [item.id, item.statistics.viewCount, item.statistics.likeCount, item.statistics.dislikeCount]).reduce((o, [id, ...v]) => Object.assign(o, { [id]: v }), {});

  // Retrieve YouTube.Channels.list
  var channelList = [];
  while (channelIds.length > 0) {
    var temp = YouTube.Channels.list("statistics", { id: channelIds.splice(0, 50).join(",") });
    channelList = channelList.concat(temp.items);
  }
  var channelStats = channelList.map((item) => [item.id, item.statistics.viewCount, item.statistics.subscriberCount]).reduce((o, [id, ...v]) => Object.assign(o, { [id]: v }), {});

  // Create an array for putting values and put the values to Spreadsheet.
  results = results.map(e => channelStats[e[0]] ? e.concat(channelStats[e[0]]) : e.concat(Array(2).fill("")));
  results = results.map(e => videoStats[e[1]] ? e.concat(videoStats[e[1]]) : e.concat(Array(3).fill("")));
  var header = ["channelId", "videoId", "title", "publishedAt", "thumbnails", "viewCount", "subscriberCount", "viewCount", "likeCount", "dislikeCount"];
  results.unshift(header);
  activeSheet.getRange(1, 1, results.length, results[0].length).setValues(results);
}
  • In this script, using var numberOfData = 200;, you can retrieve the values of number you want to retrive. When var numberOfData = 0; is used, all values which can retrieve using YouTube.Search.list are retrieved.
  • In my test, when var numberOfData = 0; is used for above script, 707 values were obtained.

References:

Upvotes: 3

Related Questions