通过Power Query访问full Clockify保存报告

Question

根据Alex的问题(以及我自己对这个问题的回答)，here我已经连接到一个保存的clockify报告，该报告已经正常工作了一段时间。但是，最近clockify在保存的报告中引入了分页，结果报告中只返回(最多)前50个条目。

Clockify支持将我指向"count“或" page”作为可能的解决方案("count“(默认50)应指示返回条目的数量，而"page”(页)应指示返回报告的页面)。不幸的是，我也不能使返回的内容有所不同。

= Json.Document(
        Web.Contents("api.clockify.me/api/reports/{my report}",
                     [Headers=    [ContentType="application/json",
                                   #"X-Api-Key"="{my API Key}",
                                   count="9999"                                  
                                   ]
                      ]                    
                    )
                )

上面的例子，我认为应该返回9999个条目，但实际上只返回50个条目。"count“参数被完全忽略...

Answer 1

我找错人了.事实证明，在保存的报告中无法避免分页，count参数什么也不做。此API正在开发中，因此，可能会发生这样的更改。相反，clockify支持建议使用POST /workspaces/{workspaceId}/reports/summary/，这需要request主体中的一些参数。示例：

{
  "startDate": "2018-01-01T00:00:00.000Z",
  "endDate": "2018-12-31T23:59:59.999Z",
  "me": "false", (Options: "false", "true", "TEAM", "ME", "null")
  "userGroupIds": [],
  "userIds": [],
  "projectIds": [],
  "clientIds": [],
  "taskIds": [],
  "tagIds": [],
  "billable": "BOTH", (Options: "BOTH", "BILLABLE", "NOT_BILLABLE")
  "includeTimeEntries": "true",
  "zoomLevel": "week", (Options: "week", "month", "year")
  "description": "",
  "archived": "Active", (Options: "Active", "All", "Archived")
  "roundingOn": "false"
}

(我从clockify support获得了更多的参数，但据我所知，目前它们没有什么不同。我在下面对它们的评论超过了它们。)

我让这段代码在Power Query中工作：

let
 url="https://api.clockify.me/api/workspaces/{workspaceId}/reports/summary/",
    content ="{
      ""startDate"": ""2017-01-01T00:00:00.000Z"",
      ""endDate"": ""2025-12-31T23:59:59.999Z"",
      ""me"": ""false"", 
      ""userGroupIds"": [],
      ""userIds"": [],
      ""projectIds"": [],
      ""clientIds"": [],
      ""taskIds"": [],
      ""tagIds"": [],
      ""billable"": ""BOTH"", 
      ""includeTimeEntries"": ""true"",
      ""zoomLevel"": ""week"", 
      ""description"": """",
      ""archived"": ""Active"", 
      ""roundingOn"": ""false"",
      ""groupingOn"": ""false"" 
"/* ,
      ""groupedByDate"": ""false"",
      ""page"":0,
      ""count"":9999,
      ""sortDetailedBy"": ""timeAsc"" (Options: "description", "descriptionAsc", "userName", "userNameAsc", "duration", "durationAsc", "time", "timeAsc")
 */
       &" }",      
    Source = Json.Document(
                    Web.Contents(url,    [Headers=    [ #"X-Api-Key"="{yourAPIkey}"
                                                        ,ContentType="application/json"
                                                        ,#"Content-Type"="application/json"
                                                       ]
                                         ,Content=Text.ToBinary(content)
                                         ] 
                                 )
                             )
    in
        Source

(来源：https://community.powerbi.com/t5/Desktop/How-to-add-body-into-Web-Contents/td-p/128996

https://eriksvensen.wordpress.com/2014/09/15/specifying-json-query-in-power-query-example-statistics-sweden/

进一步的发展：

https://www.thebiccountant.com/2018/06/05/easy-post-requests-with-power-bi-and-power-query-using-json-fromvalue/ )