google-apps-scriptgoogle-sheetsgoogle-photosgoogle-photos-api

What could be causing my Google Photos API script to only retrieve metadata for some, but not all, specified albums in a Google Sheet?

Unable to retrieve metadata from all the albums listed in google sheet. The script be​low is supposed to retrieve the specified metadata from the list of specified google photos album present in a google sheet, but it is not retrieving metadata for all the specified list of albums but only for few albums from the list.

function fetchPhotosData() {
  var albumSheetName = "albums to get"; // Replace with the name of your sheet
  var albumSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var albumSheet = albumSpreadsheet.getSheetByName(albumSheetName);

  var metadataSheetName = "METADATA"; // Replace with the name of your sheet
  var metadataSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var metadataSheet = metadataSpreadsheet.getSheetByName(metadataSheetName);

  // Define the target range for the metadata sheet headers
  var metadataSheetRange = metadataSheet.getRange(1, 1, 1, 6); // Assuming you want to start from A1

  // Set the headers for the metadata sheet
  var headers = ['FILENAME', 'CREATION TIME', 'DAY', 'ALBUM NAME', 'DESCRIPTION', 'URL'];
  metadataSheetRange.setValues([headers]);

  // Get the album names from the specified range
  var albumNamesRange = albumSheet.getRange("B2:B"); // Replace with the range containing album names
  var albumNamesValues = albumNamesRange.getValues();
  var albumNames = albumNamesValues.map(function(row) {
    return row[0];
  });

  // Retrieve albums based on the names from the Google Photos API
  var albums = getAlbums();

  // Iterate through all albums
  for (var i = 0; i < albums.length; i++) {
    var album = albums[i];
    var albumId = album.id;
    var albumName = album.title;

    // Check if the album name is present in the specified range
    if (albumNames.indexOf(albumName) !== -1) {

      // Make an API request to retrieve the photos data for the album
      var url = "https://photoslibrary.googleapis.com/v1/mediaItems:search";
      var options = {
        method: "post",
        contentType: "application/json",
        headers: {
          Authorization: "Bearer " + ScriptApp.getOAuthToken()
        },
        muteHttpExceptions: true,
        payload: JSON.stringify({
          albumId: albumId
        })
      };
      var response = UrlFetchApp.fetch(url, options);
      var data = JSON.parse(response.getContentText());

      // Extract and write the relevant photo data to the metadata sheet
      if (data && data.mediaItems && data.mediaItems.length > 0) {
        for (var j = 0; j < data.mediaItems.length; j++) {
          var photo = data.mediaItems[j];
          var filename = photo.filename;
          var creationTime = new Date(photo.mediaMetadata.creationTime);
          var creationTimeIST = Utilities.formatDate(creationTime, "IST", "yyyy-MM-dd HH:mm");
          var day = '=TEXT(B2,"dddd")';
          var url = photo.productUrl;
          var description = photo.description;

          // Check if the photo already exists in the metadata sheet
          var existingData = metadataSheet.getDataRange().getValues();
          var existingRowIndex = -1;
          for (var k = 1;k < existingData.length; k++) {
if (existingData[k][0] === filename && existingData[k][3] === albumName) {
existingRowIndex = k;
break;
}
}
// If photo exists, update the description
      if (existingRowIndex !== -1) {
        metadataSheet.getRange(existingRowIndex + 1, 5).setValue(description);
      } else {
        // Photo doesn't exist, append a new row
        var rowData = [filename, creationTimeIST, day, albumName, description, url];
        metadataSheet.appendRow(rowData);
      }
    }
  }
}
}
}

// Helper function to retrieve all albums
function getAlbums() {
var url = "https://photoslibrary.googleapis.com/v1/albums";
var options = {
headers: {
Authorization: "Bearer " + ScriptApp.getOAuthToken()
},
muteHttpExceptions: true,
};
var response = UrlFetchApp.fetch(url, options);
var data = JSON.parse(response.getContentText());
return data.albums || [];
}

// Helper function to get album names from a specific sheet
function getAlbumNames(sheet) {
var albumNames = [];
var data = sheet.getDataRange().getValues();
for (var i = 1; i < data.length; i++) {
var albumName = data[i][0];
albumNames.push(albumName);
}
return albumNames;
}

The list of albums look like this album list in sheets

Tried chat gpt, but no solution. I have more than 500 albums.

