Smartsheet to Excel with Power Query

Hi, I have a question related to Smartsheet. For several months, I have been using Smartsheet for working purposes. From Smartsheet, we link to our local Excel file with Excel Power Query to extract the real-time data. But recently, we just experienced an issue where the data extracted from the Smartsheet is not fully loaded in the excel file.

Giving you the example where in the Smartsheet sheet, there are 436 rows that contains data. However, when I extract and load the data from Smartsheet to excel, there are only 50 rows appear. I checked the excel query if there are any filter that might accidentally be made but somehow, there is none.

Find more posts tagged with

Accepted answers

All comments

Humashankar

Hi - I hope that you are Doing good !!

It is common and we know tht default limit for Smartsheet API responses is 500 and there are 436 in your sheet so definetly this could not be a source limit issue / API call issue

Kindly Validate filters or sorting anything applied in your Power Query that could be limiting the number of rows returned

Also check one more data point which is on the Power Query part which needs to handle pagination to retrieve all the data

Kindly check for filters applied within the Excel worksheet might also cause this issue

Hope this helps to reach a level of triage.

Thank you very much and have a fantastic day!

Warm regards

AQIL

Hi - Hope you are doing fine.

I do have data that are exceed 500 and it seems work well for me.

I did validate my Power Query and there are no filters applied.

I did discuss with my IT team and we noticed that the Publish Link for the data sheet took long time to render line by line and this thing happened vary in time. However, I did not face the same issue (long render time) on the other data sheet that contains 1000s of rows.

I wonder what are the factors impact my data sheet?

Thank you and have a great day!

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.