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

CycleBagEd

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.

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 our new, updated version.

Cheers,

Genevieve

Genevieve P.

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 our new, updated version.

Cheers,

Genevieve

CycleBagEd

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.

Thanks very much for getting 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