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:
split(body('Get_response_details')?['r0b4a6f2bcc9f4e0286f65367c29f57d1'],'.,')
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.
Is there a solution to this using Microsoft Excel itself? I can’t figure out an array equivalent solution using Excel formula. Was hoping to find a formula equivalent so I can dynamically connect an Excel table to it and generate some pivot tables in real time without resorting to “Text to Columns”. And I don’t have access to Flow most importantly.
Great question. I didn’t want to try that, sounded too scary in Excel. Do share if you find something out. I know enough Excel to be fun.