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!
