Write form data in Excel via HTTP request

(0) Share

Report

Posted on by Frankisback

Hello experts,

I'm a beginner in Power Automate and Flow and also new in the community.

I guess for most of you this is a quick win, but I'm a bit lost.

Would be great to get some ideas how to solve my issue.

I have a Wordpress form. Following JSON will be send after clicking "submit":

{
 "$content-type": "application/x-www-form-urlencoded",
 "$content": "cG9zdF9GVzdYWpheD0w",
 "$formdata": [
 {
 "key": "post_id",
 "value": "001"
 },
 {
 "key": "post_text",
 "value": "testpage"
 },
 {
 "key": "timestamp",
 "value": "2023-09-14T21:55"
 },
 {
 "key": "firstname",
 "value": "Userfirstname"
 },
 {
 "key": "name",
 "value": "Username"
 },
 {
 "key": "e_mail",
 "value": "test@test.com"
 }
 ]
}

My goal is to insert the data into a new excel line in a table. The table has the structure of the key values pairs already.

I'm using Flow.

In my flow I currently use a HTTP request, (I use the generated POST URL in my form) and I use the Excel action: add line to a table (sorry I use not the englisch version, so maybe it's a different name). I have tested a lot of different configurations but without success...

Would love to get some ideas how I can solve that

Thanks so much, cheers!

#frank

Categories:

Building flows

I have the same question (0)

All responses (10)

Answers (0)

Sort by

Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hello,

Sorry I am not following. You say that, that JSON will be sent after hitting submit. Do you mean it will be sent to the Flow and you want to Parse the JSON and write the Key/Value Pairs to the Excel table?

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Frankisback 10 on at

Like (0)

Report
Copy link

Link copied!

Hi Michael,

sorry for my bad description. But it is exactly like in your assumption.
The output of my Wordpress form after clicking on submit is the shared JSON.
But I'm not able (with my current knowledge) to parse that in FLOWS to get the key/value pairs out of it. You know later in my Excel file the key=column name. And I need just the values next to each other in one line...

All the best,
frank

Was this reply helpful? Yes No
Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi

Assuming, in this case that the schema is the same format (even if there are will be more key/value pairs, the overall schema is the same).

I created this sample Flow
Step 1: a Trigger where I pass in the string text you have

Step 2: I use Parse JSON to turn it into a JSON Object. Please Note here. I have leveraged the data you gave me as a Sample. If the format will change, you will have to deal with that.

Step 3. I use a Select, to pull out the Key Value pairs from $formdata
body('Parse_JSON')['$formdata']
And Key and Value are just Dynamic values from the Parse JSON

Step 4. I use a Compose to turn it into a simple JSON instance.

And here are the actual steps (pre-run)

Now all you need to do is write it to Excel

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Frankisback 10 on at

Like (0)

Report
Copy link

Link copied!

Hi Michael,

so just for my understanding. In step 1 you use a "Manual Trigger".
I guess this is the replacement for my "HTTP request trigger". (seee below)
I see the code you inserted into the schema part is different to what I have mentioned in the post, right?
There is a section with text and some attributes like title and x-ms-dynamically-added. ...
Is it possible to get the complete code? I will copy that to my http request schema then.

So currently I use the JSON in my first post in the http request, as schema (see screenshot) but I guess this is wrong.
In case I get you Schema code, I'll insert that one and give it a try.

Looking forward to get it solved soon.
Thanks so much Michael!

Was this reply helpful? Yes No
Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hello,

No, this is just a sample. The trigger was just how I got the data in. It's the stuff after that that is important.

Are you making an HTTP request, in your Flow? If so, the output of THAT action is what you needed to share with me, which I am using in my actions. I used what you provided.

Think of it this way. The Trigger doesn't matter at all zippo. It doesn't even matter what steps you take BEFORE you have whatever the final JSON output will be from those steps, but what is important is the output of those steps. Thats what I need.

I am not building the whole flow. I am building a way to convert what you gave me into a usable format for what you want to do, write to excel.

So, if you are making an HTTP request in your Flow, then you need to share with me the Output of that. If the Output of that was what you already gave me. Then you can leave your trigger however you like, but make sure the output looks like what you gave me. Then plugin the steps i gave you (minus the Trigger), after that and use the content you provided to me as the schema.

Make sense?

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Frankisback 10 on at

Like (0)

Report
Copy link

Link copied!

Hi Michael,

so when I just use an empty http request action and I do a test, I should get as output the data which you need, right? So it's like that:

When I test this it looks like that:

The details I send you already in a private message.
Thanks so much for getting me on track.
Best, frank

Was this reply helpful? Yes No
Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!

Looks good to me. at least from what I see yeah

Was this reply helpful? Yes No
Frankisback 10 on at

Like (0)

Report
Copy link

Link copied!

@FLMike that's good!
Ok next is the parse JSON part.
Is it possible that you send me (based on the info you got in pers. message) the content & schema part of the "parse json" action in a private message?
Because for field content I have as "dynamic content" just TEXT, HEADER and PATHPARAMETER in case I did not fill any schema in my http request.... just to be sure I have it right afterwards...

Would be so great.

by the way, can I send you some donation in case you get it solved?
The stuff is for a small non profit organization 🙏

Was this reply helpful? Yes No
Michael E. Gernaey 53,974 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hello,

Earlier you said when you do it empty for the Trigger, you get this Body
That's what you want as the INPUT the Parse JSON

Cheers
If you like my answer, please Mark it as Resolved, and give it a thumbs up, so it can help others
Thank You
Michael Gernaey MCT | MCSE | MCP | Self-Contractor| Ex-Microsoft
https://gernaeysoftware.com
LinkedIn: https://www.linkedin.com/in/michaelgernaey

Was this reply helpful? Yes No
Frankisback 10 on at

Like (0)

Report
Copy link

Link copied!

Hello @FLMike ,

ok I did the changes in the "Parse JSON" action.
Now I get an error (and I remember, that I got that in one of my first attempts as well)

Seems that this type "x-www-form-urlencoded" cause some issues...
Was this content type working for you in your example? Any idea how to fix that?
Thanks so much, frank

Was this reply helpful? Yes No