Solution

  • Issue:

    I further investigated your existing code and found out that the reason for the limited number of albums is from your API requests.

    Method: albums.list

    This method lists all albums shown to a user in the Albums tab of the Google Photos app. One of the query parameters of this method is pageSize that states:

    Maximum number of albums to return in the response. Fewer albums might be returned than the specified number. The default pageSize is 20, the maximum is 50.

    If the number of albums is greater than 50, the response will include a nextPageToken

    Output only. Token to use to get the next set of albums. Populated if there are more albums to retrieve for this request.

    You can then rerun the request with pageToken query parameter to capture all albums in your photo app.

    A continuation token to get the next page of the results. Adding this to the request returns the rows after the pageToken. The pageToken should be the value returned in the nextPageToken parameter in the response to the listAlbums request.

    Modified Script:

    I have updated the API requests in your code that loops to fetch all the albums by making subsequent requests until there are no more pages left.

    I also implemented this same scenario in your mediaItems.search method for media items greater than 100 in an album.

    function fetchPhotosData() {
  var albumSheetName = "sourceAlbum"; // Replace with the name of your sheet
  var albumSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var albumSheet = albumSpreadsheet.getSheetByName(albumSheetName);

  var metadataSheetName = "metaData"; // Replace with the name of your sheet
  var metadataSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var metadataSheet = metadataSpreadsheet.getSheetByName(metadataSheetName);

  // Define the target range for the metadata sheet headers
  var metadataSheetRange = metadataSheet.getRange(1, 1, 1, 6); // Assuming you want to start from A1

  // Set the headers for the metadata sheet
  var headers = ['FILENAME', 'CREATION TIME', 'DAY', 'ALBUM NAME', 'DESCRIPTION', 'URL'];
  metadataSheetRange.setValues([headers]);

  // Get the album names from the specified range
  var albumNamesRange = albumSheet.getRange("A2:B"); // Replace with the range containing album names
  var albumNamesValues = albumNamesRange.getValues();
  var albumNames = albumNamesValues.map(function (row) {
    return row[0];
  });

  // Retrieve albums based on the names from the Google Photos API
  var albums = getAlbums();

  // Iterate through all albums
  for (var i = 0; i < albums.length; i++) {
    var album = albums[i];
    var albumId = album.id;
    var albumName = album.title;

    // Check if the album name is present in the specified range
    if (albumNames.indexOf(albumName) !== -1) {

      // Make an API request to retrieve the photos data for the album
      var url = "https://photoslibrary.googleapis.com/v1/mediaItems:search";
      var options = {
        method: "post",
        contentType: "application/json",
        headers: {
          Authorization: "Bearer " + ScriptApp.getOAuthToken()
        },
        muteHttpExceptions: true,
        payload: JSON.stringify({
          albumId: albumId
        })
      };
      var photos = [];
      var nextPageToken = "";

      do {
        var params = {
          pageToken: nextPageToken
        };
        var response = UrlFetchApp.fetch(url + "?" + encodeQueryParams(params), options);
        var data = JSON.parse(response.getContentText());

        if (data.mediaItems && data.mediaItems.length > 0) {
          photos = photos.concat(data.mediaItems);
        }

        nextPageToken = data.nextPageToken;
      } while (nextPageToken);
      // Extract and write the relevant photo data to the metadata sheet
      if (photos.length > 0) {
        for (var j = 0; j < photos.length; j++) {
          var photo = photos[j];
          var filename = photo.filename;
          var creationTime = new Date(photo.mediaMetadata.creationTime);
          var creationTimeIST = Utilities.formatDate(creationTime, "IST", "yyyy-MM-dd HH:mm");
          var day = '=TEXT(B2,"dddd")';
          var url = photo.productUrl;
          var description = photo.description;

          // Check if the photo already exists in the metadata sheet
          var existingData = metadataSheet.getDataRange().getValues();
          var existingRowIndex = -1;
          for (var k = 1; k < existingData.length; k++) {
            if (existingData[k][0] === filename && existingData[k][3] === albumName) {
              existingRowIndex = k;
              break;
            }
          }
          // If photo exists, update the description
          if (existingRowIndex !== -1) {
            metadataSheet.getRange(existingRowIndex + 1, 5).setValue(description);
          } else {
            // Photo doesn't exist, append a new row
            var rowData = [filename, creationTimeIST, day, albumName, description, url];
            metadataSheet.appendRow(rowData);
          }
        }
      }
    }
  }
}

// Helper function to retrieve all albums
function getAlbums() {
  var url = "https://photoslibrary.googleapis.com/v1/albums";
  var options = {
    headers: {
      Authorization: "Bearer " + ScriptApp.getOAuthToken()
    },
    muteHttpExceptions: true,
  };
  var albums = [];
  var nextPageToken = "";

  do {
    var params = {
      pageToken: nextPageToken
    };
    var response = UrlFetchApp.fetch(url + "?" + encodeQueryParams(params), options);
    var data = JSON.parse(response.getContentText());

    if (data.albums && data.albums.length > 0) {
      albums = albums.concat(data.albums);
    }

    nextPageToken = data.nextPageToken;
  } while (nextPageToken);

  return albums;
}

function encodeQueryParams(params) {
  var encodedParams = [];

  for (var key in params) {
    encodedParams.push(encodeURIComponent(key) + "=" + encodeURIComponent(params[key]));
  }

  return encodedParams.join("&");
}


// Helper function to get album names from a specific sheet
function getAlbumNames(sheet) {
  var albumNames = [];
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    var albumName = data[i][0];
    albumNames.push(albumName);
  }
  return albumNames;
}

    Reference:

    https://developers.google.com/photos/library/reference/rest/v1/mediaItems/search

    https://developers.google.com/photos/library/reference/rest/v1/albums/list