How to Create Exam Marker with Power Automate and MS Forms
Tags: powerautomate, powerplatform, certification, lowcode
Published: 2024-01-29 07:18:35

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._


![form](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/54ssugupz62nqz19fjv4.png)

## 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.

![network traffic](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6cng8nwhuujmsz6w7eq6.png)

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.

![office script](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pyq1p6zrzfgbtahbmdb6.png)

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](https://github.com/wyattdave/Power-Platform/blob/main/Exam%20Marker/ms%20forms%20questions%20schema.json) and Response [here](https://github.com/wyattdave/Power-Platform/blob/main/Exam%20Marker/ms%20forms%20responses%20schema.json) 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.

![sharepoint http requests](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/t1acno1t0pcz3z0nb3tj.png)

Then pass the 2 http responses into the Office Script.

![run script](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ohl460kc9dexpf0tzcgo.png)

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.

![create certificates](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/79d3tt6jknkzejce0g39.png)

Finally email with results and certificate.
![full flow](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/g2squuk710s7ekoz9gms.png)

## 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.

![form settings](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vu1e3bz4f9fe554i5ww1.png)

---

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](https://github.com/wyattdave/Power-Platform/tree/main/Exam%20Marker), the form is [here](https://forms.office.com/Pages/ShareFormPage.aspx?id=3j5saw2qaEKkb5a3YhsTqCpjufsvOohKv79KrXqMqB9UOEYyVDhHVDlKRkEyUUZSRjNJMUxBVDNUUi4u&sharetoken=SgVTiEmS7Guif5CjlL5V) 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:

![my linkedin certificates](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6snh1d1ydbpk30k7fyg4.png)

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](https://forms.office.com/Pages/ResponsePage.aspx?id=3j5saw2qaEKkb5a3YhsTqCpjufsvOohKv79KrXqMqB9UREI4RzkxSDZTNU1BVjRHUDlBN0FGMVRLMi4u)
Most Knowledgeable Maker
[https://forms.office.com/Pages/ResponsePage.aspx?id=3j5saw2qaEKkb5a3YhsTqCpjufsvOohKv79KrXqMqB9UMExURk5FNDRNMEowWEVXS0pFWDJUQUFaOS4u](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.