Qiita にポストした状況(投稿数やPVとか)を集計する用事が最近ありまして、せっかくなので Power Query でやってみたのです。ひとまず Excel で要件を満たしましたが、 Power BI Desktop でも問題なくできるはず。

準備と必要なもの

Power Query を使用できる Excel、もしくは Power BI Desktop
Qiita API v2
- アクセストークン
- スキーマを読んだり

アクセストークン

アクセストークンは、[設定] → [アプリケーション] の"個人用アクセストークン" を取得。
Qiita:Team は使ってないし、読み取りしかしないのでスコープは "read_qiita" のみで。

個人用アクセストークンはどこに？

Bearer トークンなので、リクエストヘッダーに含める必要がある。まずは、Excel のリボンUIでの操作。
[データ]タブ → [データの取得] → [その他のデータソースから] → [Web から]
もしくは、[データ]タブ → [Web から]

"Web から" ダイアログ

"詳細設定"
URL部分：https://qiita.com/api/v2/authenticated_user
HTTP 要求ヘッダーパラメーター：Authorization / Bearer << 取得した個人用アクセストークン >>

接続情報を登録
Bearer トークンで認証するので、接続は "匿名" で。
この"接続"に関する情報はファイルではなくローカルシステムのユーザプロファイルに収まる。なので、繰り返し使用することができ、次回以降のアクセスにも利用される。

認証中のユーザー、ここでは自分のユーザー情報をGET。

Qiita API の個人用アクセストークンをリクエストヘッダー含める方法を確認した。Bearer トークンを使用できる他の API でも同じ。

投稿に関する情報を取得する

ゴールはPV(page_view_count)まで取得すること。

試しながら

認証中ユーザーの投稿リストを取得

少し詳しく確認していく。

"詳細設定"
URL部分：https://qiita.com/api/v2/authenticated_user/items
HTTP 要求ヘッダーパラメーター：Authorization / Bearer << 取得した個人用アクセストークン >>

取得した JSON 形式のデータから投稿のリストをGET

レスポンスヘッダーに Content-Type が含まれていますし、中の人が JSON であることを認識しパースしてくれている状態。ここで投稿に関する情報はそれぞれが record となる。

生成されたコードを見る

let
    Source =
        Json.Document(
            Web.Contents(
                "https://qiita.com/api/v2/authenticated_user/items",
                [
                    Headers=[Authorization="Bearer <<個人用アクセストークン>>"]
                ]
            )
        )
in
    Source

Json.Document Function - Power Query は Web.Contents Function - Power Quey で取得したデータ(binary) を JSON にパース。で、ここで整理しておくのは Web.Contents の引数。

Web.Contents

Web.Contents Function - Power Quey

構文：Web.Contents

Web.Contents(url as text, optional options as nullable record) as binary

引数：options は record で指定するのだけど、そのフィールドに追加したい内容を用意していく感じ。

Headers フィールド
リクエストヘッダーに追加するヘッダーを record で用意
Headers = [ Authorization = "Bearer <<個人用アクセストークン>>" ] の内容で使用された。ほかに追加するヘッダーはフィールドとして追加すればよい

以下、この後使うフィールド
RelativePath フィールド
ベースの URL に追加される text

Query フィールド
URIクエリパラメーターとして追加する内容を record で用意。
中の人がエスケープもしてくれるので便利なことが多い。

では、ガッツリまいります

予め検討すべきポイント

パラメータ
ホストとアクセストークンは繰り返し使うのでパラメーターに

利用制限
認証されているはずなので、1000回 / h まで。
なので、5 sec ごとの繰り返し問い合わせであれば制限の影響を受けない算段。

Function.InvokeAfter Function - Power Quey

構文：Function.InvokeAfter

Function.InvokeAfter(function as function, delay as duration) as any

ページネーション
Web.Contents Function - Power Quey で問い合わせをするけれども、レスポンスヘッダーすべてを取得できない Power Query の仕様なので、Qiita API v2 の Link ヘッダーを解釈する方法が使えない。

