GA4のデータをスプレッドシートへいい感じに自動連携させようと頑張った話

本記事はNTTドコモ R&D Advent Calendar 2022の10日目の記事です。

はじめに
取り組みの経緯
実現したかったこと
GA4のAPIを使ってみる
取得したデータを日付順に並び替える
データを日付ごとにまとめる
データを表形式にまとめる
データをスプレッドシートに書き込む
自動更新の設定を行う
実際に書いたコード

はじめに

はじめまして！
NTTドコモ　サービスデザイン部の上野です。
本記事ではGoogle Apps Script（GAS）を使ってGA4のデータをスプレッドシートへ自動反映した事例を紹介します。
プログラミング初心者なので、コードが読みにくい！もっと効率的な方法がある！といった箇所がいくつもあるかもしれませんが、1つの事例として参考にしていただけたらと思います。
(本記事の最後にコードを掲載しているので、必要であればパラメータをいじってぜひ活用してください。)

取り組みの経緯

社内向けサイトを運営していたときのお話です。
Google Analytics(UA)のデータをアドオンでスプレッドシートに自動連携し、データの可視化や分析を行っていました。
しかしそんなある日、「UAが廃止され、GA4に切り替わる」というニュースが飛び込んできて対応に追われることに…！
こうして「GA4に関する情報が少ない中で、GA4に対応したデータ分析環境を整える」というミッションが始まりました。
（現在はアドオンでスプレッドシートと簡単に連携できる方法があるみたいです。うらやましい…！）

実現したかったこと

実現したかったことは以下の2点です。
・GA4のデータを行がイベント、列が日付の表形式でまとめる
・毎日自動でデータを取得、更新する

完成後のイメージ↓

どうやらGASでAPIを利用するとスプレッドシートにデータが書き込まれるらしく、これなら簡単にできるだろう…とこの段階では思っていました。

GA4のAPIを使ってみる

APIを使えないと何も始まらないので、とりあえずGA4のGoogle Analytics Data APIを使って戻り値を確認してみました。
GA4のAPIをGASで実行する方法については、こちら↓の記事を参考にさせていただきました。
auto-worker.com

実際に使用したコードがこちら↓です。

