是否可以将Google照片元数据加载到Google表格中? [英] Is it possible to load google photos metadata into google sheets?
问题描述
我有一个项目，其中我扫描了1900年之前的10,000张全家福照片，并且正在Google相册中对其进行整理.我有一个电子表格，其中跟踪着整个收藏的正确日期和标题.我会一次组织一些，但是最近才发现有关Google photos API的信息.
I have a project where I have scanned 10,000 family pictures from as far back as the 1900's and I am organizing them in Google Photos. I have a spreadsheet where I was keeping track of the proper dates and captions for the entire collection. I would organize a few at a time but then recently found out about the google photos API.
我想使用类似方法方法:mediaItems.search ，将照片中的数据保存到电子表格中进行管理.
I would like to use something like the methods Method: mediaItems.list or Method: mediaItems.search to get the data from my photos into the spreadsheet to manage.
这些示例的输出正是我想要的，并且希望将其加载到电子表格中.
The output from these examples is exactly what I'm looking for and would want to load that into a spreadsheet.
如果还有一种方法可以再次从工作表进行更新，那就太棒了.
It would be super awesome if there was a way to update back from the sheet again as well.
我找到了本文，但提供的代码对我不起作用.
I found this article but the code provided does not work for me.
我现在在工作表中有此功能
I have this function now in my sheet
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);
}
在调试模式下运行它时，出现以下错误
When I run it in debug mode, I get the following error
({错误:{代码:403，消息:请求的身份验证范围不足."，状态:"PERMISSION_DENIED"}})
({error:{code:403, message:"Request had insufficient authentication scopes.", status:"PERMISSION_DENIED"}})
我知道这意味着我需要进行身份验证，但不知道如何实现.
I know this means I need to authenticate but don't know how to make that happen.
我有一个API密钥和Google photos API页面中的一个秘密.
I have an API key and a secret from the Google photos API pages.
修改 我使用了@Tanaike的链接来弄清楚如何向我的项目添加范围. 我添加了这三个.
Edit I used the links from @Tanaike to figure out how to add scopes to my project. I added these three.
- spreadsheets.currentonly
- 照片库
- script.external_request
现在，当我在调试模式下运行时，出现403错误，指示我需要设置我的API.错误摘要如下:
Now when I run in debug mode, I get a 403 error indicating I need to set up my API. Summary of the error is below:
错误: 编号:403 之前在项目130931490217中未使用过照片库API，或者已将其禁用.通过访问启用它 https://console.developers.google. com/apis/api/photoslibrary.googleapis.com/overview?project = 130931490217 Google开发人员控制台API激活 type.googleapis.com/google.rpc.Help "PERMISSION_DENIED"
error: code:403 Photos Library API has not been used in project 130931490217 before or it is disabled. Enable it by visiting https://console.developers.google.com/apis/api/photoslibrary.googleapis.com/overview?project=130931490217 Google developers console API activation type.googleapis.com/google.rpc.Help "PERMISSION_DENIED"
但是，当我尝试转到列出的URL时，我仅收到一条消息，提示加载失败".
When I try to go to the listed URL though, I just get a message that says "Failed to load."
推荐答案
在上面的评论中，我的代码在@Tanaike的帮助下工作.我有两个问题.
I got my code working with the help of @Tanaike in my comments above. I had two issues.
1)我需要在appsscript.json中指定oauthScopes，该脚本默认情况下在Google脚本中是隐藏的.可以通过转到菜单并选择视图">显示清单文件"来显示它.
1) I needed to specify the oauthScopes in appsscript.json which is hidden by default in google scripts. It can be revealed by going to the menu and selecting View > Show Manifest File.
2)我使用的是默认GCP项目，该项目无权使用photos API，因此无法启用.我需要切换到我先前创建的启用了照片API的标准GCP项目.
2) I was using a default GCP project which did not have authorization to use the photos API and could not be enabled. I needed to switch to a standard GCP project which I had created earlier and had enabled the photos API.
这是我最初发布的功能，在我正常运行后，它带有其他注释:
Here is my original posted function with additional comments after I got it working:
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);
}
这是我以相同样式创建的另一个函数，用于直接提取照片元数据.这就是我最初想要完成的.
And here is another function which I created in the same style to pull photo metadata directly. This is what I was originally trying to accomplish.
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函数的局限性以及我的图库如此之大，我仍在研究第三个函数，以从特定相册中的所有照片中提取照片元数据.一旦完成，我将编辑此答案.
Because of the limitations of the ListPhotos function and the fact that my library is so enormous, I am still working on a third function to pull photo metadata from all the photos in specific albums. I'll edit this answer once I pull that off.
这篇关于是否可以将Google照片元数据加载到Google表格中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
