Extract excel cell text into Key value field in power automate

[Bennyk]

Hi,

I have the following power automate flow:

1. A FORM that feeds excel by nature. I am using Power automate that is triggered by this FORM (works good)
2. My 2nd step is "get a row" function. All fields are correct but the 'Key Value'  in which is a specific email address in the column D in the excel. Each new FORM fill is inserted automatically to a new Excel ROW. I really don't know how to fill that email text by formula to the Key Value field. If I type in the 'key value' real email address, the test is successful.   
3. Send Email to a pre-set addresses. In the body I put the 'respond ID' function that tells the attendees the ROW ID where the new FORM content is fed. Actually that's the FORM response ID. That works good too. 

All the flow works perfect and My only issue is item 2. I just wish to add either to the subject or to the email body, the email address of the person filled the FORM. That email is different in every new line in column D. 

[SudeepGhatakNZ]

@Bennyk 

Add the 'Get a row' action from Excel Online (Business) connector. Configure it to point to your Excel file and table. For the 'Key Value' field, you can use the dynamic content from your form submission. Choose the appropriate column where the email address is stored (Column D). Is that what you are trying to achieve. Perhaps some screenshots will help.

[Bennyk]

Thank you Sudeep for your reply. Not sure I understood your meaning of content from FORM submission. I think I passed that. Regarding the other suggestions, I am already passed that and all is working but.... the right formula to get to cell content with the syntax (email in my case) and copy it in the body or subject (whatever I choose). 

hen you asked: 'is that what you are trying to do?' The answer is yes. I am trying the dynamic function but couldn't find the one that really executes the copy paste (cell content to email). This is my flow. The 1st box is for the form. The 2nd box is where the issue blocks me. Below some screen shots. 

Respond Id works (that's the Form index ID for each new response). 

Also the email works, once I type the email myself in the key value. I tried the formula you see. I have no clue what was I doing. It didn't work. 

I will be happy if you can figure this and if you do, send me some screenshots of the formulas to pick. 

[SudeepGhatakNZ]

@Bennyk ,

It sounds like you're looking to streamline the process by automatically filtering the Excel sheet based on the email address submitted through the Microsoft Form. This can definitely enhance efficiency and accuracy. Here's a refined version:

I think after the Microsoft form submission, you aim to filter the corresponding Excel sheet based on the submitter's email address. Is that correct? Try this logic below:

[Bennyk]

Hi Sudip,

First I would like to apologize for the late replies. I am a field engineer and serve half of the time in the field with customers and half in the office so I am loaded :-). 

Your solution worked like a magic!!! (I would never thought of this solution) but.....

It worked for the test in which the FORM and the Excel were placed under my personal drive on One drive. I moved them both to the SharePoint (FORM to Excel requires to be at the same place) because the 'get row' functions can only point to SharePoint but the 'Get Response' function gives me only the list in my one drive and I can't point it to SharePoint. So it ain't over yet :-(. 

I'll explain what is the need: 

In the field, engineers need to fill RMA (return merchandise authorization). This is for sending information filled in the form to operation with details of the parts need to be replaced. Each FORM response creates new line in the Excel by many engineers in many locations and the email body I created in the power automate is with link to that Excel. I just wanted to add the engineer name or email that is the source of each ticket so Operations can tell whom to reply to later. The flow I run still sends me the 'RespondId' number from the form in my personal OneDrive. Any idea how to pint them to the same location? Below is the print screen of your successful input. 

[SudeepGhatakNZ]

Hi Benny,

Here are some questions:

1) "the FORM and the Excel were placed under my personal drive on One drive". What do you mean by that statement? The form lives in Office 365 cloud. It is the excel that can be inside a Onedrive folder or SharePoint. Get a row action can point to either Onedrive or SharePoint.

2) "Each FORM response creates new line in the Excel ". From what I can see you are only reading from the Excel not writing into it.

3) "The flow I run still sends me the 'RespondId' number from the form in my personal OneDrive." Your link to onedrive will only work for people with whom you have shared the file. You can move the excel to SharePoint and  share a link to SharePoint in the same way.

[Bennyk]

Dear Sudeep,

This is the reason I explained all so the need will be understood. In Office365, viewing FORM responses, there is a way to open it in excel, and that excel is created the moment you choose to open the excel, right? so there is no option to define the location of it, right? it's placed at the same environment automatically. Saying that, under my 365 account, I believe there is personal place that is not the same as in SharePoint like the document folder. 'Get Response' shows me only files that are in My One Drive and I can't redirect it to where I need, where as 'Get Row' shows me only files under shared SharePoint and I can't change that either. 

So The FORM writes to the excel and all I need is to extract the email or name, either from the excel or from the FORM. 

Is there a way to have function that extracts both ('ResponseId' and name/email) from the FORM?

If not, then how to resolve the source pointer of both functions to the same list. 

Hope this is clear. 

[SudeepGhatakNZ]

Are you available for a short call? I can take a look.

[Bennyk]

Hi Sudeep,

I will be more than happy to have a call. What will we use to perform such communication? 

[Bennyk · Accepted solution]

Hi,

Eventually I have built both FORM and the excel in the same location in my OneDrive and the formulas suggested here work perfect. I only had to set credentials access to the excel for members in the organization. 

Thank you so much.