CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsyoutube-api
jon
jon

Reputation: 3

Google Apps Script, how to send logged data to spreadsheet?

This is probably not very complicated but I have been a lot of trouble figuring it out so any help will be great, thanks!

Code is from the Google Apps Script sample scripts, I am just trying to change it around a little bit so that data goes to an active spreadsheet.

function retrieveMyUploads() {
  var results = YouTube.Channels.list('contentDetails', {mine: true});
  for(var i in results.items) {
    var item = results.items[i];
    // Get the playlist ID, which is nested in contentDetails, as described in the
    // Channel resource: https://developers.google.com/youtube/v3/docs/channels
    var playlistId = item.contentDetails.relatedPlaylists.uploads;

    var nextPageToken = '';

    // This loop retrieves a set of playlist items and checks the nextPageToken in the
    // response to determine whether the list contains additional items. It repeats that 
    // process until it has retrieved all of the items in the list.
    while (nextPageToken != null) {
      var playlistResponse = YouTube.PlaylistItems.list('snippet', {
        playlistId: playlistId,
        maxResults: 25,
        pageToken: nextPageToken
      });

      for (var j = 0; j < playlistResponse.items.length; j++) {
        var playlistItem = playlistResponse.items[j];
        Logger.log('[%s] Title: %s',
                   playlistItem.snippet.resourceId.videoId,
                   playlistItem.snippet.title);

      }
      nextPageToken = playlistResponse.nextPageToken;
    }
 }
}

Upvotes: 0

Views: 59

Answers (1)

Tanaike
Tanaike

Reputation: 201348

I believe your goal as follows.

  • You want to put the values of playlistItem.snippet.resourceId.videoId, playlistItem.snippet.title in your script to the Spreadsheet.

In this case, how about preparing an array and putting the values to the array, and then, putting array to the Spreadsheet? When this is reflected to your script, it becomes as follows.

Modified script:

In this case, please copy and paste the following script to the script editor of Spreadsheet. By this, the values are put to the active sheet.

function retrieveMyUploads() {
  var values = []; // <--- Added

  var results = YouTube.Channels.list('contentDetails', {mine: true});
  for(var i in results.items) {
    var item = results.items[i];
    // Get the playlist ID, which is nested in contentDetails, as described in the
    // Channel resource: https://developers.google.com/youtube/v3/docs/channels
    var playlistId = item.contentDetails.relatedPlaylists.uploads;

    var nextPageToken = '';

    // This loop retrieves a set of playlist items and checks the nextPageToken in the
    // response to determine whether the list contains additional items. It repeats that 
    // process until it has retrieved all of the items in the list.
    while (nextPageToken != null) {
      var playlistResponse = YouTube.PlaylistItems.list('snippet', {
        playlistId: playlistId,
        maxResults: 25,
        pageToken: nextPageToken
      });

      for (var j = 0; j < playlistResponse.items.length; j++) {
        var playlistItem = playlistResponse.items[j];
        values.push([playlistItem.snippet.resourceId.videoId, playlistItem.snippet.title]); // <--- Added
        Logger.log('[%s] Title: %s',
                   playlistItem.snippet.resourceId.videoId,
                   playlistItem.snippet.title);

      }
      nextPageToken = playlistResponse.nextPageToken;
    }
 }

  var sheet = SpreadsheetApp.getActiveSheet(); // <--- Added
  sheet.getRange(1, 1, values.length, values[0].length).setValues(values); // <--- Added
}

Note:

  • In this modified script, it supposes that your current script works fine. Please be careful this.

Reference:

Upvotes: 2

Related Questions