Creating A Link to a specific form response

[MarcRohde]

I'm trying to figure out if there is a way to format a linke to a reponse in MS Forms.  I would like to add a 'click here' type of link to an e-mail flow each time a response is submitted.  I don't really need to emebed the detail variables into the message since the users who need the data will all have access to the form responses.

[Brad_Groux · Accepted solution]

The best way to have full granular access to all Form responses is to create a SharePoint List when a new response is received, and call the corresponding List Column for that item into the Email step(s). You could quickly start with the Record form responses in SharePoint template, and then include a link to the List item in the Send Email action.

For a more streamlined approach, you could also use the Send an email to responder when response submitted in Microsoft Forms template. I personally like having an easy to access, read, and use answer repository, which is why I record all of my Form responses to a SharePoist List. As an example, if in the future you want to expand your Flow based upon answers provided in the form to route the answers to only those it is specific to, you could add use conditions with logical expressions and parallel branches. 

Further reading if you do choose to expand:

• Add a condition to a flow
• Apply to Each
• Use expressions in conditions to check multiple values
• A Visual Explanation of Flow Logical Expressions (blog post)

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[RobElliott]

Hi @MarcRohde , there's no way to add a link to the response itself although you can add all the questions and answers in the response into your email. If you saved the response to a SharePoint list then you could add a Link to item which would open the list item in SharePoint. But you can't link to the actual response.

Rob
Los Gallardos

[Brad_Groux]

---

@RobElliott wrote:

Hi @MarcRohde , there's no way to add a link to the response itself although you can add all the questions and answers in the response into your email. If you saved the response to a SharePoint list then you could add a Link to item which would open the list item in SharePoint. But you can't link to the actual response.

Rob
Los Gallardos

---

The Forms connector has rather limited functionality, mainly because Forms itself does. In a Form's responses, you can only see the basic metrics that are captured as a whole. Currently there is not a unique API-based record that you can call and access from the Forms website. 

Currently you can view an individual response from the website, but it is several clicks in, and it actually displays as a custom view of the Form's design page (see red box):

If you would like a Microsoft Forms API, which would almost certainly expand the Connector's abilities, you can vote on the API for Microsoft Forms request on the Forms User Voice. If you'd like to expand the functionality of the Forms connector for Flow, please consider submitting it to the Flow Ideas page, where it can be voted on by other users, and tracked and commented on by the Flow team.

One reason that Microsoft likely won't develop an API for Forms, is that it is meant for Office 365 customers - who have access to SharePoint. So, developing an API for Forms would be re-inventing the wheel as it would be nearly identical in scope to the API for SharePoint Lists. Those same responses can also be sent to any data source (SQL, Cosmos DB, Excel, Google Sheets, etc) - so think of Forms as a data gathering tool, not a data storage or access tool.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

[MarcRohde]

Thanks,

I was looking deeper at the link that gets sent to the respondant and the format is pretty simple and could be recreated but what looks like is missing from the service call in the flow is the ResponseGetToken

https://forms.office.com/Pages/ResponseDetailPage.aspx?id={form id}&rid=5&GetResponseToken={request token}

I know the form id, the request ID is in the dynamic date but the get token kills it.

[Clifferton]

I think I found a way to do this.

the link that displays to a form owner when they manually access the responses looks like this.

https://forms.office.com/pages/designpagev2.aspx?lang=en-US&origin=OfficeDotCom&route=Start&sessionid={SESSIONID}&subpage=design&id={FORMID}&analysis=true&tab=0&topview=SurveyResults&qid={QIDWHATEVERTHATIS}&ridx={ID-1}

You can carefully trim that URL to something like this.

https://forms.office.com/pages/designpagev2.aspx?lang=en-US&&subpage=design&id={FORMID}&analysis=true&tab=0&topview=SurveyResults&ridx={ID-1} 

Then you only need to

1. replace {FORMID} with the form Id (without curly braces) and,
2. replace {ID-1} with the ID number in the responses excel sheet minus 1. So to see responses for ID 3, enter 2 in the url.

Flow would look something like:

When response submitted

Get response details

Get data from excel

create string variable

compose (to create url and store in the string variable)

Send email with composed link.

[smurray]

Thank you Clifferton!

Your response worked perfectly for us. I used the 'Initialize Variable' step at the top of the flow, then I put the flow ID there and use that variable to fill it in for all later steps (makes it easier since we are copying this flow to use for other forms). I then used the 'Initialized Variable' step again and have it fill the response ID from the trigger. After that, I use the 'Decrement Variable' step to lower it by one. I then used both variables in the link you provided above and it works perfectly.

[Clifferton]

Awesome! I'm glad it worked for y'all! 

[CROKat]

Hello, I'm trying to accomplish this but I don't see a "get data from excel" option in the flows. Could you help me figure this out? I have very basic knowledge of Power Automate. If possible could you post an example of this flow?

[Cliffferton]

This page has a bunch of good information on how the Excel connector works.

