Microsoft Forms Ranking Questions to Individual SharePoint Fields with Flow

In my previous post, I shared a winning combination of Forms, SharePoint, Flow, and PowerApps. In this post, we’re going to dive in deeper with the Flow used to save survey results from Forms into SharePoint.

Forms will save it’s data to Excel pretty easily, but sometimes certain question types are funky. In my use case, I was using Ranking questions, which are saved all jammed into one field in Excel. Not ideal. Instead, I wanted a unique column for each ranked result, so we could quickly analyze the results.

Ranking questions in Excel look like:

And I wanted something a wee bit more human, like:

Rrrrright? SO MUCH cleaner, SO MUCH more human!

Ranking questions are a great option, allowing users to specify a preferred order of the answers. This can be very valuable in understanding options in relation to each other. Take one of my questions for example:

We loaded up the answers with the full descriptions of the courses available so they can make an educated decision. Sadly, Forms doesn’t support any formatting in the answers, so it’s all text.

When this field is saved to Excel, it comes out like this:

Preparing to Impress – Description: Every day, we’re exposed to new skill sets that we should master. But what about the hidden “tough stuff” that no one talks about? For example, do you ever feel hesitant to ask questions in client meetings because you “should be” the expert? Do you struggle to re-focus meetings that go off track or seem inefficient? Or do you let your emotions get the best of you, and regret it later on? If you answered “yes” to any of these questions – join us! We’ll have conversations about how to manage expectations – across peers, Slalom leadership, your client – manage meetings, and show up as your best self. The best part – together, we’ll practice, create tools, and collect tangible takeaways to grow in your career.;Light a Fire with Storytelling – Description: Effective storytelling is a powerful communication tool, which you can use to build Slalom’s brand. Walk away with best practices and the confidence needed to engage others with compelling, memorable stories…and thereby help Slalom thrive.;Customer Love: Building Relationships – Description: Everything we do should build and strengthen trust with our clients. But trust isn’t enough.  We want to be Loved. Learn how account teams use Slalom’s Customer Love Scorecard to build deeper partnerships and elevate the impact we make.  An essential class for both your personal brand and Slalom Boston.;Executive Presence – Description: Effective communication and presence are necessary in order to influence others in our business world today. This module will be a refresher for some and an introduction for others to heighten our awareness of how we’re showing up and communicating and how it is impacting our audience’s perception of our executive presence.;

Yup, that’s it in Excel, one cell, all of the answers in the preferred order. Ouch! Is it accurate, yes, is it helpful, meehhh… I could work my magic in Excel and split and break it down to separate columns, but I didn’t want to, too much thinking. Instead…

Microsoft Flow to the Rescue!

In the past, I’ve share posting to Microsoft Teams when a new Form is submitted, which covers the Forms trigger and getting the response details. I won’t rehash that here.

Instead, I want to cover working with the ranking question type in Flow. As you see above, all of the answers are dumped into a single string field, a single column. We need to break it out and save them separately into unique fields.

Initialize the array

When we split a string, we create an array of values. Quick example: taking my four kids as a string: "Miah, Anna, Cal, Penny" and splitting them on the comma, gets me an array like: ["Miah","Anna","Cal","Penny"], each one being their own string. If you’re not familiar with the [], that just signifies an array, similar to separate columns in Excel.

The ranking question uses a comma ,​ to separate the answers. Here’s what the data looks like to Flow:

