google-apps-script - 是否可以将谷歌照片元数据加载到谷歌表格中？

Question

我有一个项目，我从 1900 年代扫描了 10,000 张家庭照片，并将它们组织在 Google Photos 中。我有一个电子表格，我在其中跟踪整个集合的正确日期和标题。我会一次组织一些，但最近发现了谷歌照片 API。

我想使用类似Method: mediaItems.list或Method: mediaItems.search的方法将照片中的数据放入电子表格进行管理。

这些示例的输出正是我正在寻找的内容，并且希望将其加载到电子表格中。

如果有办法再次从工作表更新回来，那就太棒了。

我找到了这篇文章，但提供的代码对我不起作用。

我现在在我的工作表中有这个功能

function photoAPI() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var albums_sh = ss.getSheetByName("albums") || ss.insertSheet("albums", ss.getSheets().length); 
  albums_sh.clear();
  var narray = []; 

  var api = "https://photoslibrary.googleapis.com/v1/albums";
  var headers = { "Authorization": "Bearer " +  ScriptApp.getOAuthToken() };
  var options = { "headers": headers, "method" : "GET", "muteHttpExceptions": true };

  var param= "", nexttoken;
  do {
    if (nexttoken)
      param = "?pageToken=" + nexttoken; 
    var response = UrlFetchApp.fetch(api + param, options);
    var json = JSON.parse(response.getContentText());
    json.albums.forEach(function (album) {
      var data = [
        album.title,
        album.mediaItemsCount,
        album.productUrl
      ];
      narray.push(data);
    });
    nexttoken = json.nextPageToken;
  } while (nexttoken);
  albums_sh.getRange(1, 1, narray.length, narray[0].length).setValues(narray);
}

当我在调试模式下运行它时，出现以下错误

({error:{code:403, message:"Request has enough authentication scopes.", status:"PERMISSION_DENIED"}})

我知道这意味着我需要进行身份验证，但不知道如何实现。

我有一个 API 密钥和来自 Google 照片 API 页面的秘密。

编辑 我使用来自@Tanaike 的链接来弄清楚如何将范围添加到我的项目中。我加了这三个。

• 电子表格.currentonly
• 照片库
• script.external_request

现在，当我在调试模式下运行时，我收到一个 403 错误，表明我需要设置我的 API。错误摘要如下：

错误：代码：403 照片库 API 之前未在项目 130931490217 中使用或已禁用。通过访问 https://console.developers.google.com/apis/api/photoslibrary.googleapis.com/overview?project=130931490217 Google 开发者控制台 API 激活 type.googleapis.com/google.rpc.Help "PERMISSION_DENIED"启用它

但是，当我尝试访问列出的 URL 时，我只会收到一条消息，上面写着“加载失败”。

标签： google-apps-scriptgoogle-sheetsgoogle-apis-explorergoogle-photos-api

Answer 1

在上面的评论中，我的代码在@Tanaike 的帮助下工作。我有两个问题。

1）我需要在appsscript.json中指定oauthScopes，它默认隐藏在谷歌脚本中。可以通过转到菜单并选择 View > Show Manifest File 来显示它。

2) 我使用的是默认 GCP 项目，该项目没有使用照片 API 的授权并且无法启用。我需要切换到我之前创建并启用了照片 API 的标准 GCP 项目。

这是我最初发布的功能，在我开始工作后有额外的评论：

function photoAPI_ListAlbums() {
  // Modified from code by Stackoverflow user Frç Ju at https://stackoverflow.com/questions/54063937/0auth2-problem-to-get-my-google-photos-libraries-in-a-google-sheet-of-mine
  // which was originally Modified from http://ctrlq.org/code/20068-blogger-api-with-google-apps-script

  /*
  This function retrieves all albums from your personal google photos account and lists each one with the name of album, count of photos, and URL in a new sheet.

  Requires Oauth scopes. Add the below line to appsscript.json
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/photoslibrary", "https://www.googleapis.com/auth/photoslibrary.readonly", "https://www.googleapis.com/auth/script.external_request"]

  Also requires a standard GCP project with the appropriate Photo APIs enabled.
  https://developers.google.com/apps-script/guides/cloud-platform-projects
  */

  //Get the spreadsheet object
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //Check for presence of target sheet, if it does not exist, create one.
  var albums_sh = ss.getSheetByName("albums") || ss.insertSheet("albums", ss.getSheets().length); 
  //Make sure the target sheet is empty
  albums_sh.clear();
  var narray = []; 

  //Build the request string. Default page size is 20, max 50. set to max for speed.
  var api = "https://photoslibrary.googleapis.com/v1/albums?pageSize=50";
  var headers = { "Authorization": "Bearer " +  ScriptApp.getOAuthToken() };
  var options = { "headers": headers, "method" : "GET", "muteHttpExceptions": true };

  var param= "", nexttoken;

  //Make the first row a title row
  var data = [
    "Title",
    "Item Count",
    "ID",
    "URL"
  ];
  narray.push(data);

  //Loop through JSON results until a nextPageToken is not returned indicating end of data
  do {
    //If there is a nextpagetoken, add it to the end of the request string
    if (nexttoken)
      param = "&pageToken=" + nexttoken; 

    //Get data and load it into a JSON object
    var response = UrlFetchApp.fetch(api + param, options);
    var json = JSON.parse(response.getContentText());

    //Loop through the JSON object adding desired data to the spreadsheet.
    json.albums.forEach(function (album) {
      var data = [
        "'"+album.title,  //The prepended apostrophe makes albums with a name such as "June 2007" to show up as that text rather than parse as a date in the sheet.
        album.mediaItemsCount,
        album.id,
        album.productUrl
      ];
      narray.push(data);
    });

    //Get the nextPageToken
    nexttoken = json.nextPageToken;
    //Continue if the nextPageToaken is not null
  } while (nexttoken);

  //Save all the data to the spreadsheet.
  albums_sh.getRange(1, 1, narray.length, narray[0].length).setValues(narray);
}

