※この記事は、"PBIJP Power Query 秘密特訓「虎の穴」炎の復活編 #14 (2022.05.21)で使用した記事です。
後日再構成して公開する予定なので、タイトルが（仮）になってます。
Web.Contents構文
Web.Contents(
url as text,
optional options as nullable record
) as binary
1.RelativePathオプションとQueryオプション
Power QueryのWeb.Contents()関数を使うと、Webで公開されているデータを取得することができます。例えば、イギリス政府のオープンデータをAPIを使って取得するには以下のようなURLを使います。
Source =
Json.Document(
Web.Contents("https://data.gov.uk/api/3/action/package_search?q=cows&rows=20")
),
上記では、全て1行のURLに記述していますが、以下のようにBase URLを https://data.gov.uk/api とし、RelativePathオプションとQueryオプションを使って記述することができます。
Source =
Json.Document(
Web.Contents(
"https://data.gov.uk/api",
[
RelativePath = "/3/action/package_search",
Query = [
q = "cows",
rows = "20"
]
]
)
),
この記述は、１行で記述するより読みやすく、メンテナンス性に優れます。また、URLパラメータはエンコードされて渡されるため、SQLインジェクションの危険性が減ります。
特に注意したいのは、URLパラメータの値を変化させて取得したい場合、最初の方法ではPower BI サービスでは以下の表な表示が出て取得できません。
RowCount = List.Last({"1", "10", "20"}),
Source =
Json.Document(
Web.Contents("https://data.gov.uk/api/3/action/package_search?q=cows&rows=" & RowCount)
),
これを、RelativePathやQueryオプションに書き換えるとエラーにならずに取得することができます。
RowCount = List.Last({"1", "10", "20"}),
Source =
Json.Document(
Web.Contents(
"https://data.gov.uk/api",
[
RelativePath = "/3/action/package_search",
Query = [
q = "cows",
rows = RowCount
]
]
)
),
データソースのアクセス許可
なぜURLを１行で書くと、Power BIサービスで使えないのでしょうか。
理由は、データソースに対するアクセス許可の設定にあります。１行でURLを記述した場合、アクセス許可とソースの設定は以下のようになります。
一方、オプションを使った場合は以下のようになり、ソースの変更ボタンはグレーアウトして選択できません。
つまり、１行に書いた場合、URL全体に対して１つのアクセス許可を与えますが、これが変動する場合、明示的なアクセス許可を与えることができないため、動的データソースのエラーが発生してしまうのです。しかし、オプションを使った場合、ベースURLに対してのアクセス許可だけで済みます。
2.Connpassのイベント一覧を取得
connpassのイベントリストを取得するAPIは、こちらにあります。
series_idとstartにパラメータを割り当ててデータを取得するには、以下のように書きます。
Source =
Json.Document(
Web.Contents(
"https://connpass.com/api/v1/event/",
[
Query = [
series_id = Param_SeriesId,
start = Param_Start,
count = "100"
]
]
)
),
これに続けて取得したデータからテーブルを作成する処理を記述していきます。（全リスト）
しかし、一度に最大１００件までしか取得できないので、１００件を超えるイベントを取得するには、数回に分ける必要があります。作成したクエリを右クリックし、コンテキストメニューから関数を作成します。
この関数とList.Generateを使って、データを１００件ずつ取得して結合するクエリを作成します。
let
Source =
List.Generate(
() => [
Start = 1,
Result = try Function.InvokeAfter(
()=> GetEventTable(Number.ToText(Start), Param_SeriesId),
#duration(0,0,0,0.2) // 0.2秒遅延
) otherwise null
],
each [Result] <> null, // 取得でエラーが発生したら終了
each [
Start = [Start] + 100, // [Start]には前の値が入っているので、１００を加えておく
Result =
try Function.InvokeAfter(
// 開始位置を[Start]から１００件後ろに移して取得
()=> GetEventTable(Number.ToText([Start]+100), Param_SeriesId),
#duration(0,0,0,0.2) // 0.2秒遅延
) otherwise null
],
each [Result]
),
Combine = Table.Combine(Source)
in
Combine
最初はstartの値が1で取得し、次に100を加えて101で取得します。取得できる件数を超えるとエラーが発生し、Resultにはnullが入り終了となります。データ取得するGetEventTable関数は、Function.InvokeAfterで遅延を挟んでいます。
(参考 「ページ分割されているWebでページ数がわからないデータを取得する」)
取得したデータを使って、ビジュアルを作成します。
3.Youtubeの動画リストを取得
YoutubeのAPIは、「Youtube API Search:List」を使います。
YoutubeのAPIを利用するには、APIキーが必要なので、取得してパラメータにセットします。(see @shinkai_「【Youtube】APIキーの取得手順（2021/04/08時点のキャプチャ）」)
他にチャンネルIDを調べておきます。
１度に取得できる件数は５０件なので、それを超える場合は２回目以降の検索にpageTokenが必要となります。パラメータの設定はRequiredを外し、TypeはAnyにして、１回目の検索はnullで渡すようにしておきます。
(参考 「省略可能なパラメータ」)
以上２つのパラメータを使って動画リストを取得するクエリを書きます。APIキーは、 ApiKeyName で key と名前を指定しておき、値は資格情報で設定します。１回目と２回目以降のアクセス方法を条件式で分けています。（全リスト）
let
Source =
Json.Document(
if (Param_PageToken = null) then
// １番最初の検索
Web.Contents(
"https://www.googleapis.com/youtube/v3/search",
[
ApiKeyName = "key",
Query = [
part = "id,snippet",
type = "video",
maxResults = "50",
channelId = Param_ChannelId
]
]
)
else
// ２番目以降の検索
Web.Contents(
"https://www.googleapis.com/youtube/v3/search",
[
ApiKeyName = "key",
Query = [
part = "id,snippet",
type = "video",
maxResults = "50",
channelId = Param_ChannelId,
pageToken = Param_PageToken
]
]
)
),
・・・以下略・・・
この状態では、まだApiキーが設定されていないので「資格情報の編集」をします。
「Web API」を選択し、キーの項目にAPIキーを入力して接続すると、以下のようなテーブルが作成されます。
これで問題なく動きますが、1回目と2回目の２つの式に分けないで記述する方法があります。
let
Source =
Json.Document(
Web.Contents(
"https://www.googleapis.com/youtube/v3/search",
[
ApiKeyName = "key",
Query = [
part = "id,snippet",
type = "video",
maxResults = "50",
channelId = Param_ChannelId,
pageToken = Param_PageToken ?? {}
]
]
)
),
この方法は、"CHRIS WEBB'S BI BLOG"で紹介されています。[Query=[postId={"1","2"}]] と記述することで同じパラメータを2つ生成される一方、[Query=[postId={}]] の記述ではパラメータが生成されないという動作を利用しています。
これを関数に変換します。
この関数とList.Generateを使って動画リストを作成するクエリは以下のように書きました。pageTokenは、1回目はnullで渡し、2回目以降は**[Result]{0}[nextPageToken]** で前回の検索結果の１行目から取得するようにしています。最後のページの検索結果には "-" がセットされ、その次の検索はエラーとなって終了します。また、万一無限ループに入ってしまわないよう、検索回数の制限を付けています。
YoutubeのAPIは、1つのキーに対して１日に使用できるアクセス数の制限があり、それを超えるとエラーが返されます。また、アクセス頻度については、Googleアナリティクスでは１秒間に１０件のリクエスト数となっていましたので、間に0.2秒の遅延を挟むようにしました。
ただ、この方式で取得できる動画は、５０１件までしか取得できませんでした。
let
Source =
List.Generate(
// initial as function
() => [
Count = 0,
Result =
try
Function.InvokeAfter(
()=> GetVideoTable(Param_ChannelId, null),
#duration(0,0,0,0.2) // 0.2秒遅延
)
otherwise null
],
// condition as function
// データ取得がエラーになったら終了。安全のため最大１０回までに制限。
each [Result] <> null and [Count] < 10,
// next as function
each [
Count = [Count] + 1,
Result =
try
Function.InvokeAfter(
()=> GetVideoTable(Param_ChannelId, [Result]{0}[nextPageToken]),
#duration(0,0,0,0.2) // 0.2秒遅延
) otherwise null
],
// optional selector as nullable function
each [Result]
),
// 全ての検索結果をまとめてテーブルを作成
Combine = Table.Combine(Source)
in
Combine
Combine
Power BIサービスでAPIキーは
ApiKeyNameを使う方法は、APIキーを安全に取り扱うことができます。しかし、Power BIサービスに発行した後、APIキーを設定しようとすると、選択肢がありません。その代わり、OAuth2が選択肢として出てきます。
Power BIサービスでは、ApiKeyNameをサポートしていないようです。
結局、Power BIサービスに発行して使うには、APIキーをパラメータに設定し、以下のように書き換えました。
Source =
Json.Document(
if (Param_PageToken = null) then
Web.Contents(
"https://www.googleapis.com/youtube/v3/search",
[
// ApiKeyName = "key",
Query = [
key = Param_Key,
part = "id,snippet",
type = "video",
maxResults = "50",
channelId = Param_ChannelId,
pageToken = Param_PageToken ?? {}
]
]
)
),
4.OAuth2認証でYoutubeの動画リストを取得する
OAuth2認証でアクセスするには、アクセストークンを取得しなければなりません。取得出来たら、keyの代わりにaccess_tokenを使ってデータを取得できるようになります。アクセストークンはPostmanで取得しておくこともできますが３０分から６０分くらいで有効期限が切れますので、そのまま使い続けることはできません。
Power BIからOAuth2認証を使用するためには、カスタムコネクタを使う必要がありそうです。検索するとカスタムコネクタを使わず、Power Queryで記述する方法もありますが、安全でなく、信頼できません。また、カスタムコネクタを使用した場合は、Power BIサービスで利用するにはオンプレミスゲートウエイが必要となります。
では、先ほどPower BIサービスで表示されたOAuth2は何かというと、Power BI DesktopでSharePointなどのMicrosoftのAzure Active Directoryの認証を行った場合、Power BIサービスではOAuth2認証が必要になるのです。
5.Web.Contentsのオプション
Web.Contentsで使用するオプションには、以下のようなものがあります。
| Option | Description |
|---|---|
| Query | クエリ パラメーターをプログラムで URL に追加します。エスケープについて考える必要はありません。 |
| ApiKeyName | ターゲット サイトが API キーに対応している場合、このパラメーターを使用すると、URL で使用する必要があるキー パラメーターの名前 (値ではない) を指定できます。 実際のキー値は、資格情報で指定されます。 |
| Headers | この値をレコードとして指定すると、HTTP 要求に追加のヘッダーが指定されます。 |
| Timeout | この値を期間として指定すると、HTTP 要求のタイムアウトが変更されます。 既定値は 100 秒です。 |
| ExcludedFromCacheKey | この値をリストとして指定すると、これらの HTTP ヘッダー キーが、データをキャッシュする計算から除外されます。 |
| IsRetry | この論理値を true に指定すると、データをフェッチするときにキャッシュ内の既存の応答が無視されます。 |
| ManualStatusHandling | この値をリストとして指定すると、応答にこれらのいずれかの状態コードが含まれる HTTP 要求に対するビルトイン処理が回避されます。 |
| RelativePath | この値をテキストとして指定すると、要求を行う前にこれがベース URL に追加されます。 |
| Content | この値を指定すると、POST のコンテンツにオプションの値が使用され、Web 要求が GET から POST に変更されます。 |
6.Webアクセスを確認する方法
Fiddlerというツールを使用するとhtmlパケットの中身を確認することができますが、新たなツールの使い方を覚える必要があります。
先ほどのアクセスを見てみると、最後のアクセスはURLのpageTokenは"-"になり、レスポンスステータスコード400 Bad Requestでエラーとなっていることがわかります。
Power BIの診断機能でも確認することができます。
全リスト
EventTable
let
Source =
Json.Document(
Web.Contents(
"https://connpass.com/api/v1/event/",
[
Query = [
series_id = Param_SeriesId,
start = Param_Start,
count = "100"
]
]
)
),
EventsList = Source[events],
ConvertedToTable =
Table.FromList(
EventsList,
Splitter.SplitByNothing(),
null,
null,
ExtraValues.Error
),
ExpandedColumn =
Table.ExpandRecordColumn(
ConvertedToTable,
"Column1",
{
"event_id",
"title",
"catch",
"description",
"event_url",
"started_at",
"ended_at",
"hash_tag",
"accepted",
"waiting",
"place"
},
{
"event_id",
"title",
"catch",
"description",
"event_url",
"started_at",
"ended_at",
"hash_tag",
"accepted",
"waiting",
"place"
}
),
AddedDateColumn = Table.AddColumn(ExpandedColumn, "date", each Text.Start([started_at],10)),
AddedStartTimeColumn = Table.AddColumn(AddedDateColumn, "start_time", each Text.Middle([started_at], 11, 5)),
AddedEndTimeColumn = Table.AddColumn(AddedStartTimeColumn, "end_time", each Text.Middle([ended_at], 11, 5)),
RemovedColumns = Table.RemoveColumns(AddedEndTimeColumn,{"started_at", "ended_at"}),
ChangedType = Table.TransformColumnTypes(RemovedColumns,{{"event_id", type text}, {"title", type text}, {"catch", type text}, {"description", type text}, {"event_url", type text}, {"hash_tag", type text}, {"accepted", Int64.Type}, {"waiting", Int64.Type}, {"place", type text}, {"date", type date}, {"start_time", type time}, {"end_time", type time}})
in
ChangedType
VideoTable
以下のクエリを関数に変換して Youtube クエリから呼び出す。
let
Source =
Json.Document(
Web.Contents(
"https://www.googleapis.com/youtube/v3/search",
[
// ApiKeyName = "key",
Query = [
key = Param_Key,
part = "id,snippet",
type = "video",
maxResults = "50",
channelId = Param_ChannelId,
pageToken = Param_PageToken ?? {}
]
]
)
),
// nextPageTokenの取得
// 最終行が取得されnextPageTokenがない場合は"-"を入れる
NextPageToken = try Source[nextPageToken] otherwise "-",
Items = Source[items],
ConvertedToTable = Table.FromList(Items, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedColumn = Table.ExpandRecordColumn(ConvertedToTable, "Column1", {"id", "snippet"}, {"id", "snippet"}),
ExpandedId = Table.ExpandRecordColumn(ExpandedColumn, "id", {"videoId"}, {"videoId"}),
ExpandedSnippet = Table.ExpandRecordColumn(ExpandedId, "snippet", {"publishedAt", "title", "description", "thumbnails"}, {"publishedAt", "title", "description", "thumbnails"}),
ExpandedThumbnails = Table.ExpandRecordColumn(ExpandedSnippet, "thumbnails", {"medium"}, {"medium"}),
ExpandedMedium = Table.ExpandRecordColumn(ExpandedThumbnails, "medium", {"url"}, {"url"}),
ChangedType = Table.TransformColumnTypes(ExpandedMedium,{{"videoId", type text}, {"publishedAt", type datetimezone}, {"title", type text}, {"description", type text}, {"url", type text}}),
// videoIdからYoutubeのURLを作成
TransferVideoUrl =
Table.TransformColumns(
ChangedType,
{{
"videoId",
each "https://youtu.be/" & _
}}
),
// UTCからJSTに変換して、タイムゾーン情報を削除
AddedPublishDate = Table.AddColumn(
TransferVideoUrl,
"登録日",
each DateTimeZone.RemoveZone(DateTimeZone.SwitchZone([publishedAt], 9)),
type datetime
),
// 列を追加してnextPageTokenをセットする
AddedNextPageToken =
Table.AddColumn(
AddedPublishDate,
"nextPageToken",
each NextPageToken,
type text
),
RemovedColumns = Table.RemoveColumns(AddedNextPageToken,{"publishedAt"}),
RenamedColumns = Table.RenameColumns(RemovedColumns,{{"title", "タイトル"}, {"description", "内容"}})
in
RenamedColumns as table
Youtube
let
Source =
List.Generate(
// initial as function
() => [
Count = 0,
Result =
try
Function.InvokeAfter(
// ApiKeyNameを使う場合
//()=> GetVideoTable(Param_ChannelId, null),
// APIキーをパラメータに設定して使う場合
()=> GetVideoTable(Param_Key, Param_ChannelId, null),
#duration(0,0,0,1) // 1秒遅延
)
otherwise null
],
// condition as function
// データ取得がエラーになったら終了。安全のため最大１０回までに制限。
each [Result] <> null and [Count] < 10,
// next as function
each [
Count = [Count] + 1,
Result =
try
Function.InvokeAfter(
// ApiKeyNameを使う場合
//()=> GetVideoTable(Param_ChannelId, [Result]{0}[nextPageToken]),
// APIキーをパラメータに設定して使う場合
()=> GetVideoTable(Param_Key, Param_ChannelId, [Result]{0}[nextPageToken]),
#duration(0,0,0,1) // 1秒遅延
) otherwise null
],
// optional selector as nullable function
each [Result]
),
// 全ての検索結果をまとめてテーブルを作成
Combine = Table.Combine(Source)
in
Combine