Answered

Form to Excel Data Correction for Choice Type

(0) Share

Report

Posted on by Hack-7

226 Moderator

I have been working on implementing a legacy form for our department into a new Power Automate workflow that will insert rows into an Excel file in a SharePoint Folder. My lack of experience with Power Automate is where I am having problems.

My issue is on forms when using a Choice - Multiple Answer question the data is inserted as: ["John Smith"] or ["John Smith", "Jim Carey"], etc.

I am doing the following in the Flow:
- When a New response is submitted

- Get Response Details

- Add a New Row into a Table

I just want the entries as comma separated and not wrapped in [] or "".

What is the easiest way to have Power Automate collect the data and insert it as needed?

Categories:

General topics

I have the same question (0)

All responses (6)

Answers (1)

Sort by

michael0808 304 on at

Like (0)

Report
Copy link

Link copied!

Please try the following:

Hope this helps!

Was this reply helpful? Yes No
Hack-7 226 Moderator on at

Like (0)

Report
Copy link

Link copied!

@michael0808 the logic behind the suggestion makes sense, but "Test Input" would be coming from the MS Form entry.

When it does that step as an Array, I get an error.
"BadRequest. The variable 'formData' of type 'Array' cannot be initialized or updated with value of type 'String'. The variable 'formDataQuebec' only supports values of types 'Array'."

Was this reply helpful? Yes No
Verified answer

michael0808 304 on at

Like (0)

Report
Copy link

Link copied!

This was a simulation of the array coming from Forms in the following array format:
["John Smith", "Jim Carey"]

You are then going to essentially concatenate those array values into a single string using Join()

Edit: It appears that Forms sends the data in a format that cannot be read as an array. You will need to read this as JSON and then use the Join action. (Change the red text to your response.)
json(outputs('Get_response_details')?['body/r3b2c3e47db204a3eb1b41ec919d5e5d8'])

Test Run:

Please let me know if this helps!

Was this reply helpful? Yes No
Hack-7 226 Moderator on at

Like (0)

Report
Copy link

Link copied!
I ended up using this to get the end result I needed:
replace( replace( replace(, '[', '' ), ']', '' ), '"', '' )

Was this reply helpful? Yes No
michael0808 304 on at

Like (0)

Report
Copy link

Link copied!

Read my edited post. You will most likely prefer it 🙂

Was this reply helpful? Yes No
Hack-7 226 Moderator on at

Like (0)

Report
Copy link

Link copied!

Yes, that is what does the trick.
Cheers

Was this reply helpful? Yes No