Edited at

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

"Web から" ダイアログ

接続情報を登録

Bearer トークンで認証するので、接続は "匿名" で。

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

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

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


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

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


試しながら


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

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

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


パラメーターを作成


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

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 以外のデータソースは介在しないので "無視" でも 。


その他