API分页在JSON体(不是头)-如何访问与电力查询自定义连接器?

Question

我已经构建了一个客户连接器，通过OAuth2连接到Vimeo API。一切都工作得很好，但似乎我需要想出一个解决方案来处理分页，因为我只在每页上得到25个项目。

我看到了如何使用Table的文档。这里的GenerateByPage和getNextPage:

https://learn.microsoft.com/en-us/power-query/samples/trippin/5-paging/readme tablegeneratebypage

以及示例GitHub自定义连接器

中的实现https://github.com/microsoft/DataConnectors/blob/master/samples/Github/github.pq

上面的函数示例:

Github.Contents = (url as text) =>
let
content = Web.Contents(url),
link = GetNextLink(content),
json = Json.Document(content),
table = Table.FromList(json, Splitter.SplitByNothing())
in
table meta [Next=link];
Github.PagedTable = (url as text) => Table.GenerateByPage((previous) =>
let
// If we have a previous page, get its Next link from metadata on the page.
next = if (previous <> null) then Value.Metadata(previous)[Next] else null,
// If we have a next link, use it, otherwise use the original URL that was passed in.
urlToUse = if (next <> null) then next else url,
// If we have a previous page, but don't have a next link, then we're done paging.
// Otherwise retrieve the next page.
current = if (previous <> null and next = null) then null else Github.Contents(urlToUse),
// If we got data back from the current page, get the link for the next page
link = if (current <> null) then Value.Metadata(current)[Next] else null
in
current meta [Next=link]);

GetNextLink = (response, optional request) =>
let
// extract the "Link" header if it exists
link = Value.Metadata(response)[Headers][#"Link"]?,
links = Text.Split(link, ","),
splitLinks = List.Transform(links, each Text.Split(Text.Trim(_), ";")),
next = List.Select(splitLinks, each Text.Trim(_{1}) = "rel=""next"""),
first = List.First(next),
removedBrackets = Text.Range(first{0}, 1, Text.Length(first{0}) - 2)
in
try removedBrackets otherwise null;

然而，我的问题是，从Vimeo API返回的分页上的元数据是通过JSON体响应来的，而不是在头中，正如文档和示例中假设的那样。Power Query/M中是否有一种简单的方法或辅助函数，允许我查看JSON响应的主体，获取分页JSON对象(如下所示)，并从那里构建我的代码?

以下是关于JSON主体内Vimeo API的分页的反馈:

"total": 1012,
"page": 1,
"per_page": 25,
"paging": {
"next": "/users/{our-user-id}/videos?page=2",
"previous": null,
"first": "/users/{our-user-id}/videos?page=1",
"last": "/users/{our-user-id}/videos?page=41"
},

非常感谢任何帮助-这是非常感激!

,

josh

Answer 1

很难从这些信息中归纳出一些东西，但看看这是否有帮助

GetNextLink = (response) =>
// response is data already run through Web.Contents()
// looks for a row that contains     "first":  
// x would evaluate to be            "first": "/users/{our-user-id}/videos?page=1",
// y would parse x to get            /users/{our-user-id}/videos?page=1
Source = Lines.FromBinary(response),
x = List.RemoveNulls(List.Transform(List.Positions(Source), each if Text.Contains(Source{_},"""first"":") then  Source{_} else null)){0},
y=Text.BetweenDelimiters( x,": ""","""")
in y