您好，我有一列包含 Google Drive 檔案夾鏈接串列，我想獲取檔案夾中包含的所有檔案的最新更新日期。 例子
通過環顧四周，我能夠通過向我提供檔案夾中包含的所有檔案的串列及其最新更新來構建以下運行良好的腳本。
function onOpen() {
var spreadsheet = SpreadsheetApp.getActive();
var menuItems = [
{name: 'UpdateCheck', functionName: 'files'},
];
spreadsheet.addMenu('Menu', menuItems);
}
function files() {
var ui = SpreadsheetApp.getUi();
var result = ui.prompt("Paste the ID of Google Drive Folder");
var folderId = result.getResponseText();
var folder = DriveApp.getFolderById(folderId);
var files = folder.getFiles();
var output = [];
while (files.hasNext()) {
var file = files.next();
output.push([file.getName(), file.getLastUpdated()]);
}
output.sort(function(a, b) {
return a[0] == b[0] ? 0 : a[0] < b[0] ? -1 : 1;
});
SpreadsheetApp.getActiveSpreadsheet().insertSheet().getRange(2, 1, output.length, output[0].length).setValues(output);
但是，我必須使用 UI 打開每個鏈接，從輸入欄位中的 URL 復制并粘貼 ID。我只想讀取列范圍，從不重復的字串中獲取 ID，然后運行我的腳本并將結果放在一個新選項卡中。任何幫助都會很棒。
uj5u.com熱心網友回復：
我相信你現在的情況和你的目標如下。
您有一張表，檔案夾鏈接被放置在您提供的影像中的“D”列中。
您希望從鏈接中檢索檔案夾 ID，并希望檢索
getLastUpdated()檔案夾中所有檔案的檔案名和值。您想將檢索到的資料放在新作業表上。
修改后的腳本：
請設定作業表名稱srcSheetName和dstSheetName。
function myFunction() {
var srcSheetName = "Sheet1"; // Please set the source sheet name.
var dstSheetName = "Sheet2"; // Please set the destination sheet name.
// Retrieve folder links from "D2:D" and check the duplication.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(srcSheetName);
var folderIds = [...new Set(sheet.getRange("D2:D" sheet.getLastRow()).getRichTextValues().flatMap(([d]) => {
var url = d.getLinkUrl();
if (url && /^https:\/\/drive\.google\.com\/drive\/folders\/. /.test(url)) {
return [url.split("/")[5].trim()];
}
return [];
}))];
// Create an array for putting to sheet.
var res = folderIds.flatMap(id => {
var folder = DriveApp.getFolderById(id);
var files = folder.getFiles();
var temp = [];
while (files.hasNext()) {
var file = files.next();
temp.push([file.getName(), file.getLastUpdated()]);
}
return temp;
});
// Put the array to the destination sheet.
ss.insertSheet(dstSheetName).getRange(1, 1, res.length, 2).setValues(res);
}
關于一些不清楚的地方，我猜測如下。
我無法理解單元格中檔案夾鏈接的格式。在這個修改中，我猜檔案夾鏈接就像
https://drive.google.com/drive/folders/###.我無法理解
get the IDs from the strings without duplicates。在此修改中，檢查了檢索到的檔案夾 ID 的重復。
參考：
- 獲取值（）
- 插入表（作業表名稱）
- 設定值（值）
添加：
根據您的回復，
快速提問，當我運行它時，我在獲取物件 DriveApp 上的方法或屬性 getFolderById 時遇到意外錯誤，
var res = folderIds.flatMap(id => { var folder = DriveApp.getFolderById(id);這是可以解決的，還是應該打開另一個執行緒？
the folder link was indeed incorrect instead of https://drive.google.com/drive/folders/### the format was https://drive.google.com/corp/drive/u/0/folders/####?resourcekey=#### so I correct the code with /^https://drive.google.com/corp/drive/u/0/folders/. /.test(url)) { return [url.split("/")[8].trim()]; but now I get 1CHKEThZJ0P-ISv0g0MoGjCCqdZuuJJyA?resourcekey=0-1gzYTJvJv1XDwF2Hx4HvEQ and I need to find a way to get rid of everything right before the "?"
For this, I modified my proposed script as follows.
Modified script:
function myFunction() {
var srcSheetName = "Sheet1"; // Please set the source sheet name.
var dstSheetName = "Sheet2"; // Please set the destination sheet name.
// Retrieve folder links from "D2:D" and check the duplication.
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(srcSheetName);
var folderIds = [...new Set(sheet.getRange("D2:D" sheet.getLastRow()).getRichTextValues().flatMap(([d]) => {
var url = d.getLinkUrl();
if (url) {
if (/^https:\/\/drive\.google\.com\/drive\/folders\/. /.test(url)) {
return [url.split("/")[5].trim()];
} else if (/^https:\/\/drive\.google\.com\/. \/drive\/u\/0\/folders\/. /.test(url)) {
return [url.split("/")[8].trim().split("?")[0]];
}
}
return [];
}))];
// Create an array for putting to sheet.
var res = folderIds.flatMap(id => {
try {
var folder = DriveApp.getFolderById(id);
var files = folder.getFiles();
var temp = [];
while (files.hasNext()) {
var file = files.next();
temp.push([file.getName(), file.getLastUpdated()]);
}
return temp;
} catch (e) {
console.log({id, msg: e.message});
}
});
// Put the array to the destination sheet.
ss.insertSheet(dstSheetName).getRange(1, 1, res.length, 2).setValues(res);
}
- By this modification, 2 kinds of URLs like
https://drive.google.com/drive/folders/###andhttps://drive.google.com/corp/drive/u/0/folders/####?resourcekey=####can be used, and when an error occurs when the files are retrieved from the folder, you can see the folder ID at the log. And, the script is not stopped.
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/404769.html
標籤：