📝業務改善ツールを作ろう！実践編！
こんにちは！😊
今回は Excel VBAからGA4 Data APIを利用してデータを取得するツール を作成します。
作成に至った経緯は↓の記事をご確認ください！
※簡単に言うと、デジタル初心者でも作れて使えるお手軽ツールを作りたいという話です。
📚 目次
- 📝 業務改善ツールを作ろう！実践編！
- 💻 開発環境
- 🗺️ 全体の流れ
- ☁️ Google Cloudの設定
- 📂 Excel VBAの準備
- 📄 JSONファイルを読み込む
- 🔍 JSONを解析する
- 🪪 JWT作成の準備
- ℹ️ 今後の流れ
- 🎉 まとめ
最終的には以下のようなツールを作成する予定です。
LPのURL・掲載期間を入力
↓
GA4 Data APIからデータ取得
↓
Excelへ自動入力
↓
AIで考察コメントを作成
今回はその第一歩として、
- Google Cloudの設定
- サービスアカウント作成
- JSONキー取得
- VBAでJSONファイルを読み込む
- JWT作成準備
までを行います。
💻開発環境
- Windows11
- Microsoft Excel 365
- Excel VBA
- Google Cloud Console
- Google Analytics 4（GA4）
🗺️全体の流れ
今回作成するツールの流れです。
Google Cloud
↓
Analytics Data API有効化
↓
サービスアカウント作成
↓
JSONキー取得
↓
GA4へ権限付与
↓
Excel VBA
↓
JSONファイル読込
↓
JWT作成準備
☁️Google Cloudの設定
Google Cloud Consoleへアクセスし、新しいプロジェクトを作成します。
プロジェクト名は任意ですが、今回は
GA4_Report
としました。
📊Analytics Data APIを有効化
左上メニューから
APIとサービス
↓
ライブラリ
を開き、
Google Analytics Data API
を検索して 「有効にする」 をクリックします。
👤サービスアカウントを作成
左上メニューから
IAMと管理
↓
サービスアカウント
を開き、
＋ サービスアカウントを作成
をクリックします。
名前は例として
GA4Excel
としました。
🔑JSONキーをダウンロード
サービスアカウントを開き、
キー
↓
鍵を追加
↓
新しい鍵を作成
をクリックします。
キーの種類は
JSON
を選択して 「作成」 を押します。
JSONファイルが自動でダウンロードされます。
🔐GA4へ権限を付与
GA4の
管理
↓
プロパティのアクセス管理
から、
JSONファイル内の
client_email
へ閲覧権限を付与します。
📂Excel VBAの準備
Visual Basic Editorを開きます。
Alt + F11
続いて
挿入
↓
標準モジュール
を選択します。
⚙️参照設定
ツール
↓
参照設定
から
- Microsoft XML, v6.0
- Microsoft Scripting Runtime
にチェックを付けます。
📦VBA-JSONを導入
GitHubから
JsonConverter.bas
をダウンロードし、
ファイル
↓
ファイルのインポート
から追加します。
📄JSONファイルを読み込む
JSONファイルをデスクトップなど任意の場所へ保存します。
例
C:\Users\ユーザー名\Desktop\GA4\
VBAでは次のコードでJSONファイルを読み込みます。
Sub JSON読込テスト()
Dim fso As Object
Dim ts As Object
Dim JsonText As String
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.OpenTextFile( _
"C:\Users\ユーザー名\Desktop\GA4\ga4-report-xxxxxxxx.json", 1)
JsonText = ts.ReadAll
ts.Close
End Sub
🔍JSONを解析する
続いて、JSON文字列をVBAで扱える形式へ変換します。
Dim Json As Object
Set Json = JsonConverter.ParseJson(JsonText)
📧client_emailを取得
Dim ClientEmail As String
ClientEmail = Json("client_email")
MsgBox ClientEmail
🆔project_idを取得
Dim ProjectID As String
ProjectID = Json("project_id")
MsgBox ProjectID
🔐private_keyを取得
Dim PrivateKey As String
PrivateKey = Json("private_key")
MsgBox Left(PrivateKey, 50)
秘密鍵は非常に長いため、先頭50文字だけ表示して確認します。
🪪JWT作成の準備
Googleへ認証するためにはJWT（JSON Web Token）を作成する必要があります。
まずはJWTで利用する時刻情報を取得します。
Dim IssuedAt As Long
Dim ExpireAt As Long
IssuedAt = DateDiff("s", "1970/1/1 09:00:00", Now)
ExpireAt = IssuedAt + 3600
取得できているか確認するには
MsgBox IssuedAt
または
MsgBox ExpireAt
を実行します。
ℹ️今後の流れ
🎉まとめ
今回は、GA4 Data APIを利用するための準備として、
- Google Cloudプロジェクト作成
- Analytics Data API有効化
- サービスアカウント作成
- JSONキー取得
- GA4への権限付与
- Excel VBAの準備
- JSONファイルの読み込み
-
client_email、project_id、private_keyの取得 - JWT作成準備
までを実装しました。
次回は、JWTを生成し、Google OAuthでアクセストークンを取得して、GA4 Data APIからデータを取得する処理を実装していきます！
誰でも簡単に使えるツールが目標です！
