GAS でエクスポートした 共有ドライブ のメタ情報を BigQuery で解析してみる

BigQuery とは何か

BigQuery は、Google Cloud Platform（GCP）上で提供されているフルマネージドのデータウェアハウスサービスです。数TBから数PBに及ぶ大量のデータを高速かつ簡単に分析することができます。基本的なSQLを用いてデータのクエリが可能であるため敷居も低く、インフラのセットアップやサーバーの管理が不要であることも大きなメリットです。

BigQuery の特徴

コストパフォーマンスに優れたフルマネージドサービス

前述した通り、 BigQuery はフルマネージドサービスであるため、企業は物理的なインフラストラクチャやサーバーの管理を気にする必要がありません。BigQuery は、使用した分だけ課金される従量課金制であるため、必要なリソースを必要な分だけ利用することができ、コストパフォーマンスに優れています。

大規模データの高速処理と豊富な分析機能

BigQuery は、数TBから数PBに及ぶ大量のデータを高速に処理することができます。BigQuery は、データを分散して複数のサーバで処理する分散処理方式を採用しています。これにより、大量のデータを一度に処理することができる仕組みを実現しています。また、Python、Java、JavaScript、Go 等のプログラミング言語向けに用意されているクライアントライブラリを使用して、 BigQuery  上のデータを操作することも可能です。

共有ドライブ とは？

共有ドライブ は、 Google Workspace 版 Google ドライブ の機能の一つで、グループでアイテムを共有するための機能です。共有ドライブ を活用することで、企業は部署やプロジェクト単位でファイルを管理することができるようになります。共有ドライブ はAPIも公開されているため、共有ドライブ の名称やID、その他メタ情報をAPI経由で取得することが可能です。

共有ドライブ の情報を BigQuery にエクスポートする手順

共有ドライブ のメタ情報を BigQuery にエクスポートする手順について解説します。前述した通り、 BigQuery へのデータのエクスポートには Google Apps Script を利用します。

① GCP プロジェクトを作成する

まずは GCP プロジェクトの作成をおこないます。既に GCP プロジェクトが準備できている方はこの章をスキップしてください。 GCP プロジェクトを新規で作成するためには以下の準備が必要となります。

• Google アカウント（ Google Workspace アカウント含む）
• クレジットカード情報

準備ができたら、 Google Cloud Platform の公式ページにアクセスし、「無料で使ってみる」から、 GCP プロジェクト開通の手続きを進めてください。初めて利用する方には$300のクレジットがついてきますので、無償で GCP を試すことができます。

② BigQuery でデータセットを作成する

開通が完了したら、 GCP の管理コンソールにアクセスし、左メニューの「 BigQuery 」をクリックします。クリックすると、「SQLワークスペース」画面がデフォルト表示され、 BigQuery のリソースが一覧表示されます。今回作成したGCPプロジェクトの右側にあるアイコンをクリックし、「データセットを作成」を選択してください。

データセットの作成画面が表示されるので、各情報を入力し「データセットを作成」をクリックします。

以下のようにデータセット配下に作成したデータセットがツリー上に表示されれば、作成は完了です。

③ Google Bard を利用してベースとなる GAS コードを生成する

次に「 共有ドライブ のメタ情報を BigQuery にエクスポートする GAS プログラム」を実装します。と、その前に・・・皆さんは Google Bard をご存知でしょうか？ Google Bard は Google が提供する大規模言語モデル「 PaLM2 」が搭載されたチャットベースのアプリケーションです。このツールを利用すれば、 GAS プログラムの大枠を自動生成することができます。例えば、以下のような指示を与えて、GAS プログラムのベースを作成します。

Google Bard を駆使すれば、実装工数を削減することができますが、一つ注意が必要です。それは、 Google Bard に限らず、大規模言語モデルのアウトプットは必ずしも、確実性のある情報ではないというところです。ですので、あくまで Google Bard が実施してくれるのは「ベース部分の生成」であり、最終的なブラッシュアップは人間の手で行うことを前提で考える必要があります。だとしても、工数を大幅に削減できるので、それだけでも価値があると思います。

前置きが長くなりましたが、その上で作成したプログラムが以下となります。

const PROJECT_ID = 'xxxxxxx'; // GCPプロジェクトIDを設定
const DATASET_ID = 'gas_test'; // 先程作成したデータセットID
const TABLE_ID = 'shared_drive'; // データセット配下に作成するテーブルの名称

