So the objective was easy, I wanted a way to test users and automatically send them their results. I had no budget for an off the shelf option, and hey I'm a Power Platform developer, this should be easy. I could have gone down the Power App avenue, but that would be locked to one test and restricted to internal users. So I went with MS Forms because:
- It is easy to build
- Fully external facing
- Has quiz mode with timer
- Integrated with Power Automate
Well all except the last one, as it didn't have the functionality I needed, so I had to get creative..
Here's the sections I went through to make it
- Mapping the Forms API
- Building Validation Script
- Pulling it together in a Flow
- Form settings
Here's my test form, it's got 3 questions and it only covers
- Text input
- Multiple Choice 1 Answer
- Multiple Choice multi Answers
There are date, ranking, matrix and net promotion as well, but these cant have a right answer so I have ignored them.
1. Mapping the Forms API
Let's start with why the Power Automate connectors won't do, well I needed the answers, and there was simply no connectors for it.
There is also no official documentation for the API and little unofficial. So my approach was the browser network tab, spying and trying to understand the API calls used when completing and reviewing a submission.
By trial and error I found 2 key APIs
GET questions - this allowed me to return all the questions, with type, options and correct answers
https://forms.office.com/formapi/api/{tenantID}/users/{formOwnerID}/forms('{formID}')/questions?$select=questionInfo,type,id
GET responses - there is a get response details action but this returned the answers in an object. This meant it would be hard to make it dynamic, I needed an array of answers. The response API did that, but you could only return all responses (no filter). Fortunately there is the $top and $skip queries , with these I could skip to the last by using the response ID from the trigger (as the id is sequential).
https://forms.office.com/formapi/api/{tenantID}/users/{formOwnerID}/forms('{formID}')/responses?&select=id,responder,submitDate,answers,responderName&$top=1&$skip={id or response -1}
Perfect, I now had my questions and answers.
2. Building Validation Script
I could have tried to do something in Power Automate, but it would have been painful if not impossible. Fortunately we have Office Scripts.
My code was going to:
- Loop over each question
- Check type (choice/ multi choice/text input)
- Lookup answer from answer array
- Validate and add score
- After loop check to see if passed
The script would have 3 inputs:
- The array of questions with answers
- The response object (as this will be the 1 object from the responses array)
- The Pass score
function main(workbook: ExcelScript.Workbook, aQuestions: intQuestions[], oAnswers: intResponse, iPassScore: number) {
There will be a few variables used as well:
const aAnswers: intAnswers[] = JSON.parse(oAnswers.answers) // convert anwers striing to array
let data: intQuestScoring[] = []; // new array to pass back each question, answer, points and if passed
let bCorrectAnswer: boolean; // for each question flag to see if correct
let iPoints: number = 0 //total points avaialble for test
let iPoint: number; // points per question
let iTotalPoints: number = 0; // total points scored
let sCorrect: string; //correct answer
The script then:
Loops over every question
aQuestions.forEach((item, i, arr) => {
Finds the question from within the response (users answers)
const sAnswer = aAnswers.find(q => (q.questionId == item.id)).answer1
Checks the type, as Choices are different from text inputs
if (item.type == "Question.Choice") {
Converts questionInfo from string to object
Const oQuestInfo: intquestionInfo_Choices = JSON.parse(item.questionInfo);
Gets the possible answers for the question, filters to find right answer and then converts to string (as there could be multiple right answers it is always an array and to check multiple right answers we can compare against one string instead of multiple items).
const aChoice: intChoices[] = oQuestInfo.Choices;
const aCorrect: intChoices[] = aChoice.filter(a => (a.IsAnswerKey))
sCorrect = JSON.stringify(aCorrect);
Check for multiple answers or one. If one check first item in correct answers. If multiple answers split the answer given and then loop over them checking to see if they are in the combined right answer string.
if (oQuestInfo.ChoiceType == 1) {
bCorrectAnswer = sAnswer == aCorrect[0].Description;
} else {
const aMulitAnswer: [] = JSON.parse(sAnswer)
const aFindCorrect = aCorrect.filter(a => {
return sAnswer.includes(a.Description)
})
bCorrectAnswer = (aFindCorrect.length == aCorrect.length) && (aCorrect.length == aMulitAnswer.length);
}
Then we update points
if (bCorrectAnswer) {
iPoints += oQuestInfo.Point
iPoint = oQuestInfo.Point;
} else {
iPoint = 0;
}
iTotalPoints += oQuestInfo.Point;
Finally I sent the data back to the flow
const oReturn: intReturn = {
pass: iPoints >= iPassScore, //have they passed
points: iPoints, //points scored
passPoints: iPassScore, // required points to pass
totalPoints: iTotalPoints, // total available points
data: data // array of answers and correct answers for validation
}
The biggest challenge was building out the schema, as everything came in as strings I had to parseJSON them. So each nest would include a conversation to JSON, the relevant interface, and the relevant comparison. It took a bit of work to map the schema (Questions here and Response here if you want them).
3. Pulling it together in a Flow
I still needed the response trigger, but I didn't need the get details. I had 2 options for the API, Entra HTTP using
Forms API endpoint or SharePoint http (as forms under the hood is a system SharePoint list). As non premium I went with SharePoint.
Then pass the 2 http responses into the Office Script.
Once returned from Office Script I had a escape condition checking to see if passed, if failed it would email them and terminate.
Next I used the Microsoft Word template action (there goes the free license, though if wanted to a replace() on a html file would work) to make a certificate.
I then converted to pdf.
Finally email with results and certificate.
4. Form settings
There are a few key form settings that you need to set.
Practice mode - Off as this will allow them to see the answers
Show results automatically - I set as off as dont want then to be able to share answers with others but this one is upto you
Who can fill out this form - If you want to make it public set it to Anyone, but then you would need an additional question to get their name and email address
Set time duration - How long do you want them to complete (think how easy it is for them to Google/find answer so time has to be tight)
Hide Submit another response - Off as else it encourages them to brute force right answers
Allow respondents to save their response - Off as allows them to share questions for others to look up before taking.
There's more I could add, like a list to record everyone's scores, but for now this will do.
Also note this is definitely not foolproof, there are a few ways to cheat, but I won't share.
A demo solution and script can be found here, the form is here to copy.
Additionally I have spun up a cool demo. If you are a Power Automate or Power Apps pro you can have a go at getting my Most Knowledgeable Automator and Most Knowledgeable Maker certificates.
It even links to LinkedIn. It's hard (well I think so) and all technical, but free (while it lasts on my dev tenant). You only get one go and it only runs till 9th Feb. I won't share anyone's scores (but you can), here's my certificates:
Exam Links Below
Most Knowledgeable Automator
https://forms.office.com/Pages/ResponsePage.aspx?id=3j5saw2qaEKkb5a3YhsTqCpjufsvOohKv79KrXqMqB9UREI4RzkxSDZTNU1BVjRHUDlBN0FGMVRLMi4u
Most Knowledgeable Maker
https://forms.office.com/Pages/ResponsePage.aspx?id=3j5saw2qaEKkb5a3YhsTqCpjufsvOohKv79KrXqMqB9UMExURk5FNDRNMEowWEVXS0pFWDJUQUFaOS4u
Note I have noticed that the links don't work in the Forem app as it converts to lowercase and returns form not found, but works in browser and I wouldn't recommend doing the test on a mobile anyway
Good luck.
Top comments (4)
@wyattdave : Amazing mate... Love the framework and the idea to validate the learning based on organisation needs.
I too tried this type of flow, it was painful and not got desired output. Love it.
I could have tried to do something in Power Automate, but it would have been painful if not impossible.
I built something like this in PA, and I can confirm it was painful 😔
Awesome blog post. I would love to see true life prepared to walk, so please share more informative updates. Great work keeps it up. vcetests.com/