Help us understand the problem. What is going on with this article?

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

More than 1 year has passed since last update.

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 から]
ExcelUI

"Web から" ダイアログ

ダイアログ Web から

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

接続の登録

認証中のユーザー、ここでは自分のユーザー情報をGET。
レコードビュー

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

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

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

試しながら

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

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

Web からダイアログ

取得した 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 QueryWeb.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

パラメーターを作成

AccessToken

QiitaAPIHost

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

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)になってる。
途中経過1

たけど、page_views_count が取得できていない。
途中経過2

なので、投稿の 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

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

残り調整して終了

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

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

プライバシー レベル

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

プライバシーレベル ダイアログ

その他

PowerBIxyz
#PowerBI とか #PowerQuery とか Microsoft MVP for Data Platform (2016,2017-2018,2018-2019,2019-2020,2020-2021), Access (2011-2015)
https://powerbi.connpass.com/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away