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

Forms to Existing Excel Document

Hello,

 

I'm currently creating a flow that upon filling out a Forms document, a SharePoint list item is created to document the response, it is then sent to the supervisor of the requester for approval/denial, if approved the responses are saved as a line item in a new Excel table to be reviewed by superiors. 

 

What I need to happen is instead of creating a new line in the new excel document, is when approved, it creates a new line in an existing excel document (that is edited weekly by users) and is added to a specific section of the document, let's say by location, so that location will have a new line submitted based on the responses in the form. 

 

Is this possible within the limits of Power Automate? 

5 REPLIES 5
JohnAageAnderse
Super User
Super User

Hello @JustinL1 

Assuming that the Excel file contains a sheet for each location, then yes, you could use Power Automate to add rows to the table in each sheet.

Did you manage to make a solution for this or are you stuck somewhere?

Kind regards, John

@JohnAageAnderse Hello,

 

Thanks for your response! 

 

Currently the project is on pause because there are other projects in the pipe that are priority so I have some time to diagnose this. 

 

So let's say in the Form there is a location code that is selected as a choice, and each sheet is separated by location, could we write an if then statement in Power Automate to send it to the location code that was selected in the Form? 

 

Does this make sense? 

Hello @JustinL1 

With a location code from the Form, you should be able to identify the Sheet and the Table in the Excel file, in which to add a row.

Try to implement one or more expressions that will do the translation from location code to Sheet and Table. For examble by using lookup lists.

 

Example scenario:

1) Form is submitted

2) Get items from the list "Location to Sheet and Table" where (Filter Query) Location code eq Form location code. Should only return one item!

3) If none or more than 1 found, error situation, inform someone to look into this as it shouldn't happen! Terminate flow!

4) Loop (apply to each) through each item (one) from step 2) and do:

4.1) Add row to Excel file in Sheet (from step 2) in Table (from step 2)

...

 

Kind regards, John

@JohnAageAnderse 

 

Here is what I have so far. I'm connecting the sharepoint list item to the form item, but how would I connect it to the table to filter out which sheet it goes on?

 

112233
44

Hello @JustinL1 

In your action for Add a row into a table, in the Table field, in the drop down list, choose the Enter custom value. This will allow you to use a Dynamic property instead, for example from a variable.

Flow.JustinL1.Add row to a table.table name.jpg

If your Excel table names are the same as the Location value you get from the Form, then you can use the Location value as the table name.

Otherwise you need to translate your Location value into a table name as I mentioned previously.

Kind regards, John

Helpful resources

Announcements
MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MSFTBizAppsLaunchEvent

Experience what’s next for Power Virtual Agents

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Users online (14,575)