1
/
5

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

GoogleAnalytics(GA)からデータをExcelで出力した際の「平均ページ滞在時間」の表記がおかしいことはありませんか?
正しい表記に変換するマクロを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では、沖縄オフィスで一緒に働くメンバーを募集しています。募集中の職種は下記リンクからご確認いただけます。どんな企業か少しでも興味を持っていただけましたら、カジュアル面談でざっくばらんにお話ししてみませんか?みなさまからのご連絡をお待ちしています。

CONTACT US | D2C dot
■ 事業者の名称 株式会社D2C dot ■ 個人情報保護管理者 株式会社D2C dot 情報セキュリティ担当役員 ■ 個人情報の利用目的 お預かりした個人情報は、当社へお問い合わせいただいた内容へのご回答に利用します。 ■ お預かりする個人情報の項目 本手続きでは、以下の項目をフォームにご入力いただきます。 お問い合わせの種類、会社名、お名前、電話番号、メールアドレス なお、電話番号については電子メールでのご連絡が取れない時に連絡先として利用します。 ■ 本人が容易に認識できない方法による個人情報の取得
https://www.d2cdot.co.jp/contact/?utm_source=wantedly&utm_medium=site&utm_campaign=wty_210930_1
株式会社D2Cdotでは一緒に働く仲間を募集しています
2 いいね!
2 いいね!
同じタグの記事
今週のランキング