Breaking

讀書心得 – 田園交響曲 Grafana – 信件的來源 Grafana – 帳戶與權限 Grafana – 簡介與安裝 AI 本地模型 – Stable Diffusion

八寶周的研究小屋

Coding

G-Drive 智慧工坊 – 檔案偵測 003 – 偵測刪除與修改

八寶周 By 八寶周 #
文章目錄
  1. 0. 前言
  2. 1. 重點
  3. 2. 內容
  4. 3. 後話
  5. 4. 參考
  6. 5. 素材
  7. 6. 成品

0. 前言

在前面的實驗中，我們成功取得新增的檔案資訊並依照格式寫入總表中，但我們並沒有建立更新的判斷，也代表刪除的檔案仍殘存於資料表上。

那這回我們就來看看如何偵測並對記錄進行改動吧！

1. 重點

Drive API 只能偵測到在垃圾桶中的檔案，若永久清除則無法觸發以下效果
透過 Spreadsheet 的 createTextFinder 方法避開迴圈搜尋

2. 內容

2.1. 偵測刪除

那對於紀錄影響最大的莫過於刪除，同時減少資料量也能讓我們下一步的偵測修改略為快速。

2.1.1. 資料測試

我們複製 searchFilesCreated 並利用其架構進行改動，並先將其命名為 searchFilesDeleted 方法。那在目前我們無法限制搜尋何時刪除，只能取得整個垃圾桶 30 日的資料。另外我們將排序時間的部分刪除以減少處理時間，畢竟我們只是找資料清除，就不用美化總表了。

JavaScript
// 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);

  // // 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
  //   writeToSpreadsheet(sheetFilesDetection, file);
  // });
}

那接著我將兩張圖片丟到垃圾桶，並執行 searchFilesDeleted 方法。那經由測試結果，也能夠看到我們只找母資料夾路徑下的結果，因此不會增加無謂的資料量。

2.1.2. 總表更新

那我們拿到了已刪除的清單，接著就是對照是否在總表內，並清除上方的資料。那首先我們將該方法最底下註解的地方改為以下樣貌，並接者書寫刪除資料的方法。

JavaScript
// Delete data from spreadsheet one by one
  fileList.forEach(file => {
    // Delete from spreadsheet
    rowDelete(sheetFilesDetection, file);
  });

JavaScript
// 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} 資料`); 
  }
}

那接著我們就依靠上次的紀錄結果進行測試，直接完整執行一次方法。可以看到原先有四行，我們故意將已刪除的一筆複製測試對於資料重複的效果，而在過程中依序搜尋順序刪除對應結果。

那可以看到，就算資料有重複，因為我們有先排序過 row index，因此會從下往上刪不會影響到位置。

2.2. 偵測修改

那在某些情況，我們可能需要搬移或者編輯資料，此時我們就要跟著進行資料的異動確保正確性。

2.2.1. 資料測試

再度複製 searchFilesCreated 並利用其架構進行改動，並先將其命名為 searchFilesModified 方法。那我們這次限制搜尋時間在上次檢查的檔案之後，依此更動 query 參數。

JavaScript
// 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`;

    // 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 on sequence
  // fileList.forEach(json => {
  //   // Use ID to get Object of File Class
  //   file = DriveApp.getFileById(json.id);
  //   // Write to spreadsheet
  //   rowUpdate(sheetFilesDetection, file);
  // });
}

我們簡單將兩個檔案修改名稱，接著執行看看 searchFilesModified 方法檢查結果。

可以看到結果有點出入，因為其包括剛刪除資料行的總表，那我們在修改 query 讓他避開特定檔案。

那為了之後表單能夠簡單遷移，我們避免手動設置條件，但由於沒有限制檔案 id 的參數，我們改用 name 限制檔案名稱的方式來替代 。接著再執行一次試試。

JavaScript
let query = `modifiedTime  > "${lastSearchFilesCreatedTime}" and trashed=false and mimeType != "application/vnd.google-apps.folder" and "${folderId}" in parents and not name = "${SpreadsheetApp.getActiveSpreadsheet().getName()}"`;

2.2.2. 總表更新

那同理，我們需要更新表格上的資訊，我們能夠複製 writeToSpreadsheet 進行修改，並配合剛剛搜尋欄位的辦法進行。

JavaScript
// 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);
  });

JavaScript
// 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} 資料`);
  }
}

完成更改後，我們首先將檔案剪下並移動，並修改上方我們有偵測修改的欄位。並依樣先多複製一行的資料，測試若有重複資料的處理是否正常。

接著執行 searchFilesModified 看看效果如何吧。

3. 後話

總合上述功能，我們的掃描方法基本已經完成，那接著我們就只剩下定時執行與接續呼叫三個掃描方法。

4. 參考

[1] Drive API — Official Doc
https://developers.google.com/drive/api/reference/rest/v3/files/list

[2] createTextFinder — Official Doc
https://developers.google.com/apps-script/reference/spreadsheet/sheet#createtextfinderfindtext

5. 素材

[1] QuAn_ — pixiv
https://www.pixiv.net/users/6657532

[2] P.art — pixiv
https://www.pixiv.net/en/users/35929537

6. 成品

為了增加識別度，我這邊將 writeToSpreadsheet() 改為 rowAppend() 。

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

// 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.sort((a, b) => new Date(DriveApp.getFileById(a.id).getDateCreated()) - new Date(DriveApp.getFileById(b.id).getDateCreated()));

  // 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();
}
八寶周

By 八寶周

Related Post

Coding

AI 本地模型 – PyTorch 環境安裝

八寶周八寶周
Coding

AWS – Billing and Cost – 計費與預算警報

八寶周八寶周
Coding

AWS – Identity Center – 分配組織帳戶與 Single Sign On

八寶周八寶周

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.

You Missed

Perusal

讀書心得 – 田園交響曲

Tech

Grafana – 信件的來源

Tech

Grafana – 帳戶與權限

Tech

Grafana – 簡介與安裝