Solutons Lounge

How to Create Exam Marker with Power Automate and MS Forms

TPWAdmin


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

  1. Mapping the Forms API
  2. Building Validation Script
  3. Pulling it together in a Flow
  4. 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
Enter fullscreen mode

Exit fullscreen mode

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}
Enter fullscreen mode

Exit fullscreen mode

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) {
Enter fullscreen mode

Exit fullscreen mode

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
Enter fullscreen mode

Exit fullscreen mode

The script then:

Loops over every question

aQuestions.forEach((item, i, arr) => {
Enter fullscreen mode

Exit fullscreen mode

Finds the question from within the response (users answers)

const sAnswer = aAnswers.find(q => (q.questionId == item.id)).answer1
Enter fullscreen mode

Exit fullscreen mode

Checks the type, as Choices are different from text inputs

 if (item.type == "Question.Choice") {
Enter fullscreen mode

Exit fullscreen mode

Converts questionInfo from string to object

Const oQuestInfo: intquestionInfo_Choices = JSON.parse(item.questionInfo);
Enter fullscreen mode

Exit fullscreen mode

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);
Enter fullscreen mode

Exit fullscreen mode

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);
            }
Enter fullscreen mode

Exit fullscreen mode

Then we update points


            if (bCorrectAnswer) {
                iPoints += oQuestInfo.Point
                iPoint = oQuestInfo.Point;
            } else {
                iPoint = 0;
            }
            iTotalPoints += oQuestInfo.Point;
Enter fullscreen mode

Exit fullscreen mode

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
    }
Enter fullscreen mode

Exit fullscreen mode

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:

https://wyattdave.github.io/PowerPlatform-Certificate/MKM/MKM%20-%208-dbb26950-467a-4c9b-9e21-2f633b4b4766.pdf

https://wyattdave.github.io/PowerPlatform-Certificate/MKA/MKA%20-%202-7a0fde98-ba6b-42ef-9110-bc54a8ad1899.pdf

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.



Source link

Exit mobile version