function getGa4Data() {

//■イベント名
 var event_array = 
 [
  "page_view",
  "session_start",
  "first_visit",
  "user_engagement",
  "scroll",
  "view_search_results",
  "click",
 ];

//■スプレッドシート情報
 var sheet_ID = "ここにスプレッドシートのIDを入力";
 var sheet_name = "ここにデータを書き込みたいシート名を入力";

//■取得期間設定
 var start_Date =  'yyyy-mm-dd';
 var end_Date =  'today;

//■GA4のデータを取得するAPIのURL
 let apiURL = 'https://analyticsdata.googleapis.com/v1beta/properties/**********:runReport';
 let mySheet = SpreadsheetApp.openById(sheet_ID).getSheetByName(sheet_name);

//POSTする際に必要となるディメンションやメトリクス、データ期間を設定
 let setDimension =[ { 'name': 'date' },{ 'name': 'eventName' }];
 let setMetrics = { 'name': 'activeUsers' };
 let setDateRange = { 'startDate': start_Date, 'endDate': end_Date};

//APIのリクエスト時に必要なペイロード値を設定する
 let payload = {
  'dimensions': setDimension,
  'metrics': setMetrics,
  'dateRanges': setDateRange
 };

//HTTPSのPOST時に必要なオプションパラメータを設定する。APIの認証のため、headersの情報も必須になる
 let options = {
  'payload': JSON.stringify(payload),
  'myamethod': 'POST',
  'muteHttpExceptions': true,
  'headers': { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
  'contentType': 'application/json'
 };

//APIリクエストを行った結果のJSONデータを変換する
 let response = UrlFetchApp.fetch(apiURL, options);
 let json = JSON.parse(response);

//スプレッドシートにデータを格納できるように、forループの処理で二次元配列(ga4Data)にjsonの"row"内のデータを格納する
 let ga4Data = [];
 for (let i = 0; i < json["rows"].length; i++) {
  ga4Data[i] = [json["rows"][i]["dimensionValues"][0]["value"], json["rows"][i]["dimensionValues"][1]["value"], json["rows"][i]["metricValues"][0]["value"]];
 }
//スプレッドシートに実行結果を書き込む
 mySheet.getRange(2, 1, ga4Data.length, ga4Data[0].length).setValues(ga4Data);
}

参考のコードに変数を追加して、少し汎用性を持たせました。
そしてこのコードを実行した結果がこちら↓です。

データの数値をお見せすることはできませんが、実行結果を見ると数値が大きい順で上から下に並んでいました。
GA4のデータをスプレッドシートに書き込むことはできたのですが、このままでは可視化や分析がやりにくいのでいい感じにしていきます。

取得したデータを日付順に並び替える

まずはデータを日付順に並び替えていきます。

イメージはこんな感じ↓

データの並び替えはこちら↓を参考にさせていただきました。
xn--eckl3qmbc2cv902cnwa746d81h183l.com

実際に書いたコードがこちら↓です。

//GA4のデータが格納された二次元配列を日付順に並び変える
 ga4Data.sort((a, b) => {return a[0] - b[0];} );

たった1行です。
1行なんですが、初心者にはシンプルすぎて逆に難しいです。
途中式を書いていない数式を見ているような感覚で、引き算っぽい処理がなぜあるのか？ということすら分かりません。

調べてみると、要素の並べ替えに用いられるsortメソッドを利用したコードであることがわかりました。
また、sortメソッドは簡単に書くと下記のルールで機能するため、引き算が重要だということもわかりました。

a - b の引き算を行ったときに、

引き算の結果が 0 未満の場合は、b→aの順番に並び替える
引き算の結果が 0の場合は並び替えを行わない
引き算の結果が 0 より大きい場合は、a→bの順番に並び替える

要素の大小を比較して昇順や降順の並べ替えを実現しているということですね。
ちなみに、実行結果がこちら↓です。

ちゃんと日付順になっています…！
すでにうれしさと達成感がありますが、もう少しきれいにデータを整えていきます。

データを日付ごとにまとめる

先ほど日付順に並べたデータを、今度は日付ごとにまとめていきます。
図で説明するとこんな感じです↓

まずは、日付ごとにまとめたデータを収納する新しい配列（ga4Data_output）と並び替えの過程で必要な配列（add_row、add_array）を定義しておきます。
なぜ新しい配列を定義したかというと、元のデータ（ga4Data）から値を取得してそのデータ内（ga4Data内）で並び替えを行う処理は煩雑になりそうだったからです。
そこで、元のデータ（ga4Data）はそのまま残しておきながら、並び替えたデータを新しい配列（ga4Data_output）に格納するという方法をとりました。

//スプレッドシートへ書き込むための配列(ga4Data_output)を定義する
 let ga4Data_output = [];

//まとめるための処理に用いる配列を定義する
 var add_row = ga4Data[0];
 var add_array=[];

新しい配列が定義できたので、次に以下の処理を行うコードを書きます。

実際に書いたコードが以下になります。

//forループで同日のイベントとイベントのデータを同じ行にまとめる
 for (let j = 0; j < json["rows"].length-1; j++) {

//隣接する2行の日付を比較する。
//同じならイベントとイベントのデータをsliceメソッドでadd_arrayに取り出して、
//add_rowに取り出した値をconcatメソッドで追加する
  if(ga4Data[j].slice(0,1).toString() == ga4Data[j+1].slice(0,1).toString()){
   add_array = ga4Data[j+1].slice(1,3);
   add_row = add_row.concat(add_array);
  
//隣接する2行の日付を比較して異なる場合はこちらの処理を実行
//add_rowにまとめられた同日のイベントとデータをpushメソッドでga4Data_outputに格納し、
//そのあとにadd_arrayとadd_rowを初期化している
  }else{
   ga4Data_output.push(add_row);
   var add_array = []
   var add_row = ga4Data[j+1]
  }
 }
  
//1つ前のforループで最後に生成されたadd_rowはループ内では
//ga4Data_outputに格納されないのでここで格納処理を実施
 ga4Data_output.push(add_row);

長くなりましたが、forループとif文を用いて以下のような処理を行っています。

隣接する2行の日付が同じか異なるか判定
日付が同じならイベント名と数値を"add_array"に格納
"add_array"に格納されたデータを"add_row"に追加
2で日付が異なった場合は"add_row"に格納されたデータを"ga4Data_output"に追加
"add_array"を初期化
"add_row"に次の日付を格納
1に戻る(最後の日付に到達したら終了)

この処理を最後まで行うことでこちら↓のように日付ごとにまとまります。

データを表形式にまとめる

最後に"ga4Data_output"にまとめたデータからイベント名を削除しつつ、特定のイベント順に並び替えていきます。
図で表現するとこんな↓感じです。

実際に書いたコードが以下になります。

//forループで各行のデータを任意の順番に並び替えている
//この処理をしないと表としてデータを出力することができない
 for(let m =0; m < ga4Data_output.length; m++){

//以下のforループで各行内の各イベントのデータを並び替えていく
  for(var k = 0;  k < event_array.length; k++){

//各イベントが何列目にあるか探索して結果を代入する
//配列内の探索については以下のサイトを参照
//参考：https://vba-gas.info/gas-indexof
   var event_num = ga4Data_output[m].indexOf(event_array[k]);

//各イベントのデータ(アクティブユーザー数)を格納する変数を定義する
//初期値は0とする
   var event_data = 0;

//各イベントのデータがあればデータを配列に格納し、
//データがなければ0を代入する処理をイベントごとに行う
//データがない場合は探索結果に"-1"が格納されるので、
//探索結果が"-1"かどうかで処理を分けている
   if(event_num == -1){
    event_data = 0;
   }else{
    event_data = ga4Data_output[m].slice(event_num+1,event_num+2);
   }

//データを任意の順番で配列に格納していく
//データの先頭には日付の情報も入れたいため、
//k=0の場合のみ先頭に日付を格納できるようにif文を用いている
   if(k == 0){
    var change_row = []
    change_row = change_row.concat(ga4Data_output[m].slice(0,1));
    change_row = change_row.concat(event_data);
   }else{
    change_row = change_row.concat(event_data);
   }
  } 

//任意の順番に並び替えられたデータをga4Data_outputに格納する
  ga4Data_output[m] = change_row;
 }

コードはかなり複雑になってしまいましたが、簡単に書くと以下のような処理を行っています。

各イベントの格納場所を探索
各イベントのデータを取得
特定の順番に並び替えた数値データだけを"ga4Data_output"に代入

図で表現すると下記のようになります。
（event_arrayでイベントCが指定されている場合を記載しています。）

そして実行すると、"ga4Data_output"の中身は以下のように目指していた形になります…！

これでデータの準備は整いました！

データをスプレッドシートに書き込む

データが準備できたので、次はスプレッドシートに書き込んでいきます。
スプレッドシートにデータを書き込むためのコードは以下になります。

//書き込むスプレッドシートのクリア(白紙化)
 mySheet.clear();
  
//DATE、イベント名のセルの塗りつぶし
 mySheet.getRange(1, 1, 1, event_array.length+1).setBackground('#7FBFFF')

//スプレッドシートに"DATE"と書き込む
 mySheet.getRange("A1").setValue('DATE');

//スプレッドシートにイベント名を書き込んでいく
//setValuesは2次元配列でしか利用できないため、
//event_arrayを2次元配列にしてから書き込みを行っている
 let event_array_2D = [event_array];
 mySheet.getRange(1, 2, 1, event_array.length).setValues(event_array_2D);

//ga4Data_outputに格納されたデータをスプレッドシートに書き込む
 mySheet.getRange(2, 1, ga4Data_output.length, ga4Data_output[0].length).setValues(ga4Data_output);
}

