cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Get all rows in EXCEL-table (EXCEL Online)

Hello,

 

I have a problem fetching all the rows in my EXCEL-table (via EXCEL Online). I only get the first 256 rows in my EXCEL-table which is the problem for my next steps, adding and updating existing rows. I saw in the body of my result that there's a variable with the link to the next 256 rows. I know in pagination that it is possible to iterate over each next link until this is null and if it's about a webpage, I can use the HTTP-action but here we are talking about the the EXCEL Online-connector which has nothing to do with a webpage.

Picture Flow.PNG

Someone whom has experience with this kind of problem before?

 

 

Kind regards

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Get all rows in EXCEL-table (EXCEL Online)

Hi @naelske_cronos ,

 

You could try to enable Pagination, and increase the number of Limit to get all the rows.

Image reference:

65.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Super User II
Super User II

Re: Get all rows in EXCEL-table (EXCEL Online)

Unfortunatley, what you're seeking to do really isn't recommended for the Excel connectors. Most Flow experts recommend staying away from the Excel connectors if at all possible due to their severe limitations, including maxmimum API calls and file lockout limitations

As the Excel connector has an API limit of 100 calls per 60 seconds, one option you may have is to build in Flow delays to insure that you aren't encroaching on the API call limits. 

I personally would never utilize the Excel connector for the workload you are seeking, it is just too unreliable with those large datasets. I'd instead utilize a SharePoint List. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

6 REPLIES 6
Highlighted
Super User II
Super User II

Re: Get all rows in EXCEL-table (EXCEL Online)

Can you please provide a detailed screenshot of your Flow so that we can better assist you?

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Community Support
Community Support

Re: Get all rows in EXCEL-table (EXCEL Online)

Hi @naelske_cronos ,

 

You could try to enable Pagination, and increase the number of Limit to get all the rows.

Image reference:

65.PNG

Hope it helps.

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Frequent Visitor

Re: Get all rows in EXCEL-table (EXCEL Online)

Hello @Brad_Groux 

 

I hope it's readable, because it's quite a big flow I have made. In the first screenshots, I'll initialize my variables that will be used for my HTTP-requests.

1.PNG

In the second screenshot, you'll see that I make two HTTP-requests, one to get the access token from the Azure Active Directory, so I can use this access token to access the endpoint of my second HTTP-request, the Graph API. The Graph API works with pagination. In the response of my HTTP-request, I have a value with the link to the next 100 rows. This link I'll put in my variable 'O365 Next Link'. The variable 'O365 Users' is an array with all the rows in my HTTP-request together (I'm talking about 1500 rows). You'll see this will be used in the next screenshot.

 

What I'm actually trying to do is to get all the users from Graph API and write them to an EXCEL-table in SharePoint with the connector (EXCEL Online). First I'll look which rows are already in my EXCEL-table.

2.PNG

With my do until, I'll check every user in the Graph API if it already exists in my EXCEL-table in SharePoint. If it exists, I'll update the existing row in my EXCEL-table, if it doesn't, I'll add a new row to my EXCEL-table. I'll apply this to each user but as I talked about earlier, these are the first 100 rows because in Graph, they work with pagination. I'll check if there is a next link and if there is, I'll make a HTTP-request with the next link to fetch the next 100 rows in that next link and to the same steps again in my do until, until there's no next link anymore and all the rows are being loaded.

3.PNG

 

To get to the real problem, I've seen that I only get the first 256 rows in my EXCEL-table which is a problem because I have more than a 1000 rows. This is a problem for my next steps, because in theory only the first 256 rows exists in my EXCEL-table, so all the rest will be added as new rows instead of existing rows. However, I've seen in the result as shown in the screenshot below, that it also uses a value 'next link' with the link to the next page with the the 256 rows. How do I call this next link? Can I use a do until like I did for Graph?

4.PNG

 

I hope this is understandable.

 

Kind regards

Highlighted
Frequent Visitor

Re: Get all rows in EXCEL-table (EXCEL Online)

Hello @v-bacao-msft

 

As you can see in the reply to @Brad_Groux, this is the working of my flow. I understand the settings of pagination like you showed me in your screenshot, but for me it looks like a bad practice to use this, as my EXCEL-table reaches more than 5000 rows. If there's a next link in the body result of 'list rows in table' why can't this be used to fetch the next 256 rows? How can this even be done? Is this with a HTTP-request? This is the next link I get: https://flow-apim-europe-001-francecentral-01.azure-apim.net/apim/excelonlinebusiness/shared-excelon...

4.PNG

 

Kind regards

Highlighted
Super User II
Super User II

Re: Get all rows in EXCEL-table (EXCEL Online)

Unfortunatley, what you're seeking to do really isn't recommended for the Excel connectors. Most Flow experts recommend staying away from the Excel connectors if at all possible due to their severe limitations, including maxmimum API calls and file lockout limitations

As the Excel connector has an API limit of 100 calls per 60 seconds, one option you may have is to build in Flow delays to insure that you aren't encroaching on the API call limits. 

I personally would never utilize the Excel connector for the workload you are seeking, it is just too unreliable with those large datasets. I'd instead utilize a SharePoint List. 

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

View solution in original post

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Get all rows in EXCEL-table (EXCEL Online)

it solved my issue and I would suggest to accept it as solution

Helpful resources

Announcements
firstImage

Super User Program Update

Three Super User rank tiers have been launched!

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (9,597)