What is the correct syntax for includeAll=true when pulling a report from the API?

CycleBagEd · 05/23/22

Hi,

I am using an API token to import smartsheet report data into Power Query in MS Excel.

If I pass no information about pageSize or includeAll I get 100 rows returned.

If I use the following:

= Json.Document(Web.Contents("https://api.smartsheet.com/2.0/reports/8695992879998852/?pageSize=10013",[Headers=[Authorization="Bearer MY-API-KEY-GOES-HERE"]]))

then 10,000 rows are returned, despite the report being 10013 rows right now. So it seems there is a limit on pageSize.

If I use the following:

= Json.Document(Web.Contents("https://api.smartsheet.com/2.0/reports/8695992879998852/?includeAll=true",[Headers=[Authorization="Bearer MY-API-KEY-GOES-HERE"]]))

then I get 100 rows.

How can I retrieve all rows in my report via the API via Power Query please?

Genevieve P. · 05/24/22

Hi@CycleBagEd

The default Page Size (if not specified) is 100, and the maximum rows that a Get Report call can retrieve is 10,000 rows at a time, as you've found.

Here's the information in our previous documentation that specifies this:https://smartsheet-platform.github.io/api-docs/#get-report

I will let the API documentation team know that this isn't clearly outlined in ournew, updated version.

Cheers,

Genevieve

Genevieve P. · 05/24/22

Hi@CycleBagEd

The default Page Size (if not specified) is 100, and the maximum rows that a Get Report call can retrieve is 10,000 rows at a time, as you've found.

Here's the information in our previous documentation that specifies this:https://smartsheet-platform.github.io/api-docs/#get-report

I will let the API documentation team know that this isn't clearly outlined in ournew, updated version.

Cheers,

Genevieve

CycleBagEd · 05/24/22

Thanks@Genevieve P.- it's interesting that I can retrieve over 10,000 rows via ODBC but the API has a 10,000 record limit - This will lead me to implement workarounds - it would be good if that could be removed.

非常感谢ting back to me on this. I am not a programmer, and couldn't find the correct way to append the options onto the report ID number in Power Query and had to source these from the general online community.

cheers

Ed

What is the correct syntax for includeAll=true when pulling a report from the API?

Best Answer

Answers

Categories