How to add row into table of a file depending on the survery response e.g Name of Tour

(0) Share

Report

Posted on by katiemol

Hello community,

Really would appreciate any assistance in this. I am trying to get responses from a customer survey on to the corrosponding excel file for their particular tour they went on. We have 1000s of files for the tours depending on the dates. I have tried the below:

Step 1: Filter the Array

You have already filtered the SharePoint files based on the customer’s tour code, so you have a list of matching files.

Step 2: Add a Condition to Check for Empty Array

This step checks if the array returned by the filter is empty or not, using the condition you’ve set (body is equal to empty()).
If the array is empty, you'll send an email notifying that the code was invalid.
If the array is not empty, you'll proceed with the next steps to append the response to the correct file.

Step 3: Append Response to the Correct Tour File (This is a new set of actions)

This happens after the condition check in the "If No" branch, meaning the array is not empty (i.e., you found a valid tour code).

Add an "Apply to Each" action:
- In the If No branch, click Add an action and select "Apply to each".
- In the "Apply to each" action, for the "Select an output from previous steps", choose "Body" from the Filter Array action. This will iterate through all the matching files.
Get File Content:
- Inside the "Apply to each", add a new action: "Get file content".
- For the File Identifier, use the "Identifier" dynamic content from the Filter Array step. This ensures you’re fetching the correct file that was matched by the filter.
Add a Row into a Table:
- Add the action "Add a row into a table" to append the response to the file.
- For the File field, select "File Content" from the Get file content action (this points to the content of the file retrieved).
- For the Table, select the table inside the corresponding tour file (make sure the table is already set up in the file for adding rows).
Map the Form Responses:
- Map the form responses (from the customer feedback) to the columns in the table. Use dynamic content to pull in the responses from the survey and match them with the appropriate fields in the table.

But when I try to add a row into table, it won't allow as I cant specify what table it is as it depends on what tour/date the customer has been on. I am also then trying to get the files on to one master spreadsheet after this step. If anyone can help that would be so great! Thanks so much.

Categories:

Building flows

All responses (3)

Answers (0)

Sort by

Suggested answer

Gopala_Krishna 1,495 on at

Like
a
(1)

Report
Copy link

Link copied!
This might be happening because Power Automate needs a consistent table name in the "Add a row into a table" operation and cannot dynamically adapt to the situation whereby the table names differ across the files. Below is an elaborate solution to cater to your issue, including gathering responses in a master spreadsheet.

Validate and Organize the Structure

Ensure each file contains a table with a consistent name (e.g., Table1). This consistency is essential because Power Automate cannot dynamically determine table names.

Take assistance of Office scripts

If you feel table names vary across the scripts and cannot have consistency then you can design an office script like below that helps with identifying and copying the data from a table

Example office script shared below:

To Consolidate Data into a Master Spreadsheet

After updating individual tour files:

Add a Copy Data to Master File step:

  Use the Get file content action to retrieve updated files.

Use Append to array variable to collect rows from each file.

Use the Add a row into a table action and loop through the "Append to array variable" data to append all rows to a master spreadsheet.
Suggested answer

Mark Nanneman 993 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!

Assuming you are trying to add a row into an excel file that has multiple tables, you could add an action to list the tables present in your excel file, and then use a filter action to find the proper table based on dynamic values.

Here's a very simple example of how to add rows to different tables based on a dynamic table name input.

Step One:

Use a "Get tables" action to list all the tables in your excel file.

Filter the list of tables in your excel for the table name that equals your dynamic value--in this case I'm inputing the table name in the manual trigger for demonstration purposes. In your case you'd need to have some sort of additional mapping to know which table name (or id) to filter for based on values in your survey submissions.

Step Two:

Get the table id of the filtered tables.

Step Three:

Add a row into the table by dropping the "table id" you found after the filter into the Table input parameter of the "Add a row into a table" action. Compose the row you want to add in JSON format with each property being the name of the column you want the new value to be added to.

Demo:
katiemol 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your help so far.

Just to clarify for context, I am trying to add a row into the excel file depending on what tour the customer has been on based on their answer in the survey. For each tour there is an excel file which contains the tour code e.g BRT_BLRT Feedback - BBFLL50430. Each file has one table and I have named it TourResponses in every single file. In the customer survey there is a question 'what was your tour code?' An example is 'BBFLL50430'. Each tour and tour file has a code e.g BBFLL5 and the date 0430. The is questions in the survery for both so I don't know if we can also try and get the answers into the correct file in a different method based on the customer answers? We have 1000s of tour files, hence the need for automation.

For now, I've attempted to use filter array to retrieve the file from 'Get files (properties only)' where the File Name contains the 'tour code' response from the customer survey. I know there is room for customers to make spelling mistakes which is why I've created a condition that if the Tour Code answer is empty it will hopefully create an item in a list instead and I can manually find the tour code. I'm not sure if this is the correct way to set the condition. Ideally I'd like the condition to be if the customer spells the tour code wrong, it will create an item. But will get to that once I've managed to add a survey response to a row in the table for the correct file.

I've tested my flow with a survery where I have entered the correct Tour Code and it still has not added the row into a table with the responses . When I ask copilot it says 'The flow did not go through the apply to each because the filtered array was not empty, indicating that there were existing items that matched the criteria.'

Get files (properties only): The exact site address came up with an error when I copied the URl into Library Name so I put Limit Entries to Folder to get the exact file name, not sure if that is correct?

This is my filter array:

Condtion:

Get file content:

Add a row into a table:

Any help on this is much appreciated! Thanks so much!

katie