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

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

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

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

## 個人用アクセストークンはどこに？
Bearer トークン なので、リクエスト ヘッダーに含める必要がある。まずは、Excel のリボンUIでの操作。
[データ]タブ → [データの取得] → [その他のデータ ソースから] → [Web から] 
もしくは、[データ]タブ → [Web から]
![ExcelUI](https://qiita-image-store.s3.amazonaws.com/0/193509/6de8a288-5c76-f1f8-259a-6bfe312c6fcc.png)

**"Web から" ダイアログ**

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

![ダイアログ Web から](https://qiita-image-store.s3.amazonaws.com/0/193509/f324a7f5-0c6c-bb6d-4451-6a01f98ed688.png)

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

![接続の登録](https://qiita-image-store.s3.amazonaws.com/0/193509/6944db84-0c41-76f3-e131-a8654349367a.png)

認証中のユーザー、ここでは自分のユーザー情報をGET。
![レコードビュー](https://qiita-image-store.s3.amazonaws.com/0/193509/ac5b7cc7-93da-820d-4b04-69b13a5fdf95.png)

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

# 投稿に関する情報を取得する
ゴールはPV(page_view_count)まで取得すること。
## 試しながら
### 認証中ユーザーの投稿リストを取得
少し詳しく確認していく。

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

![Web からダイアログ](https://qiita-image-store.s3.amazonaws.com/0/193509/d8dfbcbd-18d2-2f24-e138-5ebfb0945e83.png)


取得した JSON 形式のデータから投稿のリストをGET
![リストビュー](https://qiita-image-store.s3.amazonaws.com/0/193509/f584438b-ac5c-42ab-56e8-2af4a1b96c60.png)

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

### 生成されたコードを見る

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

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

### Web.Contents
[**Web.Contents Function - Power Quey**][Web.Contents]

```text:構文：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]

```text:構文：Function.InvokeAfter
Function.InvokeAfter(function as function, delay as duration) as any
```

**ページネーション**
[Web.Contents Function - Power Quey][Web.Contents] で 問い合わせをするけれども、レスポンス ヘッダーすべてを取得できない 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]

```text:構文：List.Generate
List.Generate(
    initial as function,
    condition as function,
    next as function,
    optional selector as nullable function
) as list
```


### パラメーターを作成
![AccessToken](https://qiita-image-store.s3.amazonaws.com/0/193509/77c0eb45-71d0-ab29-27fc-c6ffdbbaf945.png)

![QiitaAPIHost](https://qiita-image-store.s3.amazonaws.com/0/193509/9f96004f-6472-c5ac-4bfa-d44b202dae7c.png)

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

```text
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](https://qiita-image-store.s3.amazonaws.com/0/193509/f3135c8d-a5ca-4c4b-cc0a-30827843424f.png)

たけど、page_views_count が取得できていない。
![途中経過2](https://qiita-image-store.s3.amazonaws.com/0/193509/0c03bb18-7bba-b282-fd6d-b09632f93585.png)

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

```text
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](https://qiita-image-store.s3.amazonaws.com/0/193509/3af03776-c73d-e1cf-dd6a-21d8d0710c9e.png)

### 残り調整して終了
集計する用途もないのでテーブルにしやすいようタグ(tags) は 非正規化しておく。
![tags](https://qiita-image-store.s3.amazonaws.com/0/193509/7f974f17-778e-1a6c-425f-ddd4ecb5ce15.png)

```json:JSON：取得できたタグ(tags)
"tags":[
    {"name":"Excel","versions":[]},
    {"name":"PowerBI","versions":[]},
    {"name":"PowerQuery","versions":[]}
]
```
Json.Document でパースされた結果は レコード(record) のリスト(list) になる。
タグ(name) だけ抽出して結合(Combine)へ

```text:パースされたtags
{
    [name = "Excel", versions = list],
    [name = "PowerBI", versions = list],
    [name = "PowerQuery", versions = list]
}
```

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

そして、できあがり。

```text:できあがり
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](https://qiita-image-store.s3.amazonaws.com/0/193509/f96025e4-24bd-b2e7-cc3f-2c372792d819.png)


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

![プライバシーレベル ダイアログ](https://qiita-image-store.s3.amazonaws.com/0/193509/a164f81a-c116-5a69-1382-d6f1a75344b3.png)

# その他
- [Power BI 勉強会 - connpass](https://powerbi.connpass.com/)
- [Power BI Desktop を使い始めるには](http://qiita.com/PowerBIxyz/items/6c4735cb5b5712e70981)
- [Excel で Power Query を使い始めるには](http://qiita.com/PowerBIxyz/items/52e563037b855a099ca9)


[Web.Contents]:https://msdn.microsoft.com/en-us/library/mt260892.aspx
[Json.Document]:https://msdn.microsoft.com/en-us/library/mt260861.aspx
[Function.InvokeAfter]:https://msdn.microsoft.com/en-us/library/mt637158.aspx
[List.Generate]:https://msdn.microsoft.com/en-us/library/mt260751.aspx