How to Power Automate replace formulas with values in Sharepoint excel file?

(1) Share

Report

Posted on by MichaelF1

Hi everyone

I have a document library in Sharepoint which contains excel workbooks (with one spreadsheet/tab each)

When a new workbook is added I need to automate the process of replacing any formulas with values?

Thanks for the help in advance 🙂

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (0)

sandeepstw 312 Moderator on at

Like (1)

Report

Hi There,

Create a trigger 'When a file is created in a folder' SharePoint connector in Power Automate.
Use action 'List rows present in a table' Excel business and setup location of file in share Point.
Apply 'Apply to each' control and use 'value' from the 'List rows present in a table' action for its input.
Add the 'Delete row' action inside 'Apply to each', referencing the ID from the 'List rows present in a table' step.
Add the 'Add a row into a table' action still within 'Apply to each', specifying the same table as before, but with the cells' values instead of the formulas.

Save and test.

Please give kudos and mark as solution if it helps.

Thanks,
Sandeep Mishra

Was this reply helpful? Yes No
MichaelF1 82 on at

Like (1)

Report

Hi @sandeepstw , thanks for you reply!
I'm getting stuck when I try to "setup location of file in share Point". Do I have to specify an individual file? I want the latest just added to be selected; can I do this? Or all of the files would work just as well. Also, I can't select a table in the next step. I've taken a screen shot:
Thanks again 🙂

Michael

Was this reply helpful? Yes No
sandeepstw 312 Moderator on at

Like (0)

Report

yes, you need to select file

Was this reply helpful? Yes No
MichaelF1 82 on at

Like (1)

Report

@sandeepstw which file do I need to select? I either need to select the most recent one, or the whole folder. I don't know how to select either of these options?

Was this reply helpful? Yes No
sandeepstw 312 Moderator on at

Like (0)

Report

Hi,

Use in 'when file is created' action reference folder there in which you want to update files and then use 'get file' action to get the file from previous action and use that in next actions to update file .

Was this reply helpful? Yes No
rickFL 30 on at

Like (0)

Report

I tried this, but I can't get it to work. This is what I have. Can someone support? It says that there are Null values, but there are none.

Flow:

Error:

Table:

Screenshot_202407...

Screenshot_202407...

Screenshot_202407...

Was this reply helpful? Yes No