Unanswered

Create a flow to extract data from Excel files in a SharePoint list

(0) Share

Report

Posted on by abbatem

Hello dear Power Automate experts,

I would like to create a flow which extracts a specific data from all xlsx files currently available on SharePoint. I have read different ways but I'm lacking knowledge / experience to adapt the shown solutions to my use case. Does anyone know how to create a flow like that?
I'll try to explain my setting: I have a SharePoint list with several items. Each item has its own xlsx file (created by another cloud flow) which is a Purchase Order (PO). And each of this PO xlsx contains the final sum of the PO in the cell D38 (column D, row 38). Now I would like to create a cloud flow which extracts all final sums of all POs and lets me know the sum of all these final sums. The final sum can be displayed with a pop-up, calculated and pasted into a separate document or shown in a new column in the SharePoint list mentioned in the beginning. Basically, the way the sum of the final sums gets shown does not matter at all, I simply need it to be displayed. 🙂

Is there a way to create a flow like that? I'd appreciate every type of help!

Thank you very much in advance!

Best regards,

abbatem

Many thanks in advance!

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

rzaneti 4,241 Super User 2025 Season 2 on at

Like (0)

Report
Hi @abbatem ,

I won't be able to provide you with a step-by-step at the moment, but I can share some inputs to your flow design

It is possible to achieve what you want with Power Automate, but the flow may be a little complex
In general, Excel files are can be easily read by Power Automate when the data is organized into tables, which seems not to be your case. Don't worry, it is still possible to pull data from a spreadsheet range by using the Office Scripts. If you are not aware about this tool, I recommend you to read the articles below (one with a quick introduction about how to get started, and other with a step-by-step about how to extract data from a Work Order):
http://digitalmill.net/2023/06/19/get-started-with-office-scripts/
http://digitalmill.net/2023/09/01/accessing-excel-ranges-with-power-automate/

If you have the .xlsx files attached to SP List items, it may be a little complex to call Office Scripts from them. In my opinion, it will probably be easier to copy these files into a document library to run the Scripts, and after extract the data, delete these clones.
To retrieve the 'Final sum', your idea to just write it into a List field seems to be the best approach. If it makes sense for you, Power Automate also allows to send you a summary table of the final sums by Teams or email.

If you have any questions about the ideas above or need any clarification, let me know!

Was this reply helpful? Yes No
abbatem 15 on at

Like (0)

Report

Oh, that sounds good as I tried to create my flow based on this solution, but I could not use Get Rows as it was deleted from PA. Get a row did not work for me, so I will wait for Get rows' return. 🙂

Was this reply helpful? Yes No
abbatem 15 on at

Like (1)

Report

Thanks a lot, I will try to use Office scripts in the meantime while I wait for Get rows. I really do not know a lot about the scripts, so thank you for the hyperlinks. I will have a look at those. I would be glad if I could come back to you and @AlexEncodian, if I still need some help (which is very likely going to happen).

Was this reply helpful? Yes No
abbatem 15 on at

Like (1)

Report

Hi Raphael,
I had quite a lot of releases going on the last months, so I could not have a look into your solutions, but now I really want to create this flow. Your links are amazing, thanks a lot,PS: I love it when people like you write such detailed and well-written explanations for persons like me. Thank you very much for your dedication and willingness to help. This is really amazing! It worked, just as you described it! 🙂
I am really happy about that. Thanks a lot as I could not have done it without your instructions! 🙂

I would just like to go next level and think about on how to do that on multiple files and create the sum of this results. Currently, I can tell the flow to perform this action on a local file. Now, if everything would work as I would wish, this flow performs the same action on a lot of files which I, ideally, do not have to download locally, as the files are in a SharePoint list. But in your post you wrote that it is easier to download those files and then delete them after having finished the flow. Is that easy so that it can be done by people like me as well? 🙂

Was this reply helpful? Yes No
abbatem 15 on at

Like (0)

Report

Hi Alex,

thanks a lot, this helps. I am really not an expert in "power-automating" things, so this question maybe is an basic one, but how do I define the Key Value in the Get a row action? I already defined the right column (Result), but now I do not know how to tell the action that I need it to tell me the value of D37 (the total price in €).

This is how the Excel looks, actually:

PS: I know that I could enter 100,00 as the cell's value is 100,00. This would solve the problem for this specific table, I think. But the total price of each PO, I am working with, varies. So how can I solve that?

Was this reply helpful? Yes No
abbatem 15 on at

Like (0)

Report

Anyone? Please? 😅

Was this reply helpful? Yes No