データの時間書式をマクロとGASで正しく表記し直す方法

こんにちは！D2C dot 沖縄オフィスで分析業務に携わっています 友利です。
GoogleAnalytics（GA）、タグマネージャー、データポータル周りの実装や整理を担当しています。以前からマクロでのExcel集計効率化に取り組んでおり、最近GoogleAppsScript（GAS）の勉強を始めました。

今回は勉強の一環として行った、GAからデータをExcelで出力した際の「平均ページ滞在時間」を正しい表記に変換するマクロをGAS化したことについてご紹介します。

「平均ページ滞在時間」がおかしい…？

「平均ページ滞在時間」、「平均セッション時間」など、時間に関するデータをGAからExcelで取得すると、このような形式でデータが取得されます。

これは、秒単位でデータが表示されていることが原因です。
データに誤りがあるわけではありませんが、このままでは確認がしづらいため、GA上のデータと同じ見た目（hh:mm:ss）になるように調整を行います。

正しく表記するためのマクロ、GAS

まず、Excel作業用に作ったマクロの内容がこちら。
※マクロ、GASどちらもコードの実行前に変更を加えたい範囲の先頭セル（平均セッション時間列の場合はB2セル、平均ページ滞在時間列の場合はC2セル）を選択した状態でコードを実行してください

 Sub GA_時間書式変更_※実行前に対象列の先頭セルを選択()
' アクティブセルの列を取得して変数に格納
   Dim ActiveColumn As Long
   ActiveColumn = ActiveCell.Column
   Debug.Print ActiveColumn
' アクティブセルの行を取得して変数に格納
   Dim ActiveRow As Long
   ActiveRow = ActiveCell.Row
   Debug.Print ActiveRow
' 同様に、データの最終行を取得して変数に格納
   Dim LastRow As Long
   LastRow = Selection.End(xlDown).Row
   Debug.Print LastRow
  
'時間書式変更
   Dim ActivRange As Range
   For Each ActivRange In Range(Cells(ActiveRow, ActiveColumn), Cells(LastRow, ActiveColumn))
       ActivRange.Value = ActivRange.Value / 86400
       ActivRange.NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
   Next ActivRange
End Sub

今回は、このマクロの内容をGoogleスプレッドシートでも利用できるよう、GASで書き直しました。

書き直したGASがこちら

function myFunction() {
 //アクティブシートの取得
 let mySheet = SpreadsheetApp.getActiveSheet();
 //アクティブセルの取得
 let myActiveCell = mySheet.getActiveCell();
 //アクティブセルから行と列を取得
 let selectedRow = myActiveCell.getRow();
 Logger.log(selectedRow);
 let selectedColumn = myActiveCell.getColumn();
　Logger.log(selectedColumn);
 //最終行を取得
 let lastRow = mySheet.getDataRange().getLastRow();
 Logger.log(lastRow);
   　//変更範囲をmyRange、値をmyValuesに格納
 　let myRange = mySheet.getRange(2,selectedColumn,lastRow-1);
   let myValues = myRange.getValues();
 　//ログでチェック
   Logger.log(myValues);
 //繰り返し処理の変数iの宣言（2行目から最終行まで1回ずつ繰り返し）、単位変更のための計算
 for(let i=0; i< lastRow-1; i++){
   myValues[i][0]=myValues[i][0]/86400;
 }
   //変換した内容を貼り付けて、書式変更
    myRange.setValues(myValues); 
 　 myRange.setNumberFormat('H:mm:ss');　
 
}

マクロとGASの違い

どちらのコードも、実行している内容に大きな差はありませんが、マクロの内容をGAS化する際に注意した点を2つご紹介します。

①データの取得は一回でまとめて行う
マクロの場合、セル1つ1つに対して値の取得と処理を繰り返していましたが、GASは、対象範囲の値をまとめて取得し、その後に処理を実行しています。

1.セルの内容をまとめて配列に読み込み
2.読み込んだ配列を加工
3.加工した配列をまとめてセルに書き込む
という処理を行うことによりスプレッドシートへのアクセス回数を減らし、処理速度を上げています。

②for文の中身は極力少なくする
マクロの場合は、繰り返したい処理をすべてfor文の中に入れていましたが、GASに書き直す際は処理速度を上げるために、取得した配列に対して割り算を行う処理のみをfor文に入れ、その他はfor文の外に出しています。

①②どちらもGASの処理速度を上げることに繋がるコツであり、高速化については、GASのコードレビューを東京オフィスエンジニア金坂 茜さん、沖縄オフィスエンジニア川満 紫帆さんにご協力いただきました。ありがとうございます！

まとめ

どちらも処理の結果は変わらないものの、それぞれ記述方法や気をつける点には違いがありました。
特にGASについては実行時間に6分という制限があり、今回紹介したような高速化が必要となります。Excelでマクロを組んだ内容について、今後もこういった点に配慮しながらGAS化を進めていきたいと思っています。

おわりに

D2C dotでは、沖縄オフィスで一緒に働くメンバーを募集しています。募集中の職種は下記リンクからご確認いただけます。どんな企業か少しでも興味を持っていただけましたら、カジュアル面談でざっくばらんにお話ししてみませんか？みなさまからのご連絡をお待ちしています。

▼ディレクター

▼アシスタント（アルバイト）