GASでSpreadSheetのデータをBigQueryに入れてみた

日本情報通信の上野です。
今回はスプレッドシートの表をBigQueryに取り込むGASを作成していきたいと思います。
GASはスプレッドシートの拡張機能のApp Scriptから作成します。

コードの作成

まずは、エディタのコード.gsを開いてmain関数を作成していきます。

main関数では、以下のようにとりあえず接続するGCPのプロジェクトIDとBigQueryのデータセット名を指定します。
今回は編集したシートをリアルタイムにGASでBigQueryに取り込むので、対象のシートはアクティブスプレッドシートのアクティブシートです。
BigQueryのテーブル名はシート名と同じになるようにしました。

次に、テーブルとスキーマの定義を行います。
今回は、「id」、「name」、「memo」の3カラムを持つ表をBigQueryに取り込んでいきます。
シート名と同名のテーブルが指定したBigQueryのデータセットにすでに存在していた場合は、すでにあるテーブルを削除してから新しくテーブルを作成しなおすことでテーブルの内容を更新します。

以下のソースコードでは、取り込む範囲を決めてconvCsv関数を呼び出し、表を読み込んでいます。
また、表の1行目はカラム名が入っているので読み込みをスキップさせます。
最後にBigQuery.Jobs.insertでBigQueryのテーブルにデータを挿入しています。

以下のソースコードはスプレッドシートの表を取り込んでCSVに変換する関数です。
上記のmain関数から呼び出されて実行されます。

APIの追加

コードを作成し終えると、次にBigQueryのAPIを追加しなければなりません。
APIの追加の方法は、サービスからBigQuery APIを選択し、追加を押下します。

トリガーの設定

作成したGASを自動で実行するには、トリガーのトリガーを追加から、トリガーの設定を行う必要があります。
実行する関数はmainを選択し、イベントのソースはスプレッドシートから、イベントの種類は編集時を選択して保存します。
最後にデプロイボタンを押下すれば、App Sprict側の設定は終了です。

現状トリガーの設定のイベントの種類は、起動時、編集時、変更時、フォーム送信時の4つがあります。
とくに編集時と変更時の違いは分かりにくいと思います。
スプレッドシート編集時のトリガーは、スプレッドシートでセルの値を変更したり、列や行を追加・削除した場合にスクリプトが実行されます。
スプレッドシート変更時のトリガーは、セルが変更された場合にGASスクリプトが実行されます。
編集時では対象操作であった、列や行の削除・追加といった操作ではトリガー実行が起動しません。
そのため、セルを変更した時だけGASスクリプトを実行したい場合に、編集時ではなく変更時イベントトリガーを利用します。

動作確認

GASの設定は完了したので、実際にBigQueryに読み込む表をスプレッドシートに作成していきます。

BigQueryの指定したデータセットを確認すると、スプレッドシートに作成した表がテーブルとして読み込まれていることがわかります。

更新ボタンの作成（おまけ）

今回作成したGASでは、スプレッドシートの表のセルを1つ更新するたびにBigQueryのテーブルに更新をかけます。
しかし、表に大量のデータを追加したり複数のセルを更新する場合、どこかのセルが更新されるたびにBigQueryのテーブルに更新をかけると非常に効率が悪いです。
なので、表をまとめて更新した後に一括でBigQueryのテーブルに更新をかけるようにするために更新ボタンを作っていきたいと思います。
まずは、先ほど作成したGASのトリガーを削除します。
次に、表の横あたりに更新ボタンを追加します。
今回は、挿入の図形描画から円の図形とテキストボックスを用いて更新ボタンを作成しました。

作成した図形に対してスクリプトを割り当てでmain関数を選択することで、図形をクリックするとmain関数が実行されるようになります。
これで、更新ボタンの作成は終了です。

作成したボタンを押してみると、「スクリプトを実行しています」と表示されGASが実行されました。

まとめ

今回はスプレッドシートの表をBigQueryに取り込むGASを作成していきました。
スプレッドシートで管理しているデータをBigQueryに取り込んでデータ分析をしたい場合は、ぜひとも試してみてください。