Google Analyticsのプロパティ・ビューをExcelで一覧にまとめる

多数あるGoogle Analyticsのプロパティ・ビューをExcelで一覧にまとめる作業を行ったので、そのメモ。

アカウント情報を取得

Google Analytics Management API (v3)のAccount Summaries: listを利用して、アカウントの情報をJSONで出力する。

Java・PHP・Python・JavaScriptのサンプルコードがあるほか、Try It!でそのまま出力できる。

JSONをCSVに変換

JSON形式を何かに変換して、Excelで開けるようにしたいと考えた。すぐ思いついたのは、JSONをCSVに変換すること。これはConvert JSON to CSVですぐにできる。その際、"webProperties"以外の不必要な情報は削ってコンバーターにかけた。

Macの場合、CSVファイルは文字コードをShift_JISに、改行コードをCR+LFに変換しておく。

CSVをExcelで開いて加工

CSVをExcelで開くと、1行=1プロパティでデータが並んでいる。複数ビューがある場合も1行に収まっている。

これだと見にくいので、ビューが縦に並ぶように加工する。手作業でやると手間なので、VBAを使う。

空行を入れる

ビューの情報を移動して縦に並べるために、まずは空行を入れる準備をする。一番左に列を追加し、次の関数を入力する。空行を何行入れると良いかが算出される。セルの終端（AH6）は適宜変更する。

=COUNTA(G6:AH6)/4 - 1

A列に算出した数値を使って空行を入れるVBAは、『セルの数値分だけ行の挿入』（初心者）エクセル Excel [エクセルの学校]に掲載されている以下のコードを利用させて頂いた。（VBAは久しぶりすぎて忘れた。）

Sub cinsert()
    Dim i As Long
    For i = Cells(Rows.count, 1).End(xlUp).Row To 1 Step -1
        With Cells(i, 1)
            If VarType(.Value) = vbDouble And .Value > 0 Then
                If CLng(.Value) = .Value Then
                    Rows(i + 1 & ":" & i + .Value).Insert Shift:=xlDown
                End If
            End If
        End With
    Next
End Sub

ビューのデータを移動する

各プロパティに必要な空行が空いたので、VBAでビューのデータを移動する。これは次のVBAで実現できる。（これは自分で書いた。）9つのビューに対応している。10以上の場合は修正が必要。

Sub moveData()
    Dim startCells As Variant

    ' ビューのデータの始点となる列（アルファベットは使えないので左から数える）
    startCells = Array(11, 15, 19, 23, 27, 31, 35, 39, 43)
    
    For x = 0 To 8
        For i = Cells(Rows.Count, startCells(x)).End(xlUp).Row To 6 Step -1
            If Cells(i, startCells(x)).Value <> "" Then
                Range(Cells(i, startCells(x)), Cells(i, startCells(x) + 3)).Cut Range(Cells(i + (x + 1), 7), Cells(i + (x + 1), 10))
            End If
        Next
    Next
End Sub