/**
* 共有ドライブ のメタ情報をBigQueryにエクスポートする
*/
function exportSharedDriveToBigQuery() {
    try {
       // データセット内にテーブルを作成する
       createTableInDataset();
     } catch (e) {
        throw new Error(e.message);
    }
    try {
       // テーブルに共有ドライブ のメタ情報をインサートする
      BigQuery.Tabledata.insertAll({'rows': getShareDrives()}, PROJECT_ID, DATASET_ID, TABLE_ID);
    } catch (e) {
        throw new Error(e.message);
    }
}

/**
* 共有ドライブ のメタ情報を取得する
*/
function getShareDrives() {
    // 共有ドライブ の情報を取得
    const drives = Drive.Drives.list({
         pageSize: 100,
         pageToken: null,
         fields: 'items(id,name,capabilities)'
     }).items;
    const shareDrives = [];
    for (const drive of drives) {
         shareDrives.push({
              json: {
              id: drive.id, 
              name: drive.name,               
              canChangeDomainUsersOnlyRestriction: drive.capabilities.canChangeDomainUsersOnlyRestriction,                            
              canChangeDriveMembersOnlyRestriction: drive.capabilities.canChangeDriveMembersOnlyRestriction,
              }
         });
     }
     return shareDrives;
}



/**

* データセット配下にテーブルを生成する
*/
function createTableInDataset() {
     const table = {
          tableReference: {
          projectId: PROJECT_ID,
          datasetId: DATASET_ID,
          tableId: TABLE_ID
          },
          schema: {
               fields: [
                   {name: 'id', type: 'STRING'},
                   {name: 'name', type: 'STRING'},
                   {name: 'canChangeDomainUsersOnlyRestriction', type: 'BOOLEAN'},
                   {name: 'canChangeDriveMembersOnlyRestriction', type: 'BOOLEAN'},
               ]
           }
     };

     // テーブルを作成
     BigQuery.Tables.insert(table, PROJECT_ID, DATASET_ID);
}

どのようなプログラムかと言うと、 BigQuery 上で作成したデータセット「gas_test」配下に「shared_drive」テーブルを作成し、そのテーブルに対して 共有ドライブ 情報をインポートしています。インポートする 共有ドライブ 情報は以下の通りです。

では、次のステップでは、このプログラムを利用して、 共有ドライブ のメタ情報を BigQuery にエクスポートしてみます。

④  GAS コードを実行し 共有ドライブ 情報をインポート

GAS の画面にアクセスし、画面左上にある「新しいプロジェクト」ボタンを押下します。Code.gsが立ち上がったら、先程のコードを貼り付けます。適宜、固定値を修正したら、「実行」ボタンを押下してください。OAuth認証の画面がポップアップ表示されるので、承認し、プログラムを実行します。

⑤ BigQuery で 共有ドライブ 情報の確認

GAS プログラムが正常終了したら、 GCP 管理コンソールから、 BigQuery の画面を改めて確認します。②で作成したデータセットの配下に「shared_drive」というテーブルが作成されていることがわかります。このテーブルを選択し、右ペインの「プレビュー」を押下します。すると、GAS でインポートされた 共有ドライブ のメタ情報が一覧表示されます。これで BigQuery へのデータのエクスポートは完了です。

⑥ クエリを実行する

次は実際にSQLを実行してデータを操作してみましょう。同じく右ペインで「クエリ」ボタンを押下します。

SQL入力のためのボックスが表示されたら、簡単なSQLを書いてみます。どんな内容かというと「社外共有が許可されている 共有ドライブ を一覧で取得する」ためのSQLです。「実行」ボタンを押下すると、想定通り、社外共有が許可された 共有ドライブ の一覧が表示されます。

以上で今回の内容は終了となります。ここからさらにデータを活用していきたい場合は、 Looker を使って 共有ドライブ の設定状況を可視化することも可能です。

まとめ

今回の内容を通して、GAS を利用すれば、 Google が提供する複数のサービスを統合し、独自の要件を備えたアプリケーションを簡易に実装できることを実感できたのではないでしょうか。 Google Workspace のサービスに限らず、 Google Cloud Platform のサービスも統合できるのは、 GAS の大きな強みです。 Google Cloud Platform にもサーバーレスで簡易にアプリケーションを公開できるサービスがいくつもありますが、小規模なアプリケーションを作成したい場合は、 Google Apps Script が最適です。しかも、 Google Bard のような無料で利用可能なツールを駆使することで、さらに開発工数を削減することができます。アイディア次第で GAS は今回紹介した内容以外でもいろんな場面で活用することが可能です。 GAS をフックに Google Workspace と Google Cloud Platform をフル活用していきましょう！