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(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 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(
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) は 非正規化しておく。
"tags":[
{"name":"Excel","versions":[]},
{"name":"PowerBI","versions":[]},
{"name":"PowerQuery","versions":[]}
]
Json.Document でパースされた結果は レコード(record) のリスト(list) になる。
タグ(name) だけ抽出して結合(Combine)へ
{
[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 以外のデータソースは介在しないので "無視" でも 。
