外部DBがない環境の場合、スプレッドシートのみが唯一のデータ蓄積場所になるわけなのですが、例えば各支店に同じスプレッドシートに書かせるというのは、色々と不安な面があるだけでなく、見せたくないという要望もあったりします。となると、スプレッドシートを各支店別に用意して記入してもらい、本店はそれを収集する事になるわけですが、いちいち支店のスプレッドシートを開いてコピペでは非生産的です。となると、これらのファイルをリスト化し、そのリストを元にスクリプトでデータ収集が王道になると思います。

そこで必要になるのが今回のテクなのですが、自分もfilelistというシートにボタン一発で指定のスプレッドシートのIDを指定フォルダ内から収集し、filelistを元にデータを集めるスクリプトを常用しています。filelistの構築を主に例としてコードを紹介していきます。これには、指定フォルダ以下のサブフォルダ内のスプレッドシート全てを見に行くようになっているので、あまりGoogle Driveで深い階層を作ると後にこういうスクリプトを回す上で問題になるので、なるべく浅く作ることと、Google Driveはシンボリックリンク的に同一ファイルを別のフォルダにも配置が出来るので、それを利用するのもスピードアップに効果的ですよ。

今回使用するスプレッドシート等

Google Driveの特徴

Google DriveとそのAPIの特徴として、以下の点が挙げられます。

  1. ディレクトリという概念があるわけじゃない。あくまでも、人間がわかりやすい形で、ディレクトリツリー構造のようなインターフェースになっているだけで、すべてのファイルがルートディレクトリに存在している状態を思い浮かべると良い。
  2. 特定のディレクトリに属しているという情報をファイルが持っている。
  3. keyで管理されているため、同じファイル名の同じファイルが同じレベルで存在することが出来る。
  4. 検索をする際に、サブディレクトリまで精査してくれない。
  5. 細かく検索条件を設定できるものの、ちょっといまいちな点も多い。
  6. DriveAppとDocsListの2つの同じようなクラスがありましたが、DocsListは廃止されました
  7. 現在、フォルダ単位でのコピーが出来ない

こんな感じです。

WindowsやmacOSのようなOS上でのファイルの検索とは少々異なる点があるため、正直な所、決して使いやすいわけではない。しかし、複数のファイルのkeyを知る必要性があるようなケースでは、必須のテクニックであり、そこで得たkeyのデータを元に、データを取りに行くなどのコードを書くことになるわけです。

ファイルやフォルダを別のフォルダにも所属させる

Google Driveには、UNIXで言うところの「シンボリックリンク」のような機能が実はあります。普通のファイルサーバではちょっと見かけないものですが、1つのファイルやフォルダを複数のフォルダに所属させる事が可能です。どちらも実体でありショートカットではありません。ファイルのアクセス権限が複雑になったりするので、取扱い注意の機能ですが、今回のようにあちこちに散らばってる同じようなファイルを、1箇所のフォルダに集めるにはもってこいの機能です。

