Googleドライブ上のスプレッドシートのデータをBigQueryに連携する with GoogleAppsScript

# まえがき

この記事は[株式会社ビットキー 情シス Advent Calendar 2023](https://qiita.com/advent-calendar/2023/bitkey-corp-eng) の20日目の投稿です。

はじめまして。株式会社ビットキーのshiba_no_sukeです。

SaaSサービス利用がメインのビットキー情シスの世界に3か月前飛び込んできましたが、前職ではスクラッチで社内Webアプリなどを作る機会の方が多い情シス担当者でした。

今回、BigQueryについてお話ししますが、アプリ屋さんだった筆者の前提スキルをお伝えしておきます。

- BigQuery、触ったことありません
- SQL Serverは利用していたので、クエリは一応書けます
- Javascriptは、まあまあ書けます

こんなレベル感の人間でも、何とかなります、という視点でご覧いただければ幸いです。

# 前提・課題

2023年12月現在、ビットキーでは受注・契約・請求などのデータを、Googleドライブで保管している、Googleスプレッドシート（GSS）で入力・管理しています。

これらのデータをGSS上で計算・加工して、管理会計の分析に利活用しています。

しかし、当然のことながら、元となるデータは日に日に増えていくため、GSSの計算処理は徐々に遅くなりつつあります。

また、GSSに埋め込まれた計算ロジックが、正しい範囲に適用されているよね…？この数字の計算って合ってるよね…？と、分析結果の確からしさに疑問が生まれることも出てきました。

そこで、速く・正確に、管理会計の分析をするために

- GSSはデータ入力のインターフェイスとして専念させて、
- BigQueryに複雑な計算処理をがんばってもらい、
- Looker Studioで自由に見たい形の管理会計レポートを表現しよう！

と、Googleが提供する各種サービスを活用して改善しよう、いうことになりました。

この記事で、改善活動のすべてをお伝えすると、長ーったらしくなってしまうので、今回はBigQuery環境構築編と題して、BigQueryにGSSのデータをインポートするまで、をお伝えしようと思います。

# 概要

絵心がなさすぎですが、GSSデータをBigQueryに連携する、今回の簡単な構成イメージです。

![Untitled.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/31aca881-15ec-c73c-7fe1-bb3d681cbda9.png)

- Googleドライブ上のGSSデータを、BigQueryの外部テーブル（一時テーブル）を経由して、BigQueryの標準テーブルに連携するのが、本記事のゴールです。
- 一時テーブルを使用して、Googleドライブのデータにクエリを実行する方法はいくつかありますが、Google Apps Script（GAS）でGoogle BigQuery APIを利用できるため、筆者は「BigQuery APIでjobs.insertを呼び出す」という方法を選択しました。
    - 参考にしたGoogle公式ドキュメントをいくつか載せておきます
        - [BigQueryテーブルの概要](https://cloud.google.com/bigquery/docs/tables-intro?hl=ja)
        - [Googleドライブの外部テーブルを作成する](https://cloud.google.com/bigquery/docs/external-data-drive?hl=ja#bq)

# ハンズオン

### GSSデータを準備する

この記事では、GSSの2つのシートにあるデータを、BigQuery上の2つのテーブルに連携する、というシチュエーションでお伝えします。

- GSSデータ
    - sheet1
    
    <img width="550" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/76a367f8-ffd8-505d-3b6e-ff3febc4622f.png">

    - sheet2
    
    <img width="400" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/efc561c7-0289-6c4d-9cb7-6dd8bb7988e6.png">

### BigQuery標準テーブルを作成する

標準テーブルの作成方法はたくさんあるので、公式ドキュメントを参照しながら、お好きな方法で作成してください。
（ここはさほど難しくないので、詳細な手順は割愛します。）

- [BigQueryテーブルを作成する](https://cloud.google.com/bigquery/docs/tables?hl=ja#create-table)

作成後の標準テーブルは、以下のイメージです。

- table_A（sheet1の連携先テーブル）

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/cc718eca-5b87-b84f-8846-07a384a7f2c2.png">

- table_B（sheet2の連携先テーブル）

<img width="600" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/f1238fd4-7831-844b-c431-b05a45cb1f33.png">

### GSS上にGAS開発環境を作成し、BigQuery APIを呼べるようにする

次に、BigQuery APIを呼び出すためのGAS環境を作成します。

①GSSのメニュー：拡張機能 > Apps Scriptを選択して、新規のGASプロジェクトを作成します。

②サービスの追加を選択して、BigQuery APIを追加します。

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/d5769e39-7fbd-aba9-e108-e795071f3cc2.png">

③サービスの下にBigQueryが表示されていればOKです。

<img width="350" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/6bb5ac98-b12e-3884-fa7b-6ff101d28df8.png">

### 外部テーブル（一時テーブル）用のjsonスキーマ 兼 BigQueryのジョブ定義を作成する

次に、GSSとBigQueryの標準テーブルをつなぐ、外部テーブルの定義を構成するjsonスキーマでもあり、BigQueryのジョブ定義でもある、スクリプトファイルを作成します。

- [一時テーブルを使用してドライブのデータをクエリする](https://cloud.google.com/bigquery/docs/query-drive-data?hl=ja#temporary-tables)

①GAS開発環境のメニュー：ファイルを追加 > スクリプトを選択します。

②以下のスクリプトを記入します。
（テーブル定義やBigQueryの環境変数は、適宜変更してください。）

```jsx
const tableA_job = {
    "configuration": {
      "query": {
        //一時テーブルtemp_Aのデータを、BigQuery標準テーブルtable_Aに全件INSERTするというクエリ
        "query": "INSERT INTO dataset.table_A SELECT * FROM temp_A",
        "tableDefinitions": {
          "temp_A": {   //一時テーブルの名前をtemp_Aとしておく
            "autodetect": false,
            "sourceFormat": "GOOGLE_SHEETS",
            "sourceUris": [
              //データ連携対象のGSSのURLのSpreadsheetIDまでをセットする
              "https://docs.google.com/spreadsheets/d/***************************************"
            ],
            "googleSheetsOptions": {
              "range": "sheet1!A:E",  //GSSの連携対象のシートと範囲を指定する
              "skipLeadingRows": 1  //シートの連携対象からスキップしたい行数を指定する
            },
            "schema": {
              "fields": [
                {
                  "name": "a",
                  "type": "STRING"
                },
                {
                  "name": "b",
                  "type": "STRING"
                },
                {
                  "name": "c",
                  "type": "INTEGER"
                },
                {
                  "name": "d",
                  "type": "DATE"
                },
                {
                  "name": "e",
                  "type": "STRING"
                }
              ]
            }
          }
        },
        "useLegacySql": false
      },
      //ジョブのタイムアウト設定（ミリ秒）
      "jobTimeoutMs": 300000,
      //大量データの連携を検証するときは、dryRunをtrueにするようにしましょう！
      "dryRun":false
    },
    "jobReference": {
      //ロケーションを明示的に指定する
      "location": "your-location"
    }
  };
```

Sheet2のデータをtable_Bに連携するためのスクリプトも、同様に作成しています。

テーブルのカラム数が多い場合は、BigQueryのテーブルスキーマをJSON形式で取得すると便利です。

以下のサイトが大変参考になりました。

[BigQueryのテーブルスキーマをJSON形式で取得する方法](https://blog.since2020.jp/data_analysis/bigquery_table_schema/)

### BigQueryにデータをインポートするジョブを作成する実行文をGASで書く

最後に、Apps Scriptの公式ドキュメントに掲載されているサンプルをもとに、実行文を書いてみます。

- [Apps ScriptのBigQueryサービス](https://developers.google.com/apps-script/advanced/bigquery?hl=ja)

①GAS開発環境のメニュー：ファイルを追加 > スクリプトを選択します。

②以下のスクリプトを記入します。
（BigQueryのプロジェクトIDなどは、別のスクリプトファイルで定数として定義しています。）

```jsx
function insertJobs() {
  //スプレッドシートが置かれているGoogle DriveのフォルダのIDを指定する
  //フォルダID=ブラウザ上でドライブを開き、対象のフォルダにアクセスしたときのURLから確認できる
  //「https://drive.google.com/drive/folders/〇〇〇〇」の〇〇〇〇部分
  const folderId = GDRIVE_FOLDER_ID;
  DriveApp.getFolderById(folderId);

  //BigQueryのプロジェクトIDを指定する
  const projectId = BIGQUERY_PROJECT_ID;
  //BigQueryのロケーションを指定する
  const location = BIGQUERY_DATASET_LOCATION;

  //ジョブの定義
  const a_job = tableA_job;
  const b_job = tableB_job;

  try {
    //2テーブル分同時にジョブを仕込む
    const tableAJobInsert = BigQuery.Jobs.insert(a_job, projectId);
    const tableBJobInsert = BigQuery.Jobs.insert(b_job, projectId);

    //2つのジョブが完了するまでの間、5秒待ってジョブの結果を取りに行く、を繰り返す
    while (
      BigQuery.Jobs.get(projectId, tableAJobInsert.jobReference.jobId, { "location": location }).status.state != "DONE"
      || BigQuery.Jobs.get(projectId, tableBJobInsert.jobReference.jobId, { "location": location }).status.state != "DONE"
    ) Utilities.sleep(5000);

    //各ジョブの結果を取得する
    const tableAJobResult = BigQuery.Jobs.get(projectId, tableAJobInsert.jobReference.jobId, { "location": location });
    //table_AのデータINSERTに失敗したら、ジョブのエラー結果をキャッチする
    if (tableAJobResult.status.errorResult != null) {
      console.log('table_A:Job insertion failed.');
      console.log('error code:' + tableAJobResult.status.errorResult.reason);
      console.log('error message:' + tableAJobResult.status.errorResult.message);
    } else {
      //ジョブが成功したとき
      console.log('table_A:Job insertion succeeded!');
    }

    const tableBJobResult = BigQuery.Jobs.get(projectId, tableBJobInsert.jobReference.jobId, { "location": location });
    //table_BのデータINSERTに失敗したら、ジョブのエラー結果をキャッチする
    if (tableBJobResult.status.errorResult != null) {
      console.log('table_B:Job insertion failed.');
      console.log('error code:' + tableBJobResult.status.errorResult.reason);
      console.log('error message:' + tableBJobResult.status.errorResult.message);
    } else {
      //ジョブが成功したとき
      console.log('table_B:Job insertion succeeded!');
    }
  //スクリプトの中でエラーが発生したら、エラー結果をキャッチする
  } catch (err) {
    console.log('An error occurred in executing the script.');
    console.log(err);
  }
}
```

### 実行してみよう！

試しに、GAS開発環境で実行してみます。

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/d9647a37-1b73-f79d-c8d9-3a8f2e42b1ed.png">

table_Aもtable_Bも、成功という結果が返ってきました！！

本当に成功したのか、BigQueryのジョブの結果も見てみましょう。

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/74a33ffe-4725-029a-979b-f3e27de4c7c5.png">

同じ時間にtable_Aとtable_Bへのデータ連携ジョブが作成され、どちらも成功しています！

では、最後にテーブルの結果も見てみましょう。

- table_A

<img width="660" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/2d7fed38-1d19-1a35-009a-3b4c636958d0.png">

- table_B

<img width="520" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/cc9fed0a-5a14-44a6-3278-2227e9b461c6.png">

どちらのテーブルにも、GSSに準備したデータが全件登録されていることが確認できました！

### 大量レコードを処理してみよう！

上記の方法を使って、同じGSS内で、以下の大量レコードを持つ2シート分のデータを処理してみます。

- table_C
    - 列：A~Z（26カラム）✕ 30,000レコード
    - A列はINTEGER型で行番号を振り、B列はSTRING型の列に、C列以降は適当に計算させたINTEGER型の列になっています。
    
    <img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/50007421-6470-5653-a5bf-f1a603192b50.png">

- table_D
    - 列：A~Z（26カラム）✕ 50,000レコード
    - table_Cと同じ構成で、20,000レコード増やしました。

データとプログラムを準備して、GASを実行してみました。

<img width="650" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/110beb7d-4d2e-4713-ae75-edc1107ff651.png">

なんと、30秒で成功という結果が返ってきました！

ホントかよ…と、ちょっと疑いたくなるぐらいですね…

ということで、ジョブとテーブルの結果も確認してみましょう。

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/7db9ac15-78fc-ff3a-f00a-5ec4dd69761b.png">

- table_C

<img width="480" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/e75fd31a-9a26-8f88-1374-06ca6e3882d5.png">

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/d0e0d826-bc33-e0b2-46de-00d2a8915c88.png">

- table_D

<img width="480" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/9be91b8c-e65b-31f8-7e62-64a4837d13e9.png">

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/bab6a97e-e4fb-f475-37e6-7ce1a1ea4506.png">

30,000レコードのtable_Cは17秒で、50,000レコードのtable_Dは26秒で、データが連携されていることが確認できました！

BigQuery上なら、大量データの計算も加工も、とても速く実行することができます。

例えば、「table_Cとtable_DをA列をキーにINNER JOINし、（簡単な計算式ではあるけれど…）各列を計算させて30,000レコードSELECTする」といった以下のクエリの実行速度は、たったの1秒です！

```sql
SELECT
  c.A AS rowNo,
  c.B AS title,
  c.C + d.C AS C,
  c.D - d.C AS D,
  SAFE_MULTIPLY(c.E, d.E) AS E,
  c.F / d.F AS F,
  MOD(c.G, d.G) AS G,
  SAFE_MULTIPLY(c.H, d.H) AS H,
  c.I + d.I AS I,
  c.J - d.J AS J,
  SAFE_MULTIPLY(c.K, d.K) AS K,
  c.L / d.L AS L,
  MOD(c.M, d.M) AS M,
  SAFE_MULTIPLY(c.N, d.N) AS N,
  c.O + d.O AS O,
  c.P - d.P AS P,
  SAFE_MULTIPLY(c.Q, d.Q) AS Q,
  c.R / d.R AS R,
  MOD(c.S, d.S) AS S,
  SAFE_MULTIPLY(c.T, d.T) AS T,
  c.U + d.U AS U,
  c.V - d.V AS V,
  SAFE_MULTIPLY(c.W, d.W) AS W,
  c.X / d.X AS X,
  MOD(c.Y, d.Y) AS Y,
  SAFE_MULTIPLY(c.Z, d.Z) AS Z
FROM
  `dataset.table_C` AS c
INNER JOIN
  `dataset.table_D` AS d
ON
  c.A = d.A
ORDER BY
  rowNo
```

<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/a8b6751c-8c12-8e60-e417-1dfd9f7f5b43.png">

上記のクエリと同じ結果が得られるよう、GSS上で計算させてみたところ、30,000レコード分の全体の計算結果が返ってくるまで、約7秒かかりました。
（以下のような感じで、各列に計算式を入れています。）
<img width="700" src="https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3631798/bd81e655-ba0d-a9c5-5c82-fa45d3e049d7.png">

もちろん、結果を単純比較することはできませんが、BigQueryが"分析用データベース"というだけのことはあるようです。

:::note warn
[12/8の記事](https://qiita.com/i_am_miko/items/8920220fbeca22f76e72#step2bigquery%E3%81%A7%E3%83%87%E3%83%BC%E3%82%BF%E3%82%92%E9%9B%86%E8%A8%88%E3%81%97%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B)にも記載されている通り、BigQueryの利用料金は取り扱うデータ量によって変わります。
大量データの連携をいきなり実行する前に、以下の方法を取るようにしましょう。
①少量レコードの連携で、クエリの正確性を事前に確かめる
②[ジョブ定義](#外部テーブル一時テーブル用のjsonスキーマ-兼-bigqueryのジョブ定義を作成する)の"dryRun”を”true”にして、ジョブの作成・実行が成功するかを事前に確かめる
:::

### 余談

GSSの同一ワークブック内のデータ量の限界は、1,000万セルとなっています。（2023/12時点）
26カラムの場合、理論上だと384,615レコード登録できる計算です。
この章の検証をするにあたって、限界までデータを登録するとGSSの挙動が不安定になるだろうと、半分の19万強レコードで検証しようと考えていました。
が、しかし、実際に19万レコードのデータをGSSに準備してみると、計算式を入れている関係もあるのか、GUIが固まってしまいました…（きっと、あるあるですよね！）
GSSの限界セル数は、段階を経て徐々に増えていますが、ゴリゴリ計算が必要なビッグデータを扱うのは難しい部分もあるようです。
改善計画の動機に戻ってしまいますが、データの分析や表示については、やはり、餅は餅屋ということで、BigQueryやLooker Studioに任せた方がよさそうだと感じました。

# メリット/何が嬉しいのか

今回、上記の方法を選択したのには、いくつか理由がありました。

**①データ連携のトリガーを自動化したい**

・・・GASでBigQueryのジョブを作成する、という手段は、データ連携を一定のタイミングで自動的に実行する上で、比較的容易に実装することができると考えたからでした。

また、ジョブの作成を並行して実行できるため、複数のテーブルデータを連携するときに、データの静止点を作ることもできます。

**②データ連携がエラーになったとき、外部でエラー内容をキャッチできるようにしたい**

・・・GASでジョブの結果を取得することで、SlackやGmailでエラー結果を通知する、GSSにエラーログを転記する、など、様々な方法でエラー内容をキャッチすることができます。

# 継続課題（2023/12/20現在）

本記事を書いている時点では、GSS・BigQuery・Googleドライブの各環境に必要な権限が何か、という点を検証している段階であるため、詳細をお伝えすることができませんでした。

検証結果をまとめられたら、改善活動の続きとともに、新たな記事にできたらよいなと思っています。

# 最後に

はじめにお伝えした通り、「BigQuery？触ったことない！」という筆者でしたが、いろいろ調査・検証した結果、こんな方法もありますよー、という1つを、まとめることができたと思っています。

ですが、本記事の方法がベストだ！というわけではありません。

もっとよい方法があるよ、こんな方法もあるよ、という方がいらっしゃいましたら、ぜひ、共有していただけると幸いです！