見た目を整えるために、一部のセルを塗りつぶして表っぽくしています。
実際にスプレッドシートに書き込んだ結果がこちら↓です。

ついにUAのアドオンと同じように、GA4のデータをスプレッドシート上で表示することができるようになりました！

自動更新の設定を行う

最後に仕上げの自動更新設定です。

GASではトリガーという機能を利用して、指定したタイミングで自動的にプログラムを実行することができます。
この設定は簡単で、こちら↓のGASのトリガーページで「トリガーを追加」を押下し、毎日特定の時間に動作するように設定するだけです。

以下のように設定すると毎朝自動でデータが更新されるようになります。

これで無事にGA4のデータがこれまでと同じ形でスプレッドシートに自動的に連携されるようになりました。
少し苦労しましたが初歩的なプログラミングの知識をつける良い経験になったので、今後はもっと発展的なことにも挑戦してみたいです。

実際に書いたコード

ここまでの章で説明したコードをそのまま使えるように1つにまとめました。
APIの設定完了後にスプレッドシートやAPIのパラメータを入力してもらうことで、そのまま動かせるようになっています。
中身を見なくても必要なパラメータを変数に代入してあげることで利用できるようになっているので、ぜひ参考にしていただければと思います。

function getGa4Data() {
  //＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝//
  //　以下に情報を入力することでプログラムが機能します。　　　　　　　//
  //＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝//

  //■イベント名(新しくイベントを追加した場合はこの配列内に同じように追加していく）
  //　(利用したいイベントのみ記載でOK！）
     var event_array = 
     [
     "page_view",
     "session_start",
     "first_visit",
      "user_engagement",
      "scroll",
      "view_search_results",
      "click",
      ];

 //■スプレッドシート情報
    //sheet_IDは書き込みたいスプレッドシートのID
    //(スプレッドシートのURLのd/～/editの間の"～"の部分)
      var sheet_ID = "ここにスプレッドシートのIDを入力";
    //sheet_nameは書き込みたいスプレッドシートにあるシートの名称
    //(ここで指定したシートにデータが書き込まれる)
      var sheet_name = "ここにデータを書き込みたいシート名を入力";

 //■取得期間設定
    //start_Dateはデータを取得したい最初の年月日
      var start_Date =  'yyyy-mm-dd';

    //end_Dateはデータを取得したい最後の年月日
    //(todayにすることで常に今日までのデータを取得できる)
      var end_Date =  'today;

 //■GA4のデータを取得するAPIのURL
    //runReportのAPIリクエストを行うPOSTのURLを定義(数字がGA4のプロパティID)
    //参考:https://auto-worker.com/blog/?p=3250
      let apiURL = 'https://analyticsdata.googleapis.com/v1beta/properties/**********:runReport';

  //＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝//
  //　情報入力ここまで！（これ以降のコードは基本的に変更しない！）　　//
  //＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝//


    //任意のスプレッドシートから出力結果を格納するシートを読み込み
      let mySheet = SpreadsheetApp.openById(sheet_ID).getSheetByName(sheet_name);

    //POSTする際に必要となるディメンションやメトリクス、データ期間を設定
      let setDimension =[ { 'name': 'date' },{ 'name': 'eventName' }];
      let setMetrics = { 'name': 'activeUsers' };
      let setDateRange = { 'startDate': start_Date, 'endDate': end_Date};

    //APIのリクエスト時に必要なペイロード値を設定する
      let payload = {
        'dimensions': setDimension,
        'metrics': setMetrics,
        'dateRanges': setDateRange
       };

    //HTTPSのPOST時に必要なオプションパラメータを設定する。APIの認証のため、headersの情報も必須になる
      let options = {
       'payload': JSON.stringify(payload),
       'myamethod': 'POST',
       'muteHttpExceptions': true,
       'headers': { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
       'contentType': 'application/json'
      };

    //APIリクエストを行った結果のJSONデータを変換する
      let response = UrlFetchApp.fetch(apiURL, options);
      let json = JSON.parse(response);

    //スプレッドシートにデータを格納できるように、forループの処理で二次元配列(ga4Data)にjsonの"row"内のデータを格納する
      let ga4Data = [];
      for (let i = 0; i < json["rows"].length; i++) {
        ga4Data[i] = [json["rows"][i]["dimensionValues"][0]["value"], json["rows"][i]["dimensionValues"][1]["value"], json["rows"][i]["metricValues"][0]["value"]];
       }

    //GA4のデータが格納された二次元配列を日付順に並び変える
    //参考URL↓
    //http://xn--eckl3qmbc2cv902cnwa746d81h183l.com/instructor-blog/200316how-to-sort-an-array-in-google-apps-script/
      ga4Data.sort((a, b) => {return a[0] - b[0];} );

    //これ以降で同日のイベントとイベントのデータ(アクティブユーザー数)を同じ行にまとめていく
    //スプレッドシートへ書き込むための配列(ga4Data_output)を定義する
      let ga4Data_output = [];

    //まとめるための処理に用いる配列を定義する
      var add_row = ga4Data[0];
      var add_array=[];

    //forループで同日のイベントとイベントのデータを同じ行にまとめる
    //concatメソッドやpushメソッドの違い、考え方については以下のサイトを参照
    //参考：https://nujonoa.com/how-to-combine-two-dimensional-arrays/
      for (let j = 0; j < json["rows"].length-1; j++) {

    //隣接する2行の日付を比較する。
    //同じならイベントとイベントのデータをsliceメソッドでadd_arrayに取り出して、
    //add_rowに取り出した値をconcatメソッドで追加する
    //配列の要素の比較については以下のサイトを参照
    //参考：https://marycore.jp/prog/js/array-equal/
      if(ga4Data[j].slice(0,1).toString() == ga4Data[j+1].slice(0,1).toString()){
         add_array = ga4Data[j+1].slice(1,3);
         add_row = add_row.concat(add_array);
  
    //隣接する2行の日付を比較して異なる場合はこちらの処理を実行
    //add_rowにまとめられた同日のイベントとデータをpushメソッドでga4Data_outputに格納し、
    //そのあとにadd_arrayとadd_rowを初期化している
       }else{
          ga4Data_output.push(add_row);
          var add_array = []
          var add_row = ga4Data[j+1]
           }
       }
  
     //1つ前のforループで最後に生成されたadd_rowはループ内では
     //ga4Data_outputに格納されないのでここで格納処理を実施
       ga4Data_output.push(add_row);

     //forループで各行のデータを任意の順番に並び替えている
     //この処理をしないと表としてデータを出力することができない
       for(let m =0; m < ga4Data_output.length; m++){

     //以下のforループで各行内の各イベントのデータを並び替えていく
       for(var k = 0;  k < event_array.length; k++){

     //各イベントが何列目にあるか探索して結果を代入する
     //配列内の探索については以下のサイトを参照
     //参考：https://vba-gas.info/gas-indexof
         var event_num = ga4Data_output[m].indexOf(event_array[k]);

     //各イベントのデータ(アクティブユーザー数)を格納する変数を定義する
     //初期値は0とする
         var event_data = 0;

     //各イベントのデータがあればデータを配列に格納し、
     //データがなければ0を代入する処理をイベントごとに行う
     //データがない場合は探索結果に"-1"が格納されるので、
     //探索結果が"-1"かどうかで処理を分けている
         if(event_num == -1){
             event_data = 0;
          }else{
             event_data = ga4Data_output[m].slice(event_num+1,event_num+2);
          }

      //データを任意の順番で配列に格納していく
      //データの先頭には日付の情報も入れたいため、
      //k=0の場合のみ先頭に日付を格納できるようにif文を用いている
         if(k == 0){
             var change_row = []
             change_row = change_row.concat(ga4Data_output[m].slice(0,1));
             change_row = change_row.concat(event_data);
         }else{
             change_row = change_row.concat(event_data);
             }
         } 

    //任意の順番に並び替えられたデータをga4Data_outputに格納する
        ga4Data_output[m] = change_row;
      }

    //書き込むスプレッドシートのクリア(白紙化)
      mySheet.clear();
  
    //DATE、イベント名のセルの塗りつぶし
      mySheet.getRange(1, 1, 1, event_array.length+1).setBackground('#7FBFFF')

    //スプレッドシートに"DATE"と書き込む
      mySheet.getRange("A1").setValue('DATE');

    //スプレッドシートにイベント名を書き込んでいく
    //setValuesは2次元配列でしか利用できないため、
    //event_arrayを2次元配列にしてから書き込みを行っている
      let event_array_2D = [event_array];
      mySheet.getRange(1, 2, 1, event_array.length).setValues(event_array_2D);

    //ga4Data_outputに格納されたデータをスプレッドシートに書き込む
      mySheet.getRange(2, 1, ga4Data_output.length, ga4Data_output[0].length).setValues(ga4Data_output);
   }