Power Platform Community Forum Thread Details

Flow to save MS Forms responses in dynamically created Excel files

The goal is to look at the email address of the MS Form respondent, and then create a new Excel saved in a specific folder, based on the email address up to the @ sign, and then save the Form data in that newly created file.

If someone has already got their file created and saved in the folder because of a previous response, then the line of Form data should be saved in that existing file.

But I keep getting an error step at the last step, this is the entire flow -

Trigger:

When a new response is submitted (Microsoft Forms)
Form ID: [theform’s ID]

Actions:

1.Get response details

Form ID: [same as above]
Response ID: @triggerOutputs()?['body/resourceData/responseId']

2.Compose_1

Expression:
concat(split(outputs('Get_response_details')?['body/responder'], '@')[0],' - Form Responses.xlsx')

3.Get files (properties only)

Site Address: [a dropdown Sharepoint URL]
Library Name: [a Document Library]
Limit entries to folder: Folder Path: /****/****/Reports/Form Responses

4.Filter array

From: @outputs('Get_files_(properties_only)')?['body/value']
Condition:
@equals(item()?['{FilenameWithExtension}'],outputs('Compose_1'))

5.Condition_1

Expression:
length(body('Filter_array')) > 0

If true (file exists):
there are no steps here, it would continue to Step 6.

If false (file does not exist):

Get file content 1
Site Address: [a dropdown Sharepoint URL]
File Identifier: /****/****/Reports/Form Responses/Blank - Form Responses.xlsx

Create file
Site Address: [a dropdown Sharepoint URL]
Folder Path: /****/****/Reports/Form Responses
File Name: @outputs('Compose_1')
File Content: @body('Get_file_content_1')

6.Initialize variable

Name: headers
Type: Object
Value:
{ "ID": @{triggerOutputs()?['body/resourceData/responseId']}, "Submission time": @{outputs('Get_response_details')?['body/submitDate']}, "Responders' Email Address": @{outputs('Get_response_details')?['body/responder']}, "Keywords": @{replace(replace(replace(outputs('Get_response_details')?["I clicked on the appropriate column 'Keywords' under 'When a new response is submitted"], '[', ''), ']', ''), '"', '')} }

'[', ''), ']', ''), '"', '')} } -I added this part because the Keywords question on MS Form is a multiple-choice, so it could be a single value or an array.

7.Add a row into a table

Location: [a dropdown Sharepoint URL]
Document Library: [a Document Library]
File: [dynamic file name from Compose_1]
Table: Table1
Item: @variables('headers')
Problem:
The “Add a row into a table” step fails with a 400 “Invalid request” error, even after creating a new Excel file in Excel Online, ensuring the table is named Table1, and matching all column names.

When I test this, the flow succeeds until the very last step, where I get the Invalid Request message above. Quick notes - I've definitely saved a tbale named Table1 in the blank responses file (which is copied and renamed for new responders), the excel columns are all text.

I have no idea why its failing at the last step, so I'd really appreciate any tips or help!

Categories:

Process Advisor

Quick Links