Forum Discussion
Power automate flow - Excel worksheets created from MS forms
Hi,
I’m trying to create a flow which will record the responses from MS forms within excel workbook.
I have figured out how to add rows to the spreadsheet, but ideally I’d like to create separate worksheet within the workbook when someone responds to the form, rather than keep adding rows to the same table.
I have added an action which creates a worksheet, but it’s always empty, and all the answers are recorded in the main table I have created in the workbook originally….
please help! 🙏🏻🙏🏻🙏🏻
Thank you 😊
1 Reply
bs1993 Make sure you have already created your workbook and you know what the column names will be. The flow below will add a new worksheet and table for each response submitted.
Form
Flow
1. The trigger is, as always, the Forms when a new response is submitted" immediately followed by the "get response details" action:
2. Add an Excel "create worksheet" action and select the location and library and the workbook. Worksheet names must be unique so we are going to use the response ID for the worksheet name:
3. Next, add a "create table" action. Table names also need to be unique and we're going to name the table the same as the worksheet. So for the table range field I have selected Response ID!A1:F1 as my table will have 6 columns. The table name can't start with a number, only with a letter or underscore, so in the table name field I have put an underscore before Response ID. For the column names field type the column names separated by a comma.
4. Finally, we'll add a row into the table so add the "add a row into a table" action. For the table field add the underscore before Response ID as before. Open the advanced options and for the date time format field select ISO 8601 otherwise any dates will be put into the table as an integer. In the row field you need to add some JSON code with your column names and select the the questions from the form.
The response below had response ID 3 and you can see in the image that the worksheet name is 3 and the table name is _3 and it has put the answers into the relevant columns:
When another response is submitted, in this case response ID 4, it adds another sheet and another table:
Come back with any questions about this.
Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP UK (and classic 1967 Morris Traveller driver)
