Google Sheetでファイル情報を自動取得するシステムを作る

今回は、Google Sheetでファイルを自動取得するシステムを簡単に構築してみたいと思います。これを行うことにより「あの資料どこやったっけ」が無くなって資料を探すのが楽になるかも！？

１．準備

• Google Drive

• 資料（ドキュメント・エクセル など…）

２．フォルダーの作成

下記のような感じでGoogle Drive内にいつも通りの感じでフォルダを作って資料を整理しましょう。この時フォルダ内にスプレッドシートも作成しましょう。

フォルダ場所のイメージ画像

３．スプレッドシートの作成

こんな感じにREADME、検索機能、データベース、3つのタブを作成します。
READMEには、ファイル名の約束事とかを書きます。特に接頭辞を書くことで、後で検索機能を実装する際に活躍します！

README

検索機能

４．スクリプトを作成

スプレッドシートの上部にあるメニューバーから拡張機能を選択その中の「Apps Script」を選択します。次にコード.jsと書かれているところに下記のコードを書いてください。XXXXXと書かれているところにIDをコピペしてください。スプレッドシートの場合は、URLで（d/XXXXX/edit）ってなっていると思うのでその部分をコピペ。フォルダは、（folders/）から後ろを全部コピペしてください。

// フォルダ内のファイルとサブフォルダを再帰的にリストアップする関数
function listFolderContents(folder, values) {
  var files = folder.getFiles();
  
  while (files.hasNext()) {
    var file = files.next();
    var name = file.getName();
    var id = file.getId();
    var url = file.getUrl();
    var lastUpdate = file.getLastUpdated();
    var owner = file.getOwner().getName();
    values.push([id, name, url, lastUpdate, owner]);
  }
  
  var subfolders = folder.getFolders();
  while (subfolders.hasNext()) {
    var subfolder = subfolders.next();
    listFolderContents(subfolder, values);
  }
}

// メインの関数
function myFunction() {
  // スプレッドシートのIDとシート名を指定
  var spreadsheetId = "XXXXXXX"; // スプレッドシートのIDを入力してください
  var sheetName = "データベース"; // シート名を変更する必要がある場合は変更してください
  
  // スプレッドシートから指定されたシートを取得
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  
  // フォルダのIDを指定
  var folderId = "XXXXXXX"; // フォルダのIDを入力してください
  var folder = DriveApp.getFolderById(folderId);
  
  // フォルダ内のファイルとサブフォルダを再帰的にリストアップし、valuesに格納
  var values = [];
  listFolderContents(folder, values);
  
  // スプレッドシートの内容をクリア
  sheet.clear(); 
  
  // ヘッダー行を追加
  sheet.appendRow(["File ID", "Name", "URL", "Last Updated", "Owner"]);
  
  // データを追加
  if (values.length > 0) {
    sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
  }
}

５．検索機能

下記の図のような状態を作ってください。

検索機能

次に、File IDのところに下記のコードをコピペしてください。必要に応じてセルの場所などを調節してください。ここは自分がやりたい機能を盛り込めば大丈夫です！

=IF(D2="AND", QUERY('データベース'!A:F, "where B contains '" & B2 & "' and B contains '" & C2 & "'", 1), IF(D2="OR", QUERY('データベース'!A:F, "where B contains '" & B2 & "' or B contains '" & C2 & "'", 1), "Invalid operation")) 

６．完成！！

これでフォルダー内のファイル情報を表示できるようになり、検索もできるようになりました！必要に応じてコードをカスタマイズして資料管理を少しでも楽にできるようにしてみましょう！

７．まとめ

いかがでしたでしょうか？今後も不定期ですが、こういった情報を共有したいと思います！
最後まで読んでいただきありがとうございました！！いいなって思っていただけた方は、是非スキ&フォローよろしくお願いします！！！！