"r0b4a6f2bcc9f4e0286f65367c29f57d1": "Preparing to Impress - Description: 
Every day, we’re exposed to new skill sets that we should master. But what 
about the hidden “tough stuff” that no one talks about? For example, do you 
ever feel hesitant to ask questions in client meetings because you “should 
be” the expert? Do you struggle to re-focus meetings that go off track or 
seem inefficient? Or do you let your emotions get the best of you, and 
regret it later on? If you answered “yes” to any of these questions – join 
us! We’ll have conversations about how to manage expectations – across 
peers, Slalom leadership, your client – manage meetings, and show up as 
your best self. The best part – together, we’ll practice, create tools, 
and collect tangible takeaways to grow in your career., Light a Fire with 
Storytelling - Description: Effective storytelling is a powerful 
communication tool, which you can use to build Slalom’s brand. Walk away 
with best practices and the confidence needed to engage others with 
compelling, memorable stories…and thereby help Slalom thrive., Customer 
Love: Building Relationships - Description: Everything we do should build 
and strengthen trust with our clients. But trust isn’t enough.  We want to 
be Loved. Learn how account teams use Slalom’s Customer Love Scorecard to 
build deeper partnerships and elevate the impact we make.  An essential 
class for both your personal brand and Slalom Boston., Executive Presence - 
Description: Effective communication and presence are necessary in order 
to influence others in our business world today. This module will be a 
refresher for some and an introduction for others to heighten our awareness
of how we’re showing up and communicating and how it is impacting our 
audience’s perception of our executive presence.

Note the commas before the next answer. We’ll split on that. First, we need to create the variables which will store the array. I have 3 questions in my form, so I create 3 arrays.

Set array from ranking questions

Further down in the flow, after we confirm the person is going to the event (using a basic condition) we set these variables by splitting the question from Forms.

The split commands look like:

Don’t worry, you don’t have to write it all out, you can create this pretty quick in the dynamic content/expression dialog that appears. Click in the value field in the action, then in the dialog that appears on the right, click Expressions at the top, then start typing in split.

Type in the open parenthesis ( and then click Dynamic content and select the field to split. That will insert the ugly name for the field like I have above: body('trigger name')?['field id']. Flow will insert the closing parenthesis ) for you.

Click Expression again, then go to the far right, the end of the split command. Before the ), add your string to split by, in my example I added ,'.,'. Wait, what’s that period in there? As we discussed a little bit ago, ranking questions are stored using a comma to separate their answers. What happens when your answers have commas in them too? Then you won’t get a clean split.

Taking my earlier example of my kids, if the string has commas in the names, like "Miah, son, Anna, daugther, Cal, son, Penny, daughter", then splitting on comma would make an array with Miah and son being separated. Instead, I added a period at the end of each of my questions so I can separate at the question level. Normal people don’t write with a period then a comma, just bad grammar, so I knew this would be safe. So something like "Miah, son., Anna, daugther., Cal, son., Penny, daughter"which then can split to ["Miah, son","Anna, daugther","Cal, son","Penny, daughter"]. I hope this helps clarify a little. Back to flow.

Saving the answers to SharePoint

From here, we can then pick each answer from each array, and save to SharePoint:

Let’s break down what’s going on each of the Track fields

The value in each field looks similar to:

​​trim(split(variables('heartTrack')[0],' - ')[0])

Let’s start in the middle of what we see here.

variables('heartTrack') is the variable I created earlier. To load this, you’ll need to go to Expression like we did before and type in split( again, then switch to Dynamic content and select your variable.

[0], the first one, is taking the first string in the array. Arrays are zero-based indexes, which means counting starts at 0, not 1. So if there are 4 items in an array, you would pull them out using 0, 1, 2, 3. In my example, ["Miah, son","Anna, daugther","Cal, son","Penny, daughter"], 0 would return "Miah, son", 3 would return "Penny, daugther". Heart Track 1 uses variables('heartTrack')[0] to get the first preferred answer. Heart Track 2 uses variables('heartTrack')[1], and so on.

split splits my single answers, I don’t want the full description in my SharePoint columns, so I can split on - . After it splits on the dash, I want the first item, just the name of the course. So each track field will have [0] the second time.

trim removes whitespace, extra spaces, around strings, to ensure I’m getting a clean string in there.

For a quick review, here are some of the fields:

Heart Track 3: trim(split(variables('heartTrack')[2],' - ')[0])

Mind Track 4: trim(split(variables('mindTrack')[3],' - ')[0])

Hands Track 2: trim(split(variables('handsTrack')[1],' - ')[0])

That’s it!

The rest of the flow is pretty standard. See my previous post for this flow in context to the overall solution I built.

Probably the only other thing worth noting in this Flow is the formatted confirmation email it sends out. I cover how that works in this post.