"?page=1&per_page=100" のような URI クエリパラメータは使えるので page パラメーターをインクリメントして、結果が 0 件にまで繰り返す方針にする。Do Loop のような使い方。
投稿数(items_count) と per_page で必要な繰り返しを予め計算し、"カスタム列の追加"をしていってもよい。こっちは For Each。

List.Generate Function - Power Quey

構文：List.Generate

List.Generate(
    initial as function,
    condition as function,
    next as function,
    optional selector as nullable function
) as list

パラメーターを作成

途中経過 - すべての投稿を取得まで

let
    // 問い合わせ用 UDF
    GetData = 
        (option as record) as any =>
            Function.InvokeAfter(
                () =>
                Web.Contents(
                        QiitaAPIHost,
                        // options(type record) に option を追加
                        Record.Combine(
                            { [ Headers = [ Authorization = "Bearer " & AccessToken ] ], option }
                        )
                ),
                // 5 sec ごと
                #duration( 0, 0, 0, 5 )
            ),
    // pageをインクリメントしてページごとの問い合わせを繰り返す
    Source =
        List.Generate(
            // initial:record を使い、複数の要素(フィールド)を扱う
            () => [ counter = 0, items = { null } ],
            // condition:item が存在する場合繰り返す
            each List.Count( [items] ) > 0,
            // next:生成されるリストアイテム(record)の定義
            each
            [
                counter = [counter] + 1,
                option =
                    [
                        RelativePath = "authenticated_user/items",
                        Query = [ page = Text.From( counter ) ]
                    ],
                items =
                    Json.Document(
                        GetData( option ) // 追加するフィールドを渡して invoke
                    ) // JSON にパース
            ],
            // selector:生成されたレコードからフィールドを選択
            each [items]
        ),
    // リストを結合(union)して null のリストアイテムを除外
    Posts =
        List.RemoveNulls( 
            List.Combine( Source )
        )
in
    Posts

List.Generate で繰り返し取得したデータは投稿(record) のリスト(list)になってる。

たけど、page_views_count が取得できていない。

なので、投稿の id をもとに取得。
投稿ごとに取得したときは page_views_count とれるようだから。

let
    // 問い合わせ用 UDF
    GetData = 
        (option as record) as any =>
            Function.InvokeAfter(
                () =>
                Web.Contents(
                        QiitaAPIHost,
                        // options(type record) に option を追加
                        Record.Combine(
                            { [ Headers = [ Authorization = "Bearer " & AccessToken ] ], option }
                        )
                ),
                // 5 sec ごと
                #duration( 0, 0, 0, 0 )
            ),
    // pageをインクリメントしてページごとの問い合わせを繰り返す
    Source =
        List.Generate(
            // initial:record を使い、複数の要素(フィールド)を扱う
            () => [ counter = 0, items = { null } ],
            // condition:item が存在する場合繰り返す
            each List.Count( [items] ) > 0,
            // next:生成されるリストアイテム(record)の定義
            each
            [
                counter = [counter] + 1,
                option =
                    [
                        RelativePath = "authenticated_user/items",
                        Query = [ page = Text.From( counter ) ]
                    ],
                items =
                    Json.Document(
                        GetData( option ) // 追加するフィールドを渡して invoke
                    ) // JSON にパース
            ],
            // selector:生成されたレコードからフィールドを選択
            each [items]
        ),
    // リストを結合(union)して null のリストアイテムを除外
    Posts =
        List.RemoveNulls( 
            List.Combine( Source )
        ),
    // レコードのリストをテーブルに変換
    TableFromRecords = Table.FromRecords( Posts ),
    // 投稿の id ごとに問い合わせ、 page_views_count を列として追加
    AddedCorrectPV =
        Table.AddColumn(
            TableFromRecords,
            "page_views_count_correct",
            each
                Json.Document(
                    GetData(
                        [ RelativePath = "items/" & [id] ]
                    )
                )[page_views_count]
        ) 
in
    AddedCorrectPV

こんどは取得できたので、概ね目的は達成。

