📸

Google App ScriptでGoogle Drive内のファイルを表管理

に公開
Google Apps Script
tech

課題

  • テスト業務中、実行結果の証跡残しなどをGoogle Driveに残しておく必要があったが管理が大変だった。
    • 証跡とり忘れ,確認,etc...

目的

Google App Script(GAS)を用いて、Google Drive(以下、ドライブ)にあるファイル名,URL,...などを一覧化する。また、必要な証跡の有無を都度チェックする。


準備

  1. ドライブにフォルダを作成（ここでは名前をevidenceとする）
  2. ドライブのURLに注目https://drive.google.com/drive/folders/${folderID}
    URLのfolders/以降がフォルダIDとなる。
  3. 任意の場所にGoogleスプレッドシートを作成し、シート1の名前を「ファイル一覧」、シート2の名前を「必要エビデンス」とする。
  4. シート画面の上部の「拡張機能」->「Apps Script」
    からGASを書ける

エビデンス起票スクリプト

function checkNewFilesRecursive() {
// ファイルを収集
  function collectFilesRecursive(folder, currentPath, fileList) {
    var folderPath = currentPath ? currentPath + "/" + folder.getName() : folder.getName();

    var files = folder.getFiles();
    while (files.hasNext()) {
      var file = files.next();
      fileList.push({
        id: file.getId(),
        name: file.getName(),
        url: file.getUrl(),
        lastUpdated: Utilities.formatDate(file.getLastUpdated(), Session.getScriptTimeZone(), "yyyy/MM/dd HH:mm:ss"),
        size: file.getSize(),
        path: folderPath
      });
    }

    // サブフォルダ
    var subfolders = folder.getFolders();
    while (subfolders.hasNext()) {
      collectFilesRecursive(subfolders.next(), folderPath, fileList);
    }
  }

  var folderId = "1I0pM5a2moSvlDT1BhCQTc20g1F-pVweU"; // 対象フォルダID
  var folder = DriveApp.getFolderById(folderId);

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ファイル一覧");
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet("ファイル一覧");
    sheet.appendRow(["ファイルID", "ファイル名", "URL", "更新日", "サイズ(Bytes)", "フォルダパス"]);
  }

  // シートの既存データ取得
  var lastRow = sheet.getLastRow();
  var existingData = [];
  if (lastRow > 1) {
    existingData = sheet.getRange(2, 1, lastRow - 1, 6).getValues();
  }

  // シート上のファイルIDマップ
  var existingMap = {};
  existingData.forEach((row, index) => {
    existingMap[row[0]] = index + 2; // シート行番号（ヘッダー1行を考慮）
  });

  // Driveの全ファイルを収集
  var driveFiles = [];
  collectFilesRecursive(folder, "", driveFiles);

  // Drive上のIDセット
  var driveIds = driveFiles.map(f => f.id);

  // 削除されたファイルはシートから削除
  for (var i = existingData.length - 1; i >= 0; i--) {
    if (driveIds.indexOf(existingData[i][0]) === -1) {
      sheet.deleteRow(i + 2);
    }
  }

  // 追加または更新
  driveFiles.forEach(f => {
    if (existingMap[f.id]) {
      // 更新された場合は上書き
      sheet.getRange(existingMap[f.id], 2, 1, 5).setValues([[
        f.name,
        f.url,
        f.lastUpdated,
        f.size,
        f.path
      ]]);
    } else {
      // 新規追加
      sheet.appendRow([f.id, f.name, f.url, f.lastUpdated, f.size, f.path]);
    }
  });

  Logger.log("Driveとシートの同期完了");
}

ファイルチェック

function checkEvidenceMarkNoExt() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var driveSheet = ss.getSheetByName("ファイル一覧");      // Drive同期済み
  var evidenceSheet = ss.getSheetByName("必要エビデンス"); // エビデンスチェック用

  if (!driveSheet || !evidenceSheet) {
    SpreadsheetApp.getUi().alert("シートが存在しません。名前を確認してください。");
    return;
  }

  // Drive上のファイル名リスト取得（拡張子なし）
  var lastRowDrive = driveSheet.getLastRow();
  var driveFiles = [];
  if (lastRowDrive > 1) {
    driveFiles = driveSheet.getRange(2, 2, lastRowDrive - 1, 1).getValues().flat()
      .map(name => name.replace(/\.[^/.]+$/, "")); // 拡張子を除去
  }

  // 必要エビデンスリスト取得（拡張子なしで比較）
  var lastRowEvidence = evidenceSheet.getLastRow();
  if (lastRowEvidence < 1) return;
  var evidenceList = evidenceSheet.getRange(2, 1, lastRowEvidence - 1, 1).getValues();

  // 判定結果作成
  var results = evidenceList.map(function(row) {
    var fileName = row[0];
    if (!fileName) return [""]; // 空白は空白のまま
    var baseName = fileName.replace(/\.[^/.]+$/, ""); // 拡張子除去
    return [driveFiles.includes(baseName) ? "〇" : "✖"];
  });

  // 2列目に結果を書き込み
  evidenceSheet.getRange(2, 2, results.length, 1).setValues(results);
}

定期実行

トリガー⇒時間ベース を設定することで、起票&チェックを定期実行してくれます！

Discussion