MS Forms Weighted

Brass Contributor

We have various surveys that we send to client using an external survey provider. I am investigating the ability to satisfy our needs by using MS Forms. One requirement we have is the ability to have a weighting tied to our questions as I have specified below. We would like to capture the scoring value in the Excel document behind the Form for easy tabulation. Can someone guide me as the whether this is possible? Thanks.

 

Question: How was the quality of service you received?

1. Superior (worth 100)

2. Good (worth 80)

3. Average (worth 60)

4. Poor (worth 40)

5. Terrible (worth 0)

 

6 Replies

Hi Chris (@Chris_Mancini) it's not possible with Forms on its own, but you could have a simple flow in Power Automate to save the weighting number (and any of the other answers in your form) to a different spreadsheet. The result is shown on the attached video.

 

The flow is shown below and triggers whenever a new form response is submitted. The first action is to get the response details, then we initialize a variable to hold the weighting score. Next, a type of condition called a Switch is added that looks at what the answer was in the question and then for each case applies the appropriate weighting to it and set the variable value. Finally it adds a row into a table in Excel using that variable value:

1-Flow.png

 

2-Flow.png

 

3a-Flow.png

 

3b-Flow.png

 

3c-Flow.png

 

4-Flow.png

Come back with any questions about this.

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User

@RobElliott Thanks for your response. That is pretty much what I thought would need to be done. I was thinking a list in SP that contained each question with the appropriate weight. When the response was received, the Flow would check the list, get the weight, and update the response record which would have a hidden question that related to each real answer. Thanks again

@RobElliott I see you responded to this a while back. I am having some trouble with the weighted scores. I followed your instructions and did a weighted score for answers, for example, High Risk should equal 5, Medium Risk should equal 3 and so on. The information transfers to Excel from forms but it does not show the values, only the words like High Risk etc. 

 

Not sure what to do at this point. I may be doing something wrong in the Switch section. I am using a Likert Scale question in Microsoft Forms. 

 

Thanks in advance for your help!

@RobElliott  Hello, I get the message below for the table

table error message.png

 

I don't know what you're trying to bring in from Excel or where that is stored, but it hasn't found the table in your spreadsheet.

@alexelizondo @RobElliott I have the same concern, only the value is shown on the spreadsheet, not the score.