Unanswered

Form responses data collection to existing excel spreadsheet

(0) Share

Report

Posted on by dani_falso

I have a couple of issues with my flow and looking for some assistance.

1. How exactly does one set up the excel table correctly for Power Automate to input the data? I thought I had it right, but I clearly don't. At first I'd select the table range as most of the spreadsheet as I have formulas set up in some columns, however that just pushed new entries to the very bottom making it seem like nothing was there. Now I've changed it to the column headers but now I can't sort the columns because Excel thinks there's nothing in the table. How should the table be and how can I get the formulas to still calculate?

2. My Excel spreadsheet has data validation in the form of drop down lists. When my flow adds a new row to my spreadsheet, the data comes across with brackets around the option i.e. ["form option"]. Is there a way to remove this and for flow to simply input the information normally?

3. Some of my Form questions are set up for multiple answers, is it possible for this to be displayed in Excel with it looking like this ["form option"], ["form option"], ["form option"], etc?

Categories:

Building flows

I have the same question (0)

All responses (6)

Answers (0)

v-litu-msft on at

Like (0)

Report

Hi @dani_falso,

1. After you update the Excel table, you need to wait for the Excel file version update, generally a minute to serval minutes, then use List rows present in a table action with an order by.

2. Use the json() function and join() function could convert the option answer into a string, for example,

["Option 1","Option 2"] >>>> Option 1,Option 2

join(json(body('Get_response_details')?['r4e88c21471ea4cb2a0f15381fdffcf']),',')

3. Use replace function could do it, use '],[' to replace ',', for example:

replace(body('Get_response_details')?['r4e88c21471ea4cb2a0f15e4381fdffcf'],',','],[')

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
dani_falso 6 on at

Like (0)

Report
Hi @v-litu-msft , I've tried both of your suggestions and I can't seem to get either of them to work. When I do the list rows present action, the test fails and I get this error:

When I use the replace action
replace(body('Get_response_details')?['rb789ae35ce7947feb2c40ae404743ddd'],',','],[')
, nothing happens.

How do I get the data to input into an existing spreadsheet in order of date in line with current rows , rather than after the table?
Is there a way to have the form responses added into my spreadsheet and my formulas already in my spreadsheet still calculate? It's currently adding a row but ignoring my formulas

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report
Hi @dani_falso,

Could you please share screenshots of your Flow configuration?

What's syntax input in the list rows present action?

When I use the replace action replace(body('Get_response_details')?['rb789ae35ce7947feb2c40ae404743ddd'],',','],['), nothing happens. Could you share the run history? Input and output.

How do I get the data to input into an existing spreadsheet in order of date in line with current rows, rather than after the table? Answer: There is no way could directly do it, you could list all rows by using order by, then update back to the list.

Is there a way to have the form responses added into my spreadsheet and my formulas already in my spreadsheet still calculate? It's currently adding a row but ignoring my formulas. Answer: you could try that not update the calculate column when the update excel rows.

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
dani_falso 6 on at

Like (0)

Report

@v-litu-msftscreenshots of my flow are below

Here is the run history from the replace action

Can you please explain how I set up your suggestions in points 4 and 5? I'm not sure what you mean.

Was this reply helpful? Yes No
v-litu-msft on at

Like (0)

Report

Hi @dani_falso,

There is no need to loop through the response list cause response is only one, please remove the Apply to each action and try again:

For answer 4, please refer to this post:

https://powerusers.microsoft.com/t5/Building-Flows/Guidance-for-building-a-new-flow-how-to-add-a-row-into-a-table/m-p/589687#M77604

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
dani_falso 6 on at

Like (0)

Report

@v-litu-msft thanks for that, the apply to each action automatically appeared when I selected the Response ID in the get response details action. I've removed it now however the replace action still does nothing - see run history below

Am I missing something in the expression?
replace(body('Get_response_details')?['rb789ae35ce7947feb2c40ae404743ddd'],',','],[')

The list rows present action still fails when I run a test. I don't think this is going to work for what I need

Was this reply helpful? Yes No