Notice: Function _load_textdomain_just_in_time was called incorrectly. Translation loading for the ultimate-addons-for-gutenberg domain was triggered too early. This is usually an indicator for some code in the plugin or theme running too early. Translations should be loaded at the init action or later. Please see Debugging in WordPress for more information. (This message was added in version 6.7.0.) in /opt/bitnami/wordpress/wp-includes/functions.php on line 6114
G-Drive 智慧工坊 – 檔案偵測 004 – 部屬與測試 - 八寶周的研究小屋

G-Drive 智慧工坊 – 檔案偵測 004 – 部屬與測試

0. 前言


在過往的三篇中我們完成了所有物件的變動偵測功能,將著就要進行基礎的壓力測試與共用資料夾測試,最終確認後進行部屬。

1. 重點


GAS 本身沒有偵測磁碟變動的 Trigger,我們改用每日定時的方式

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;
}

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.