残り調整して終了

集計する用途もないのでテーブルにしやすいようタグ(tags) は非正規化しておく。

JSON：取得できたタグ(tags)

"tags":[
    {"name":"Excel","versions":[]},
    {"name":"PowerBI","versions":[]},
    {"name":"PowerQuery","versions":[]}
]

Json.Document でパースされた結果はレコード(record) のリスト(list) になる。
タグ(name) だけ抽出して結合(Combine)へ

パースされたtags

{
    [name = "Excel", versions = list],
    [name = "PowerBI", versions = list],
    [name = "PowerQuery", versions = list]
}

非正規化する

// リストアイテムを区切り文字ありで文字列結合
Text.Combine(
    // リストアイテム(record) の [name]フィールドを参照し置換
    List.Transform( _, each [name] ),  
     ","
)

そして、できあがり。

できあがり

let
    // 問い合わせ用 UDF
    GetData = 
        (option as record) as any =>
            Function.InvokeAfter(
                () =>
                Web.Contents(
                        QiitaAPIHost,
                        // options(type record) に option を追加
                        Record.Combine(
                            { [ Headers = [ Authorization = "Bearer " & AccessToken ] ], option }
                        )
                ),
                // 5 sec ごと
                #duration( 0, 0, 0, 5 )
            ),
    // pageをインクリメントしてページごとの問い合わせを繰り返す
    Source =
        List.Generate(
            // initial:record を使い、複数の要素(フィールド)を扱う
            () => [ counter = 0, items = { null } ],
            // condition:item が存在する場合繰り返す
            each List.Count( [items] ) > 0,
            // next:生成されるリストアイテム(record)の定義
            each
            [
                counter = [counter] + 1,
                option =
                    [
                        RelativePath = "authenticated_user/items",
                        Query = [ page = Text.From( counter ) ]
                    ],
                items =
                    Json.Document(
                        GetData( option ) // 追加するフィールドを渡して invoke
                    ) // JSON にパース
            ],
            // selector:生成されたレコードからフィールドを選択
            each [items]
        ),
    // リストを結合(union)して null のリストアイテムを除外
    Posts =
        List.RemoveNulls( 
            List.Combine( Source )
        ),
    // レコードのリストをテーブルに変換
    TableFromRecords = Table.FromRecords( Posts ),
    // 投稿の id ごとに問い合わせ、 page_views_count を列として追加
    AddedCorrectPV =
        Table.AddColumn(
            TableFromRecords,
            "page_views_count_correct",
            each
                Json.Document(
                    GetData(
                        [ RelativePath = "items/" & [id] ]
                    )
                )[page_views_count]
        ),
    // record の リストになっている tags を 非正規化
    DenormalizedTags =
        Table.TransformColumns(
            AddedCorrectPV,
            {
                "tags",
                each Text.Combine( List.Transform( _, each [name] ), "," )
            }
        ),
    // 必要な列のみ
    RemovedOtherColumns = 
        Table.SelectColumns(
            DenormalizedTags,
            { "tags", "title", "page_views_count_correct", "likes_count",
              "comments_count", "created_at", "updated_at", "url" }
        ),
    // データ型の変更
    ChangedType =
        Table.TransformColumnTypes(
            RemovedOtherColumns,
            {
                { "tags", type text }, { "title", type text },
                { "page_views_count_correct", Int64.Type },
                { "likes_count", Int64.Type }, { "comments_count", Int64.Type }, 
                { "created_at", type datetimezone},
                { "updated_at", type datetimezone }, { "url", type text }
            }
        )
in
    ChangedType

タグの非正規化も問題なしで完了

プライバシーレベル

異なるデータソースからデータを取得するクエリを結合しようとするとプライバシーレベルを設定しなさいとなる。
まぁ、設定してください。完全に不要なデータであれば "チェックを無視" すればよい。
この投稿の例であれば "パブリック" が妥当で、Qiita 以外のデータソースは介在しないので "無視" でも。

Excel で Qiita 投稿一覧を取得する