I haven't built a flow like this one and it kind of depends on exactly what you're trying to do.

You could List rows present in a table if you want all the data from the whole table.

Or if you know what row you want, you could use Get a row.

In either case, the number in the ID column is what you'll need to modify to display the correct response.

[HigoChumbo]

Clifferton's solution worked for me.

Just note I had to subtract 1213 instead of 1. It seems to be related to the row number the item is at in the Excel file with all the responses you get in the "Open in Excel" button from the form's main page.

For some reason in my case we only had some 500 items but the ids started in the 1200s, so for instance the item with Response ID 1640 was in the row 429 and the "ridx" i had to put in the link was 427. I guess it was accounting for a couple header files or something.

[Cliffferton]

The ID column is sequential no matter what data exists in the excel sheet. If prior responses were deleted from the excel sheet, new responses would continue to come in with higher ID numbers.

[goncaloperes · Accepted solution]

The URL to access a given response is something like this

https://forms.office.com/pages/designpagev2.aspx?lang=en-US&&subpage=design&id=FORMID&analysis=true&tab=0&topview=SurveyResults&ridx=RESPONSEID

When one accesses a form, one should be able to get the ID from the URL. In the following image, one will see how to get it from the response collection screen, but the ID appears in more views.

Use that ID to update the link above in the FORMID.

On another hand, considering that anytime one deletes all the answers that were submitted in a given form, the ID of the response in the form will not restart, but the ridx in the URL always starts from 0, for most cases it is convenient to have a variable named responseID.

Given that, I would recommend the following flow:

1. When a new response is submitted
2. Get response details
3. Initialize variable: responseURL (String)
4. Initialize variable 2: responseID (Integer)
5. Set variable
6. Set variable 2
7. Send an email (V2)

If one has deleted various answers and the ID of the Response in the form is 7, considering that the ridx in the URL starts at 0, add the following expression

sub(int(triggerOutputs()?['body/resourceData/responseId']), 7)

If one didn't delete any answer, considering that the ridx starts at 0 and the ID of the first Response is 1, then one can use the following

sub(int(triggerOutputs()?['body/resourceData/responseId']), 1)

And then in the Set variable 2, use the following expression

concat('https://forms.office.com/pages/designpagev2.aspx?lang=en-US&&subpage=design&id=FORMID&analysis=true&tab=0&topview=SurveyResults&ridx=', variables('responseID'))

 In the email body, then one can do something like this:

[KES2]

Hi Clifferton - I'm a bit of a power automate newbie, but I'm figuring it out pretty well. Would you be able to supply images of exactly what to insert into the fields for these two steps: 

create string variable

compose (to create url and store in the string variable)

Thanks!

[angelwing350]

Hi goncaloperes,

I tried your method and everything worked except when the email is received, the link gets an error that the form doesn't exist. I have attached a view of the process as it is now. Can you help?

[Clifferton]

KES2,

Sorry for the late reply. Here is a page on using variables in Power Automate. You need to "initialize a variable" first. In that step, you'll name the variable and choose the type of variable (string).

The compose step is a bit more difficult to explain, but if you look at @goncaloperes's post right above yours, you'll see a clearer explanation.

In the future, if you have a question that does not fit the topic of a discussion, such as how to use variables, you should probably start a new thread.

~C

[Clifferton]

Angelwing,

Your images don't show where or how you modified the URL to create the link to the form responses. If you include more detail there, we might be able to help.

[Amira703]

I'm not very experienced with initializing and setting variables. Are you able to open those steps to show the code inside?

The way I'm doing it must be wrong since the URL that results defaults to the first response vs. most recent. The ridx number increases by one each time, but how to do I code it to go to the most recent ridx?

[kinnett]

I have tried this multiple times and keep getting the error- "enter a valid integer".  Can you clarify for me what goes in each of these boxes?   I assumed this went into "initializing variable 2", but I keep receiving the "enter a valid integer" error

3. Initialize variable: responseURL (String)
4. Initialize variable 2: responseID (Integer)
5. Set variable
6. Set variable 2

[Clifferton]

@kinnett You can do the whole thing with the compose action too, rather than using variables. I used variables because they were easier to use in my multi-level flow. Using compose simplifies the whole thing quite a bit. You could even do it in one compose action, but I think it's easier to break up math actions from string actions.
Here's a simple example.

Inside the subtraction compose action, have the following expression which adds negative 1 to the response ID. @Amira703 If you just grab the response ID from the dynamic content you get from the Get Response Details action, it will be the one that was most recently submitted.

add(-1,triggerOutputs()?['body/resourceData/responseId'])

Inside the Compose URL action, I have this input. This prevents you from having to muck around with concatenate expressions. The dynamic content here is just the output from the subtraction compose action.

Then in the email, I find it easier to create the link within the HTML code instead of trying to get dynamic content into the WYSIWYG editor. The dynamic content here is the output from the URL compose action.

Then the URL is actually a link in the email so folks can just click it instead of having to copy and paste.