0. 前言
在過往的三篇中我們完成了所有物件的變動偵測功能，將著就要進行基礎的壓力測試與共用資料夾測試，最終確認後進行部屬。
1. 重點
2. 內容
2.1. 共享資源測試
在上回的測試中只展現了個人的垃圾桶，正巧該專案本身就是要在共用環境使用因此順道測試 run 方法。
此時我換隻帳號，將上回殘存的檔案刪除與修改各一筆，另外上傳一份檔案。當然若你只是個人使用，可以跳過這邊的調整。
接著切回帳號執行 GAS 的三種方法，雖然檢測新增與修改都能正常運作，但這時會發現很嚴重的事實。因為 searchFilesDeleted() 是翻找用戶的垃圾桶，因此我們無法抓到其他用戶刪除的檔案。所以我們必須將刪除的部分獨立出來讓每個用戶都註冊 Trigger 。
雖然我們教學沒提及，但 Shared Drive 的話則是本身共用一個垃圾桶，因此不必擔心這個議題。
2.2. 時間測試
那緊接著我就要為各位展示基礎的壓力測試，或許各位只是要記錄如 Doc 或 Spreadsheet 等資料，每日增加的數目並不大多，但我目前一位作者的作品抓下來並人工整理後有時高達 500 – 600 張甚至超過，那我接著就利用執行時間來進行推估。
我們一般用戶的單次執行時間上限是 6 分鐘，並現在似乎沒了每分鐘最大修改 Spreadsheet 操作數量。
那我先將表單建立副本到其他地方進行測試，那我們依序觸發以下功能分開計時 。
— 先清空紀錄總表
— 先將 100 張照片上傳在一個資料夾，
2.2.1. 執行 GAS 紀錄新增用時。
在 43:29 執行於 47:13 完成 –> 3 分 44 秒，但我們必須進行 sorting 才能存到正確的時間戳，我們試試看如何加速。那首先我將 searchFilesCreated() 內的排序方法改為 function，並減少 DriveApp 的呼叫。
JavaScript
// Sorting by createdDate
fileList = sortFilesByCreatedDate(fileList); JavaScript
// Sort files by File Time Created
function sortFilesByCreatedDate(fileList) {
// Fetch metadata for all files once
const filesWithDates = fileList.map(file => {
const fileMeta = DriveApp.getFileById(file.id);
return {
id: file.id,
name: file.name,
createdDate: fileMeta.getDateCreated()
};
});
// Sort the array by created date
filesWithDates.sort((a, b) => a.createdDate - b.createdDate);
// If needed, you can now map this back to the original fileList format
const sortedFileList = filesWithDates.map(file => ({
id: file.id,
name: file.name
}));
return sortedFileList;
}從 07:35 執行到 09:31 –> 1 分 56 秒
2.2.2. 執行 GAS 紀錄更新用時
我利用 Windows 的 .bat 檔案將資料夾中所有檔案改名，我們接著執行看看更新的部分。
於 20:03 開始至 21:34 –> 1 分 31 秒
2.2.3. 刪除資料並記錄 GAS 用時
那接著我們將資料夾刪除再度進行測試，卻發現是資料是空的，因為在垃圾桶中系統攤不開裡面的檔案，我們退回一步，將資料夾中的圖片全數刪除。
於 27:49 開始至 28:30 –> 41 秒，但這是在垃圾桶乾淨的環境下測的，因此迴圈數量有減少。
經過這些實驗，可以知道其實我們這個程式由於迴圈居多，若綁一起非常容易超時，為此我們需要切割。
2.3. 執行方法
首先我們將書寫新的方法專門給後續的 Trigger 觸發，那在步驟上我們會依靠偵測刪除先減少表上的資料量，接著更新有變動的資料，最終才新增新的紀錄。
如果你是個人用戶可以使用以下方法，且看過上述測試不怕超時的可用以下方法。
JavaScript
// Run all functions in sequences
function run() {
console.info(`【刪除階段】`);
try { searchFilesDeleted(); } catch (error) { console.error(`【刪除報錯】`); }
console.info(`【修改階段】`);
try { searchFilesModified(); } catch (error) { console.error(`【修改報錯】`); }
console.info(`【新增階段】`);
try { searchFilesCreated(); } catch (error) { console.error(`【新增報錯】`); }
console.info(`【工作完成】`);
}但如果跟我一樣套用在共享資料夾，則必須拆開刪除與兩個方法。那由於刪除會影響資料表的排序，但其本身也沒有對於時限的需求，只能偵測垃圾桶中全部 30 日內的檔案，因此我們不用特別修改設定。而 Trigger 的定時器是以整點為基準，因此我們調快在 1 小時前觸發即可。
那我範例的 run 方法則只有修改與新增的功能。
JavaScript
// Run all functions in sequences
// -- yet run searchFilesDeleted() 1 hr ere for problems on detecting others' trash can
function run() {
console.info(`【修改階段】`);
try { searchFilesModified(); } catch (error) { console.error(`【修改報錯】`); }
console.info(`【新增階段】`);
try { searchFilesCreated(); } catch (error) { console.error(`【新增報錯】`); }
console.info(`【工作完成】`);
}完成之後，我們將 Properties 中的時間再往前撥動之後，換執行 run() 看看是否正常。
但如果我們有超時的風險，則我建議用原始的三個程式，將檢查修改與新增放同個時段，他們之間不會互相影響。
2.4. 定時機制
那我假設各位作息正常，並且不怕超時，我們就在 04:00 的時候觸發 run 方法。
我們點選左側選單中的鬧鐘圖樣（Trigger），並點選又下的藍色按鈕新增觸發器。接著如以下設定，並選擇執行的時間從 4am – 5am 。右側的是當有報錯時的通知設定，預設是每日，但我調整成立即。
那接著與我相同是共享資料夾的用戶，我們依照 24 小時制我們最多可以提供 23 個使用者進行刪除檢測，分開時段是為了避免相衝導致行數被錯刪。對於小團體來說已是非常足夠，也適用於區分管理員的群組。
我們只要注意時間不要與 run 方法同時段，以及用戶之間不相衝就可以了。
那像我一樣資料過大的，則將三個 search 方法建立 Trigger，並讓刪除避開偵測修改與新增即可。
3. 後話
到這邊我們的檔案偵測系統基本就完成，但我們可以看到他還是非常粗淺的版本，十分的脆弱又緩慢。
但我們就將就使用，但基本檔案若沒很多還是個實用的小工具。但若有成功我會再推出更新後的方案。
4. 參考
[1] Quotas for Google Services — Official Doc
https://developers.google.com/apps-script/guides/services/quotas
5. 成品
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;
}
}
// 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}`); }
}
// Run all functions in sequences
// -- yet run searchFilesDeleted() 1 hr ere for problems on detecting others' trash can
function run() {
console.info(`【修改階段】`);
try { searchFilesModified(); } catch (error) { console.error(`【修改報錯】`); }
console.info(`【新增階段】`);
try { searchFilesCreated(); } catch (error) { console.error(`【新增報錯】`); }
console.info(`【工作完成】`);
}
// Search Files Newly Deleted
//
// - Loop through stack to see if any folder need to be scan
// -- Get all soft-deleted items under folder
// ---- Loop through every file
// ------ Using file id to search which row data at by URL
// -------- Delete the row
// -- Look into the subfolder
// ---- Push subfolder to stack to continue while-loop
function searchFilesDeleted() {
// Getting the spreadsheet for data writing
const sheetFilesDetection = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(PropertiesService.getScriptProperties().getProperty('sheetFilesDetection_sheetName'));
// Init first folder path
let folderStack = [DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next().getId()];
// List for search result
let fileList = [];
while (folderStack.length > 0) {
// // Debugging
// console.log(folderStack);
// Choosing folder to run process
let folderId = folderStack.pop();
// Query
let query = `trashed=true and mimeType != "application/vnd.google-apps.folder" and "${folderId}" in parents`;
// 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);
// Find all subfolders
const folders = DriveApp.getFolderById(folderId).getFolders();
// Add subfolders to stack
while (folders.hasNext()) {
folderStack.push(folders.next().getId());
}
}
// Debugging
console.log(fileList);
// Delete data from spreadsheet one by one
fileList.forEach(file => {
// Delete from spreadsheet
rowDelete(sheetFilesDetection, file);
});
}
// Search Files Newly Modified
//
// - Loop through stack to see if any folder need to be scan
// -- Comparing Files Modified Time and Latestest File Created Time on last execution to see if is new
// -- Look into the subfolder
// ---- Push subfolder to stack to continue while-loop
// - Update attributes to spreadsheet
function searchFilesModified() {
// 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');
// Init first folder path
let folderStack = [DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next().getId()];
// List for search result
let fileList = [];
while (folderStack.length > 0) {
// // Debugging
// console.log(folderStack);
// Choosing folder to run process
let folderId = folderStack.pop();
// Query
let query = `modifiedTime > "${lastSearchFilesCreatedTime}" and trashed=false and mimeType != "application/vnd.google-apps.folder" and "${folderId}" in parents and not name = "${SpreadsheetApp.getActiveSpreadsheet().getName()}"`;
// 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);
// Find all subfolders
const folders = DriveApp.getFolderById(folderId).getFolders();
// Add subfolders to stack
while (folders.hasNext()) {
folderStack.push(folders.next().getId());
}
}
// Debugging
console.log(fileList);
// Write attributes to spreadsheet one by one
fileList.forEach(json => {
// Use ID to get Object of File Class
file = DriveApp.getFileById(json.id);
// Write to spreadsheet
rowUpdate(sheetFilesDetection, file);
});
}
// Search Files Newly Created
//
// - Loop through stack to see if any folder need to be scan
// -- Comparing Files CreatedDate and Latestest File Created Time on last execution to see if is new
// -- Look into the subfolder
// ---- Push subfolder to stack to continue while-loop
// - Resort full fileList by file created date
// - Write attributes to spreadsheet
// - Reset property "lastSearchFilesCreatedTime" to latest
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');
// Init first folder path
let folderStack = [DriveApp.getFileById(SpreadsheetApp.getActiveSpreadsheet().getId()).getParents().next().getId()];
// List for search result
let fileList = [];
while (folderStack.length > 0) {
// // Debugging
// console.log(folderStack);
// Choosing folder to run process
let folderId = folderStack.pop();
// Query
let query = `createdTime > "${lastSearchFilesCreatedTime}" and trashed=false and mimeType != "application/vnd.google-apps.folder" and "${folderId}" in parents`;
// 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);
// Find all subfolders
const folders = DriveApp.getFolderById(folderId).getFolders();
// Add subfolders to stack
while (folders.hasNext()) {
folderStack.push(folders.next().getId());
}
}
// Sorting by createdDate
fileList = sortFilesByCreatedDate(fileList);
// Debugging
console.log(fileList);
// Write attributes to spreadsheet on sequence
fileList.forEach(json => {
// Use ID to get Object of File Class
file = DriveApp.getFileById(json.id);
// Write to spreadsheet
rowAppend(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());
}
}
// Delete all related row datas from spreadsheet
function rowDelete(sheet, file) {
// index
const fileId = file.id;
// Search result
var occurrences;
// TMP storage for row index to delete from end to front
var rows = [];
try {
// Find all URL contains the id
occurrences = sheet.createTextFinder(fileId).findAll();
// If found
if (occurrences.length > 0) {
// Push all into list
occurrences.forEach(range => { rows.push(range.getRow()); });
// Change to by from end to front
rows.sort((a, b) => b - a);
// Delete all rows related
rows.forEach(row => { sheet.deleteRow(row); })
console.log(`【刪除成功】 -- 從偵測表刪除 ${fileId} 資料`);
}
} catch (error) {
console.error(`【刪除失敗】 -- 從偵測表刪除 ${fileId} 資料`);
}
}
// Update attributes in spreadsheet
//
// @ Author Name
// @ File Name
// @ File URL
// @ Folder URL
// @ File Modified Date
function rowUpdate(sheet, file) {
// index
const fileId = file.getId();
// Search result
var occurrences;
// TMP storage for row index to delete from end to front
var rows = [];
try {
// Find all URL contains the id
occurrences = sheet.createTextFinder(fileId).findAll();
// If found
if (occurrences.length > 0) {
// Push all into list
occurrences.forEach(range => { rows.push(range.getRow()); });
// Get the header row
const columnHeaders = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
// Index
const indexWorkCategory = columnHeaders.indexOf('繪畫風格');
const indexWorkAuthor = columnHeaders.indexOf('作家名稱');
const indexDateCreated = columnHeaders.indexOf('建檔時間');
const indexDateModified = columnHeaders.indexOf('更新時間');
const indexFolderURL = columnHeaders.indexOf('母資料夾');
const indexWorkName = columnHeaders.indexOf('作品名稱');
const indexWorkURL = columnHeaders.indexOf('檔案位置');
// Read new values
// -- File name
const parts = file.getName().split(' - ');
// ---- Author Name
const authorName = parts[0];
// ---- Work Name
const workName = parts[1];
// -- File URL
const workURL = file.getUrl();
// -- Folder URL
const folderURL = file.getParents().next().getUrl();
// -- File Modified Date
const formattedDateModified = Utilities.formatDate(file.getLastUpdated(), Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss");
// Update all rows related
rows.forEach(row => {
let range = sheet.getRange(row, 1, 1, sheet.getLastColumn());
// Read all original values as array
let data = range.getValues()[0];
// Replace values
data[indexWorkAuthor] = authorName || "Unknown";
data[indexDateModified] = formattedDateModified;
data[indexFolderURL] = folderURL;
data[indexWorkName] = workName || "Undefined";
data[indexWorkURL] = workURL;
// Replace row
range.setValues([data]);
})
console.log(`【更新成功】 -- 從偵測表修改 ${fileId} 資料`);
}
} catch (error) {
console.error(`【更新失敗】 -- 從偵測表修改 ${fileId} 資料`);
}
}
// Write attributes back to spreadsheet
//
// @ Artstyle
// @ Author Name
// @ File Name
// @ File URL
// @ Folder URL
// @ File Created Date
// @ File Modified Date
function rowAppend(sheet, file) {
// Get attributes
// -- File Name
const parts = file.getName().split(' - ');
// ---- Author Name
const authorName = parts[0];
// ---- Work Name
const workName = parts[1];
// -- File URL
const workURL = 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 indexWorkCategory = columnHeaders.indexOf('繪畫風格');
const indexWorkAuthor = columnHeaders.indexOf('作家名稱');
const indexDateCreated = columnHeaders.indexOf('建檔時間');
const indexDateModified = columnHeaders.indexOf('更新時間');
const indexFolderURL = columnHeaders.indexOf('母資料夾');
const indexWorkName = columnHeaders.indexOf('作品名稱');
const indexWorkURL = columnHeaders.indexOf('檔案位置');
// Prepare the data array for the new row
let row = new Array(columnHeaders.length).fill('');
row[indexWorkCategory] = getArtStyle(file.getParents().next());
row[indexWorkAuthor] = authorName || "Unknown";
row[indexDateCreated] = formattedDateCreated;
row[indexDateModified] = formattedDateModified;
row[indexFolderURL] = folderURL;
row[indexWorkName] = workName || "Undefined";
row[indexWorkURL] = workURL;
// Write to last row in sheet
sheet.appendRow(row);
}
// Get artstyle based on folder name
//
// - Look up parent folder until no more @ in folder name
function getArtStyle(folder) {
// With @ -> Artist portfolio
if (folder.getName().indexOf('@') !== -1) {
// Find it's parent
do {
folder = folder.getParents().next();
} while (folder.getName().indexOf('@') !== -1);
}
// Art style
return folder.getName();
}
// Sort files by File Time Created
function sortFilesByCreatedDate(fileList) {
// Fetch metadata for all files once
const filesWithDates = fileList.map(file => {
const fileMeta = DriveApp.getFileById(file.id);
return {
id: file.id,
name: file.name,
createdDate: fileMeta.getDateCreated()
};
});
// Sort the array by created date
filesWithDates.sort((a, b) => a.createdDate - b.createdDate);
// If needed, you can now map this back to the original fileList format
const sortedFileList = filesWithDates.map(file => ({
id: file.id,
name: file.name
}));
return sortedFileList;
}