0. 前言
由於我個人在雲端資料夾中，與朋友共享繪畫用的參考圖庫，積年累月下來，圖片多了我們也不容易分類。突發奇想下，是否能夠利用 Google Spreadsheet 紀錄新的檔案並區分出風格與作者名稱，過後利用篩選器更快找到想要的資訊呢？
那我們就先從最基本的單資料夾進行偵測，紀錄到 Google Spreadsheet 開始搭建吧。
1. 重點
2. 內容
2.1. 判斷資料夾在雲端硬碟的位置
如果用資料夾名稱搜尋，我們必須經過多筆迴圈，還有可能遭遇同名的議題。因此，我決定用 Google Spreadsheet 的位置作為判斷依據，偵測試算表所在的資料夾內部活動。
由於後續會利用 Folder ID 限制搜尋範圍，因此共用的資料夾理論上也是能執行的。我們接著建立個 Spreadsheet 用來儲存資料與執行程式吧。
當我們手動執行該方法後，應該會打印出母資料夾的網址，我們嘗試連線進入確認是否正確。那由於 My Drive 本身在 Google 的架構中也屬於 Folder，因此也會回傳 URL，只是作為頂層其 Folder ID 也等同於 Drive ID，主要用於 Shared Drive 的跨磁碟分享。
JavaScript
// Find parent folder by where the spreadsheet is stored
function getParentFolder()
{
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the ID of the spreadsheet
var spreadsheetId = spreadsheet.getId();
// Get the parent folders of the spreadsheet
var parentFolders = DriveApp.getFileById(spreadsheetId).getParents();
// Check if there are any parent folders
if (parentFolders.hasNext()) {
// Assuming we only have a parent folder
var parentFolder = parentFolders.next();
// For testing purpose
console.log(`Parent Folder: ${parentFolder.getUrl()}`);
// Return the folder object
return parentFolder;
}
// If there are no parent folders, return null
else {
console.error("[ERROR] Critical Error on Getting Parent URL");
return null;
}
}
我們還是聚焦在個人帳戶的基礎上，不討論要 Workspace 帳戶才能建立的 Shared Drive。總之進入連結後應該能看到我們剛所建立的檔案。
2.2. 建立檢查資料夾內檔案的方法
取得了資料夾位置後，我們便要搜尋其內部的變動。那為了先確認可行性，這邊我只先掃描該層資料夾，而沒有迴圈內部的子資料夾。
此時我們先新增一個 function 專門用來初始化設定。貼上以後，請修改 sheetname 的常數為你的分頁名稱，之後再執行該方法。
JavaScript
// Init setting in project propeties
function init() {
// Sheet Name
const sheetFilesDetection_sheetName = "總表";
try {
PropertiesService.getScriptProperties().setProperties({
'sheetFilesDetection_sheetName': sheetFilesDetection_sheetName,
// Init lastSearchFilesCreatedTime
'lastSearchFilesCreatedTime': new Date().toISOString()
});
console.info("[ O K ] Successfully initalize the app.");
} catch (error) { console.error(`[ERROR] Failed to initialize the app: ${error}`); }
}當我們執行之後，進到 Project Settings >> Properties 區塊，除了紀錄分頁名稱之外，還有最重要用來判斷時間的基準，之後會以上次偵測所紀錄的最近期建檔檔案時間做判斷，在他之後的都算新檔案。後面我們統一會用 ISO 8601 的日期格式。
接著我們就要開始測試執行檔案了，避免用迴圈浪費大把時間，就要使用官方提供的搜尋方法。但由於 DriveApp 本身的搜尋方法無法檢測到 createdDate 參數，我們就透過 HTTP Request 呼叫 Drive API v3 進行查詢。請注意變數 folderId 目前只是暫時測試用，之後會進行更動以適用其他子資料夾。
JavaScript
// Search Files Newly Created
//
// - Comparing Files CreatedDate and Latestest File Created Time on last execution to see if is new
// -- T
// ---- Write important details into spreadsheet
// -- F
// ----
function searchFilesCreated() {
// Getting the spreadsheet for data writing
const sheetFilesDetection = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(PropertiesService.getScriptProperties().getProperty('sheetFilesDetection_sheetName'));
// Latest file created time on last execution
const lastSearchFilesCreatedTime = PropertiesService.getScriptProperties().getProperty('lastSearchFilesCreatedTime');
// TMP
let folderId = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next().getId();
// Setting up search condition
const query = `createdTime > "${lastSearchFilesCreatedTime}" and trashed=false and mimeType != "application/vnd.google-apps.folder" and "${folderId}" in parents`;
// List for search result
let fileList = [];
// Checkpoint
let pageToken = "";
do {
try {
const url = encodeURI(
`https://www.googleapis.com/drive/v3/files?q=${query}&pageSize=1000&pageToken=${pageToken}`
); // Include driveId and includeItemsFromAllDrives parameters
const res = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
});
const obj = JSON.parse(res.getContentText());
if (obj.files && obj.files.length > 0) {
fileList = [...fileList, ...obj.files];
}
pageToken = obj.nextPageToken;
} catch (error) {
console.error(`[ERROR] Error fetching files: ${error}`);
break;
}
} while (pageToken);
// Debugging
console.log(fileList);
// fileList.forEach(json => {
// // Use ID to get Object of File Class
// file = DriveApp.getFileById(json.id);
// // Write to spreadsheet
// writeToSpreadsheet(sheetFilesDetection, file);
// });
// There's new file
if (fileList.length > 0) {
// Store time of lastest file created time
PropertiesService.getScriptProperties().setProperty('lastSearchFilesCreatedTime', DriveApp.getFileById((fileList[fileList.length - 1].id)).getDateCreated().toISOString());
}
}
我們首先隨便丟入幾張圖檔。
接著執行 searchFilesCreated 方法，應該會找到這幾個檔案。
檢查 Properties，可以看到雖然我在 19 分執行，但實際時間紀錄卻是在 2 分，這是避免迴圈過程太久導致的紀錄時差，當這空隙越大代表越多檔案可能被遺漏，尤其在同時執行的過程中。
而我們偏不信邪，再次執行方法一次，此時應該就不會掃到任何檔案，並不改動時間標記，避免執行間的微秒差距造成缺漏的問題。
2.2.1. Drive API Query 解說
我們回頭講解下 query 中的參數，首先是檢查建檔時間晚於上次執行所紀錄的檔案時間，要求不在垃圾桶內（軟刪除），然後不屬於資料夾型態。最後依靠 Folder ID 限制偵測範圍，不然會偵測整個雲端磁碟。
而由於我們之後會有多個子資料夾的結果要進行合併處理，我們就先不用 orderBy 參數要求排序檔案。
而在呼叫的 URL 後方，pageSize 代表每次回傳的筆數，可設於 1 – 1000 之間。而 pageToken 的用途是接續點，假設今天檔案有 1500 筆，第一輪搜尋完會回傳 nextToken 的參數作為紀錄點，而第二輪就會從這邊開始。若沒有設定的話，就會因為有 nextToken 而迴圈卡死在搜尋前 pageSize 個檔案，非常重要。
範例 1: pageSize 為 1 但無 pageToken 參數
由於我們的程式碼會累加進 fileList，但各位應該有發現到陣列變成了但資料都同一筆，此時的 nextToken 一直指向下個符合的檔案，從而陷入無限迴圈。
範例 2: pageSize 為 1 而有 pageToken 參數
此時我補回 pageToken 參數，有了紀錄點，程式就能夠依靠上次的紀錄點持續執行。可以看到這次讀取到剛加入的幾分檔案，而由於沒其他符合搜尋條件的結果，nextToken 變為空值，同時脫出迴圈。
2.3. 將檔案資訊寫回 Google Spreadsheet
在前面我們成功的找到新的檔案了，但我們還沒有將他們寫入總表。我先超前設計好其它欄位，不過目前測試時所存的只有後面五項。請再依照個人需求設計欄位，並自行修改以下方法。
JavaScript
// Write attributes back to spreadsheet
//
// @ File name
// @ File URL
// @ Folder URL
// @ File Created Date
// @ File Modified Date
function writeToSpreadsheet(sheet, file) {
// Get attributes
// -- Filename
const fileName = file.getName();
// -- File URL
const fileUrl = file.getUrl();
// -- Folder URL
const folderURL = file.getParents().next().getUrl();
// -- File Created Date
const formattedDateCreated = Utilities.formatDate(file.getDateCreated(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
// -- File Modified Date
const formattedDateModified = Utilities.formatDate(file.getLastUpdated(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
// Get the header row
const columnHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// Index
const indexDateCreated = columnHeaders.indexOf('建檔時間');
const indexDateModified = columnHeaders.indexOf('更新時間');
const indexFolderURL = columnHeaders.indexOf('母資料夾');
const indexFileName = columnHeaders.indexOf('作品名稱');
const indexFileURL = columnHeaders.indexOf('檔案位置');
// Prepare the data array for the new row
let row = new Array(columnHeaders.length).fill('');
row[indexDateCreated] = formattedDateCreated;
row[indexDateModified] = formattedDateModified;
row[indexFolderURL] = folderURL;
row[indexFileName] = fileName;
row[indexFileURL] = fileUrl;
// Write to last row in sheet
sheet.appendRow(row);
}完成之後，我們要再 searchFilesCreated() 啟用，請幫剛剛最底下被註解的地方啟用。
JavaScript
fileList.forEach(json => {
// Use ID to get Object of File Class
file = DriveApp.getFileById(json.id);
// Write to spreadsheet
writeToSpreadsheet(sheetFilesDetection, file);
});完成之後，我們再放些新的檔案到資料夾，再度執行 searchFilesCreated 後，應該能看到成功寫回 Google Spreadsheet 之中，並能快速的連到資料夾與預覽圖片。這樣就完成基本款了。
3. 後話
但為了區分畫風與標註作家，我們將預期有許多的子資料夾，那下次的目標就是對底下的所有物件進行掃描，並嘗試透過資料夾名稱進行分類。
4. 參考
[1] Property Service
https://developers.google.com/apps-script/guides/properties
[2] Issue and Workaround on v2 Query of createdDate
https://stackoverflow.com/a/74118817
[3] Drive API — Official Doc
https://developers.google.com/drive/api/reference/rest/v3/files/list
5. 素材
[1] QuAn_ — pixiv
https://www.pixiv.net/users/6657532
[2] HzW3 — pixiv
https://www.pixiv.net/users/13804723
6. 成品
JavaScript
// Init setting in project propeties
function init() {
// Sheet Name
const sheetFilesDetection_sheetName = "總表";
try {
PropertiesService.getScriptProperties().setProperties({
'sheetFilesDetection_sheetName': sheetFilesDetection_sheetName,
// Init lastSearchFilesCreatedTime
'lastSearchFilesCreatedTime': new Date().toISOString()
});
console.info("[ O K ] Successfully initalize the app.");
} catch (error) { console.error(`[ERROR] Failed to initialize the app: ${error}`); }
}
// Search Files Newly Created
//
// - Comparing Files CreatedDate and Latestest File Created Time on last execution to see if is new
// -- T
// ---- Write important details into spreadsheet
// -- F
// ----
function searchFilesCreated() {
// Getting the spreadsheet for data writing
const sheetFilesDetection = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(PropertiesService.getScriptProperties().getProperty('sheetFilesDetection_sheetName'));
// Latest file created time on last execution
const lastSearchFilesCreatedTime = PropertiesService.getScriptProperties().getProperty('lastSearchFilesCreatedTime');
// TMP
let folderId = DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next().getId();
// Setting up search condition
const query = `createdTime > "${lastSearchFilesCreatedTime}" and trashed=false and mimeType != "application/vnd.google-apps.folder" and "${folderId}" in parents`;
// List for search result
let fileList = [];
// Checkpoint
let pageToken = "";
do {
try {
const url = encodeURI(
`https://www.googleapis.com/drive/v3/files?q=${query}&pageSize=1000&pageToken=${pageToken}`
); // Include driveId and includeItemsFromAllDrives parameters
const res = UrlFetchApp.fetch(url, {
headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() },
});
const obj = JSON.parse(res.getContentText());
if (obj.files && obj.files.length > 0) {
fileList = [...fileList, ...obj.files];
}
pageToken = obj.nextPageToken;
} catch (error) {
console.error(`[ERROR] Error fetching files: ${error}`);
break;
}
} while (pageToken);
// Debugging
console.log(fileList);
fileList.forEach(json => {
// Use ID to get Object of File Class
file = DriveApp.getFileById(json.id);
// Write to spreadsheet
writeToSpreadsheet(sheetFilesDetection, file);
});
// There's new file
if (fileList.length > 0) {
// Store time of lastest file created time
PropertiesService.getScriptProperties().setProperty('lastSearchFilesCreatedTime', DriveApp.getFileById((fileList[fileList.length - 1].id)).getDateCreated().toISOString());
}
}
// Find parent folder by where the spreadsheet is stored
function getParentFolder() {
// Get the active spreadsheet
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
// Get the ID of the spreadsheet
var spreadsheetId = spreadsheet.getId();
// Get the parent folders of the spreadsheet
var parentFolders = DriveApp.getFileById(spreadsheetId).getParents();
// Check if there are any parent folders
if (parentFolders.hasNext()) {
// Assuming we only have a parent folder
var parentFolder = parentFolders.next();
// For testing purpose
console.log(`Parent Folder: ${parentFolder.getUrl()}`);
// Return the folder object
return parentFolder;
}
// If there are no parent folders, return null
else {
console.error("[ERROR] Critical Error on Getting Parent URL");
return null;
}
}
// Write attributes back to spreadsheet
//
// @ File name
// @ File URL
// @ Folder URL
// @ File Created Date
// @ File Modified Date
function writeToSpreadsheet(sheet, file) {
// Get attributes
// -- Filename
const fileName = file.getName();
// -- File URL
const fileUrl = file.getUrl();
// -- Folder URL
const folderURL = file.getParents().next().getUrl();
// -- File Created Date
const formattedDateCreated = Utilities.formatDate(file.getDateCreated(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
// -- File Modified Date
const formattedDateModified = Utilities.formatDate(file.getLastUpdated(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
// Get the header row
const columnHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// Index
const indexDateCreated = columnHeaders.indexOf('建檔時間');
const indexDateModified = columnHeaders.indexOf('更新時間');
const indexFolderURL = columnHeaders.indexOf('母資料夾');
const indexFileName = columnHeaders.indexOf('作品名稱');
const indexFileURL = columnHeaders.indexOf('檔案位置');
// Prepare the data array for the new row
let row = new Array(columnHeaders.length).fill('');
row[indexDateCreated] = formattedDateCreated;
row[indexDateModified] = formattedDateModified;
row[indexFolderURL] = folderURL;
row[indexFileName] = fileName;
row[indexFileURL] = fileUrl;
// Write to last row in sheet
sheet.appendRow(row);
}