这是我以相同风格创建的另一个功能，用于直接提取照片元数据。这就是我最初想要完成的。

function photoAPI_ListPhotos() {
  //Modified from above function photoAPI_ListAlbums

  /*
  This function retrieves all photos from your personal google photos account and lists each one with the Filename, Caption, Create time (formatted for Sheet), Width, Height, and URL in a new sheet.
  it will not include archived photos which can be confusing if you happen to have a large chunk of archived photos some pages may return only a next page token with no media items.

  Requires Oauth scopes. Add the below line to appsscript.json
  "oauthScopes": ["https://www.googleapis.com/auth/spreadsheets.currentonly", "https://www.googleapis.com/auth/photoslibrary", "https://www.googleapis.com/auth/photoslibrary.readonly", "https://www.googleapis.com/auth/script.external_request"]

  Also requires a standard GCP project with the appropriate Photo APIs enabled.
  https://developers.google.com/apps-script/guides/cloud-platform-projects
  */

  //Get the spreadsheet object
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //Check for presence of target sheet, if it does not exist, create one.
  var photos_sh = ss.getSheetByName("photos") || ss.insertSheet("photos", ss.getSheets().length); 
  //Make sure the target sheet is empty
  photos_sh.clear();
  var narray = []; 

  //Build the request string. Max page size is 100. set to max for speed.
  var api = "https://photoslibrary.googleapis.com/v1/mediaItems?pageSize=100";
  var headers = { "Authorization": "Bearer " +  ScriptApp.getOAuthToken() };
  var options = { "headers": headers, "method" : "GET", "muteHttpExceptions": true };

  //This variable is used if you want to resume the scrape at some page other than the start. This is needed if you have more than 40,000 photos.
  //Uncomment the line below and add the next page token for where you want to start in the quotes.
  //var nexttoken="";

  var param= "", nexttoken;
  //Start counting how many pages have been processed.
  var pagecount=0;

  //Make the first row a title row
  var data = [
    "Filename",
    "description",
    "Create Time",
    "Width",
    "Height",
    "ID",
    "URL",
    "NextPage"
  ];
  narray.push(data);

  //Loop through JSON results until a nextPageToken is not returned indicating end of data
  do {
    //If there is a nextpagetoken, add it to the end of the request string
    if (nexttoken)
      param = "&pageToken=" + nexttoken; 

    //Get data and load it into a JSON object
    var response = UrlFetchApp.fetch(api + param, options);
    var json = JSON.parse(response.getContentText());

    //Check if there are mediaItems to process.
    if (typeof json.mediaItems === 'undefined') {
      //If there are no mediaItems, Add a blank line in the sheet with the returned nextpagetoken

      //var data = ["","","","","","","",json.nextPageToken];
      //narray.push(data);
    } else {
      //Loop through the JSON object adding desired data to the spreadsheet.
      json.mediaItems.forEach(function (MediaItem) {

        //Check if the mediaitem has a description (caption) and make that cell blank if it is not present.
        if(typeof MediaItem.description === 'undefined') {
            var description = "";
          } else {
            var description = MediaItem.description;
          }

        //Format the create date as appropriate for spreadsheets.
        var d = new Date(MediaItem.mediaMetadata.creationTime);

        var data = [
          MediaItem.filename,
          "'"+description, //The prepended apostrophe makes captions that are dates or numbers save in the sheet as a string. 
          d,
          MediaItem.mediaMetadata.width,
          MediaItem.mediaMetadata.height,
          MediaItem.id,
          MediaItem.productUrl,
          json.nextPageToken
        ];
        narray.push(data);
      });
    }

    //Get the nextPageToken
    nexttoken = json.nextPageToken;    

    pagecount++;
    //Continue if the nextPageToaken is not null
    //Also stop if you reach 400 pages processed, this prevents the script from timing out. You will need to resume manually using the nexttoken variable above.
  } while (pagecount<400 && nexttoken);

    //Continue if the nextPageToaken is not null (This is commented out as an alternative and can be used if you have a small enough collection it will not time out.)
  //} while (nexttoken);

  //Save all the data to the spreadsheet.
  photos_sh.getRange(1, 1, narray.length, narray[0].length).setValues(narray);
}

由于 ListPhotos 函数的限制以及我的图书馆如此庞大的事实，我仍在研究第三个函数，以从特定相册中的所有照片中提取照片元数据。完成后，我将编辑此答案。