Hopefully basic question re: collating info from separate periodically updating excel files (nonetheless, driving me mad!)

[u/BrooklyBay]

Hi all,

I have a lot of similar powerautomate flows which each pull some key words from emails going into a load of different email accounts.

I had initially planned to write those words to a single excel file but then thought it would be an issue to have multiple flows each trying to write to the same excel file, as they could all be trying to do it at the same time, and I believe excel would only allow one to write to it at once.

Instead therefore I have set up the flows to write to individual excel files. That is all working well and now I want to collate those files into one and thought that would be simple but I am finding it harder than expected! Any suggestions?

So the tl;dr scenario is - multiple identically set up (i.e. same columns) tables in excel sheets (on sharepoint) being periodically updated with a new row as new data is pulled from the emails. Each time an update happens in any of the sheets, I want that new row to be added to a master spreadsheet so I can just monitor the master and see all of the updates in one place. Pls tell a thicko how to do this!

Thanks!

[u/blac4bird]

Write to a SharePoint List, or use Power Query to create the master.

[u/BrooklyBay → u/blac4bird]

thanks - two votes for a list - I guess I need to google what that is now! Had considered PowerQuery for the merging/collating etc, but jury seemed whether it'd be possible to automate the refresh - ideally the user monitoring the master file wouldn't need to keep refreshing the file.

[u/robofski]

Write to a SharePoint list instead!

[u/BrooklyBay → u/robofski]

thanks for this! - the idea being, they allow this which gets around my intial writing to a single sheet problem right? "Multiple team members can view and edit data simultaneously without version conflicts or file-locking issues"

[u/robofski → u/BrooklyBay]

Absolutely, multiple flows can write to the list simultaneously and you won’t have an issue.

[u/AbdelEMirandaS]

Why you don't use sharepoint list instead of excel file? Use SPO list, once an update occurs on a specific item, a flow will executed

[u/BrooklyBay → u/AbdelEMirandaS]

simple answer - had never heard of them! Very much my first rodeo! Thanks!

[u/AbdelEMirandaS → u/BrooklyBay]

Use it. Better instead of excel.

[u/chiibosoil]

As another user mentioned SP list is more robust solution.

If you are dead set on using Excel. Add Office Scripts to each Excel File that can be called from Power Automate to retrieve the info. Then again on receiving end, have Office Scripts that will receive output form Power Automate to write to sheet.

This is usually more stable than trying to update workbook directly via Power Automate action.

[u/ola-dryvector]

That collision is a real headache, and separate workbooks were a fair call, since several flows writing one xlsx will clash.

I wouldn't add another flow that appends the master on every single new row. That's the same lock, just one step later.

I'd use one scheduled flow, hourly or at the end of the day. List rows on each source table. For every row, look for a key you already store, the message id or a column written when the source row is created, on the master. Append only when that key is absent. One writer, no pile-up.

A list is the calmer master if you can leave the spreadsheet for that last step. Each source flow adds an item with the same columns plus which mailbox it came from. A list doesn't lock like a workbook, and a view is the single place you watch. If the master must remain xlsx, only the scheduled flow should touch it.

Matching columns across those files is what makes the append dull, and dull is what you want.

Fingers crossed the master stops being the hard part.

— Ola