Excel Online(Microsoft 365)のデータをtrocco® でBigQueryに転送する

# これはなに

私はprimeNumberというデータエンジニアリングの会社にて、様々な企業のビジネス課題をデータの力で解決するお手伝いをしています。

弊社が提供する分析基盤構築・運用の支援SaaS「trocco®」にて、**転送元HTTP・HTTPSの認可方式としてOAuth 2.0を選択可能に** という機能が昨年リリースされました。

https://documents.trocco.io/docs/release-note-2023-07

なかなか汎用性が高く便利な機能なので、今回は本機能を使ったユースケースを紹介しようと思います。

※本記事内容の再現においてプログラミング知識は不要となっておりますが、OAuth 2.0の一般的な仕様については、概要を理解した上で取り組まれることをおすすめいたします。

# 今回のゴール

MicrosoftのOneDrive上に配置されているExcelファイルのデータを、trocco®を介してBigQueryに転送します。
Microsoft Graph API がOAuth 2.0 の認証方式を取っているため、そちらを使って検証してみます。
![スクリーンショット 2023-09-21 22.19.49.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/ff36a28c-af02-993d-da75-224db14e7a48.png)

# 事前準備

### trocco®アカウントの作成

trocco®には無料で始められるフリープランがあります。

https://trocco.io/lp/index.html

### Microsoft 365 アカウントの作成および認証情報の作成

検証としては個人のアカウントでも問題ないかと思います。
以下の公式リファレンスを参考に、「アプリケーション登録」と「クライアントシークレットの追加」を実施します。

https://learn.microsoft.com/ja-jp/graph/auth-register-app-v2

リダイレクトURIには、[trocco®ヘルプドキュメント](https://documents.trocco.io/docs/connection-configuration-http-https) に記載されているリダイレクトエンドポイントを設定してください。
登録後、以下の情報を控えておきます。

* クライアントID
* クライアントシークレット

### OneDrive上に検証データを配置

OneDrive上に検証用のExcelファイルを作成します。
![スクリーンショット 2023-09-21 22.27.25.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/308d9a45-d7f6-cc99-0789-2308c266e660.png)

### データウェアハウス環境の準備
データを格納する箱として、データウェアハウス(DWH)を用意します。
今回はGoogle CloudのDWH製品であるBigQueryを利用します。BigQueryは毎月1TBのスキャンまで無料(※記事執筆時点)ですので、気軽に検証利用ができます。

https://cloud.google.com/bigquery/pricing?hl=ja

# 実装

### OAuth 2.0の接続情報をtrocco®上に登録する

troccoの画面で「接続情報」→「新規作成」→「HTTP・HTTPS」を選択します。
![スクリーンショット 2023-09-21 22.46.26.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/6f3183bc-1f57-31fa-feeb-36347e7848ca.png)

フォーム画面に進み、必要情報を入力していきます。
認可URL/アクセストークンURLの黒塗り部分はテナントIDとなります。

![スクリーンショット 2023-09-21 22.54.44.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/152157fc-8987-1f19-791d-119b3d15299e.png)

スコープには `offline_access Files.ReadWrite.All` を指定します。
必須項目を埋めて認証を実行すると、ポップアップが出てきますので内容を確認して承認します。

![スクリーンショット 2023-09-21 22.36.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/dc1b800d-2e46-1b9b-37f2-f769045dbc75.png)

認証が成功したら、接続情報を保存します。

### 転送設定を作成する

転送設定の新規作成画面で、転送元「HTTP・HTTPS」と転送先「Google BigQuery」を選択します。

![スクリーンショット 2023-09-21 23.54.22.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/1c304063-09d3-05e4-dc8d-93da35e75b4b.png)

ドキュメントを参照しつつ、各項目を埋めていきます。

![スクリーンショット 2023-09-21 23.56.24.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/f509f169-3d46-6625-2fb9-02e57007ec56.png)

「接続情報」では、前項で登録したものをプルダウンから選択します。
URLは以下を指定し、{drive-item-id} の部分を取得対象ファイルのID文字列で置き換えます。

```
https://graph.microsoft.com/v1.0/me/drive/items/{drive-item-id}/workbook/worksheets('Sheet1')/usedRange
```

drive-item-id の確認方法については、以下の記事にわかりやすく記載されておりましたので参考にさせていただきました。

https://techceed-inc.com/engineer_blog/8194/

今回はメタデータも含めてすべての取得データを保存しようと思いますので、JSONPathの項目に `$` と入力します。

![スクリーンショット 2023-09-21 23.57.23.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/bd53623e-22de-cdff-e3fd-e522b1ff5826.png)

次のプレビュー画面にてデータを確認します。
![スクリーンショット 2024-02-27 16.11.42.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/0c65f9f5-83d7-2fab-dd7f-5260a128a229.png)
valuesというカラムにjson文字列が入っていると思います。
BigQuery上でjsonをパースする想定のため、データはここの時点では1行にまとまっている状態で問題ありません。

設定を保存後、「実行」ボタンからデータを転送します。
![スクリーンショット 2024-02-27 18.40.12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/790f2498-56ac-cf47-a411-7bc3a322eb9b.png)

転送成功していることを確認できました。

### データ変換

BQ内のデータ(values)は以下のような形で格納されています。

```javascript
[["test_column_1","test_column_2"],["aaa","bbb"],["ccc","ddd"]]
```

このままでは集計や分析に使えないため、データを変換して別テーブルに保存します。
カラム名の配列を削除し、その他の配列を各行に分割します。

```sql
SELECT
  JSON_VALUE(unnested,'$[0]') AS test_column_1,
  JSON_VALUE(unnested,'$[1]') AS test_column_2
FROM
  `<project>.<dataset>.<table>`,
  UNNEST(
    JSON_QUERY_ARRAY(
      JSON_REMOVE(PARSE_JSON(values),'$[0]'),'$')) AS unnested
```

上記クエリの結果は以下のようになります。
こちらをCTASやINSERTにてデータをテーブルに投入することで、分析SQLなどで参照できるようになります。
![スクリーンショット 2024-02-27 18.45.01.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/3511608/e34ff146-f100-f154-680d-0ffc7d7d666e.png)

今回は説明を省略しますが、trocco®のワークフロー機能を活用することで、「データ取得/転送」→「データ変換」までの一連の流れを繋げて管理することも可能です。

https://trocco.io/lp/function/workflow.html

# まとめ

以上で、OneDrive上のExcelデータをBigQuery上の分析テーブルとして保存することができました。
OAuth2.0の認証設定を画面から行うことで、プログラミング実行環境を用意せずともAPI経由でデータを取得することが可能となります。
一度DWHにデータを取り込めば、SQL次第で非常に柔軟にデータ変換を実施できますので、今回試したMicrosoft 365以外にも様々なAPIを試してみたいと思います。


# リファレンス

再掲となりますが、Microsoft Graph APIの使い方については、以下の記事を大いに参考にさせていただきました。

https://techceed-inc.com/engineer_blog/8194/