Answered

Get all rows in EXCEL-table (EXCEL Online)

(0) Share

Report

Posted on by naelske_cronos

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

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (2)

Brad_Groux 4,556 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
Verified answer

v-bacao-msft on at

Like (4)

Report

Hi @naelske_cronos ,

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

Image reference:

Hope it helps.

Best Regards,

Was this reply helpful? Yes No
naelske_cronos on at

Like (0)

Report

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.
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.
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.

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?

I hope this is understandable.

Kind regards

Was this reply helpful? Yes No
naelske_cronos on at

Like (0)

Report

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-excelonlinebu-.../drives/.../tables/{...}/items?source=groups/...&$skip=256

Kind regards

Was this reply helpful? Yes No
Verified answer

Brad_Groux 4,556 on at

Like (1)

Report

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!

Was this reply helpful? Yes No
ErichH 322 on at

Like (0)

Report

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

Was this reply helpful? Yes No