cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lbeckers
Regular Visitor

Applying function on data in excel after adding a row

Hi all,

 

question from a newbie...

 

My planned flow has to save data from MS forms in an Excel file and then fill out a Word template. I wanted to edit some data from the MS forms in the excel file, before they are used to complete the Word document (and send as attachment with mail).

Schermafbeelding 2021-04-30 om 01.48.04.png

In my excel file I have 2 worksheets, where the data from the 1st sheet should be modified by functions in the 2nd sheet so that these can be entered in the word document.

When testing the flow, the data is normally added in the 1st sheet. But in the 2nd worksheet, no data are added, not even unprocessed copies of cells out of the other sheet. On the other hand, it seems that a row is added here as ...

 

The connector description for excel business states that 'Simultaneous file modifications made by other connectors or manual edits are not supported' Does anyone have a suggestion for this?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
fchopo
Super User
Super User

Hi @lbeckers 

Another solution would be the replacement of the Excel document by a SharePoint list, which would make the flow much more easier.

Regards,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

6 REPLIES 6
fchopo
Super User
Super User

Hi @lbeckers 

In order to update 2nd sheet, I would use "Office Scripts": Office Scripts in Excel on the web - Office Scripts | Microsoft Docs

I mean, you can create an script to update the 2nd sheet based on the data in the 1st sheet.

When you have created this script, you can call it from a Power Automate flow: 

 

power-automate-tutorial-6[1].png

 

Have a look at this documentation: Call scripts from a manual Power Automate flow - Office Scripts | Microsoft Docs

Hope it helps!

Ferran

 

 

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!
GeoffRen
Microsoft
Microsoft

Can you provide more information about what you want? It'd be helpful if you could show an example of the 1st and 2nd worksheets and how you expect the data to be modified.

Hi GeoffRen,

 

The idea is to use a XLOOKUP on the data entered from Forms in the Excel. And the matched data/info from this should be inserted in the template word document.

Below you can find an example on what I would like to get:

Data from Forms in ExcelData from Forms in Excel

 

Data in Excel for WordData in Excel for Word

 

RefRef

 I hope this can provide you some information regarding my question.

 

Thanks in advance

Hi @fchopo ,

 

Thank you very much for your quick answer.

I have very limited knowledge in writing scripts. So if this appears to be the only option to get this flow running, I will need to counsel friend/family.

 

Kind regards

fchopo
Super User
Super User

Hi @lbeckers 

Another solution would be the replacement of the Excel document by a SharePoint list, which would make the flow much more easier.

Regards,

Ferran

Did I answer your question? Please consider to mark my post as a solution to help others.
Proud to be a Flownaut!

View solution in original post

Hi @fchopo 

 

great solution! Slightly cumbersome initially with the IF function instead of the XLOOKUP, but it does the job as needed. Thanks for your input!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (2,506)