How to copy scores from forms in excel?

[docschouwstra]

Im a teacher and i made a couple of learning excersises in microsoft forms. Because i have more than 100 students i want to track the progress in excel to be able to help where needed. I have build a flow in automate where it fills in that a student filled in a form. That makes it possible to make students at least do the excersises, but i have no clue what their scores are. To fill in the scores in excel i already attempted two things:

1. I downloaded the excel file from forms witch shows the results. In the flow i copy this information in another excelfile where i track the studentprogress. I put in a delay to make sure the new results have time to render in the file. It was working, but within an hour the file is not updating anymore and it stopped working.

2. A second option i thought of is to make a new excel file. In the flow i copy the answers from forms in the new excel file. I can make the excel file check al the answers and make it calculate the average score. Than i can copy the average score in a next step in the flow and copy it in the tracking excelfile. The problem with this solution is that it is a lot of work to put in excel. I have put in all the single answers of all the questions for all the excersises. This will take too much of my time. 

Does somebody have a sollution make this work with less labour? 

[RobElliott]

@docschouwstra it is possible to get the total score for each quiz response and calculate the percentage. The main work you have to do in the flow is to create a condition for each question and if it's answered correctly then increment a variable by 1. The example below was created for a lunch & learn at my company and saves the score into a SharePoint list (which I always prefer to Excel), but the principle is the same for Excel. I've used the classic designer in Power Automate which I still prefer to the modern designer.

This is the quiz which has just 3 questions:

This is the SharePoint list. I haven't added a column for each answer, just the the responder's email into the title column, a number column for the score and a calculated column for the percentage (=[Score]/3 and outputted as a percentage). But of course you could add the answers as well.

The overall flow looks like this:

The details are as follows:

So when the form is completed the flow will run and will populate the list with the  score and the percentage as shown on the attached video.

Rob
Los Gallardos
Principal Consultant, SharePoint, Forms and Power Platform, WSP Global (and classic 1967 Morris Traveller driver)

[docschouwstra]

Thank you for your answer! This can be a lot of work but i think this is for now the only option.