やり方は簡単

  1. ファイルやフォルダを選択する
  2. Shift + Zキーを押す(その他のショートカットキーはこちらを参照
  3. メニューが出てくるので、所属先まで行き「ここに追加」を選択する
  4. 同一のフォルダやフォルダが別の場所にも表示されるようになる

図:どちらも実体を表しています。

また、この作業をしたファイルがどこのフォルダに所属しているのか?また、所属の解除も簡単です。

  1. そのファイルやフォルダを選択する
  2. 右上にある(i)のボタンをクリックしてファイル情報のサイドバーを表示
  3. サイドバーの詳細タブをクリックする
  4. パスの項目に所属先一覧が出てくる。☓印をクリックすると、所属の解除ができる

図:所属先一覧も簡単に確認できます。

ソースコード

単一のフォルダ内のファイルを列挙する

  • 今回は、folderidに検索対象のフォルダのIDを予め、入れておいてあるので、これを元にフォルダの探索を実行し、ファイルのIDを列挙させています。
  • Drive.searchFilesではmimeTypeにてGoogleスプレッドシートを条件に指定していますので、他のファイルはヒットしないようになっています。

サブフォルダまで含めてファイルを探索するコード

  • 今回は条件を付けて、Google Spreadsheet形式のファイルだけを探索してピックアップしています。
  • 指定フォルダ以下に所属しているスプレッドシートのIDと名前をリストアップしています。
  • Google Driveの他のフォルダにも所属させるテクニックを併用すると、1箇所にバラバラのフォルダに属しているファイルを集めておくことができるので、コード実行が早く完了します。
  • 特定フォルダではなく、ルートフォルダ(マイドライブ直下)から始める場合は、DriveApp.getRootFolder().getId();でIDを取得させると良い。

基準フォルダから下のフォルダ階層をすべてリストアップするコード

  • folderlistシートに指定フォルダ以下のフォルダ構造をリストアップします。
  • そのフォルダのIDと所属してる親フォルダのIDを記述します。
  • 現在、Google Driveはフォルダ単位でのコピーができません。よって、Google Apps Scriptで作成し作りこんだファイルの複製は、サブフォルダまで含めて、すべて自分で手動で行う必要性があります。
  • folderlistに出来上がるリストは、左から【フォルダ名】【そのフォルダのID】【所属する親フォルダのID】となっています。

ポイント

検索条件概要

これがもっとも面倒な点。そして自分がハマりまくった点でもあります。理解するのに時間を消費してしまいました。
検索条件は自由気ままに入れることができるわけじゃなく、ルールがあります。下の参考リンクにあるDrive SDK – Search for Filesを見ると、指定方法がわかるのですが、いかにもGoogle Driveだなぁという項目が揃っています。
  1. title ・・・要するにファイル名での検索をする
  2. fullText・・・要するに全文検索。そのワードが入っているファイルを検索する。
  3. mimeType・・・ファイルの形式。正直ちょっと指定には癖がある。今回のキモの一つ。
  4. modifiedDate・・・編集日で検索をする。
  5. lastViewedByMeDate・・・最後に自分が見たファイルの日付で検索するもの。
  6. trashed・・・削除したファイルを検索。ゴミ箱あさり。
  7. starrad・・・⭐をつけたファイルを検索。
  8. parents・・・親フォルダIDに所属しているファイルを検索
  9. owners・・・ファイルのオーナーで検索する
  10. writers・・・そのファイルの編集者で検索する
  11. readers・・・そのファイルの閲覧者で検索する
  12. sharedWithMe・・・自分と共有しているファイルについて検索する
上記のうちで、普段使いで使いそうなのはそう多くありません。特に取り上げるべきはmimeTypeの指定方法と、そして、ここにはありませんが、スクリプト中の変数をメソッドの条件式の一部として使う方法が重要になります。検索条件を指定する時の注意点は
  1. かならずダブルコーテーション(” “)でくくった中にオプションを書くこと。
  2. 条件の指定はシングルコーテーション(’ ‘)でくくった中に書くこと。
  3. 変数を用いる場合にはちょっと特別な書き方をする。下の方にある検索条件の一部に変数を使用するを参考にしてください。
  4. 支店別に用意した特定のスプレッドシートという事をやりたい場合には、更にファイル名を決めて於いてそれを含むという条件を加え、サブフォルダ以下にその名称を含む他のファイルを設置しないなどの運用ルールが必要です。

mimetypeの指定

今回想定しているケースは特定のフォルダ内のファイルを検索することにあるのですが、スプレッドシートデータだけをヒットさせたい場合には、ファイルタイプが絶対不可欠です。下のEnum MimeTypeで紹介されているようなものでも動くのかもしれませんが、今回は普通通り、きちんとしたMimeTypeを指定することにしました。この辺りがしっかりリファレンスに記載されていなかったりします。Google Docs関係のMimeTypeはこちらにまとめられていますので参考にしましょう。
今回は、スプレッドシートのみをヒットさせたいので、以下のように記述します。
この条件式で、スプレッドシートのみをリストアップすることが出来るようになります。

検索条件の一部に変数を利用する

プログラミングじゃ当たり前のように変数を検索条件の一部に使用するわけなのですが、VBA上がりの人間などだと、ダブルコーテーションで閉じたあとに、アンパサンドや+などで続ければ条件式に入ると思いがち。しかし、Google Apps Scriptでは、シングルコーテーションでくくり、なおかつダブルコーテーションでくくり、その中で+変数+と記述するのがルールになっています。これをやらないとエラーになります。気がつくのに自分は無駄な時間を大量に消費しました。ということで、以下のようになります。keyという変数の中の値を検索条件の一部として使用しています。

複数の条件式をつなげる場合には、andなどをいれる点は、他の言語と変わりありません。

サブフォルダ以下を探索させるために

今回のスクリプトでは、指定フォルダ内だけでなく、指定フォルダ内にあるサブフォルダ以下のフォルダ内全ての配下のスプレッドシートを列挙するという仕組みになっています。その為、探索段階では、どれだけのサブフォルダが隠れているのかはわかりません。そこで、配列としてfolderlistを用意し、新たにフォルダを発見したら配列に追加し、終了したものから配列から削除するというルーチンが入っています。配列の上から順番にフォルダを探索していくので0番目を削除しているのはそういうことです。(folderlist.splice(0,1)がそれをやってる。)

最終的にフォルダリストが空になったら、探索は終了し、flielistに格納されてる配列を一気にスプレッドシートに書き込みをします。

実行と結果

スプレッドシートのメニューより「ファイル探索」を開きます。3個のメニューがあり、リストは洗い替えでリストアップされる仕組みになっています。挙動は動画のような感じになります。フォルダ階層が複雑で深すぎると、タイムアウトする恐れがありますので、実用時には、特定のファイルはシンボリックリンク的に、1つの特定フォルダにファイルを集めておくと良いです。

  1. 単一フォルダ – 指定フォルダ直下のファイルをリストアップします。
  2. サブフォルダ含む – 指定フォルダ直下以外にもサブフォルダ以下もすべて探索しリストアップします。
  3. フォルダ階層列挙 – 指定フォルダ以下のフォルダ階層構造を列挙します。

関連リンク

共有してみる: