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

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?

Find more posts tagged with

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.