These instructions are created thanks to Al Friend from CastleBay school in Barra and the #TeamMIEEScotland team where this was shared.

This uses a Microsoft form to add an entry into a shared Excel spreadsheet.  The form includes a rating question asking how the pupils are and if the mark is 3 or 4 out of 10 then it sends an email is sent upon submission to certain staff members.  If the mark is 1 or 2 out of 10 an email is sent upon submission to other staff members.

Our pupil care and support staff team, heads of years, deputes and senior leaders have access to the shared Excel file so can check the entries during the day and contact the young people or their families accordingly.

Instructions for creation.

1. Create the Microsoft Form .  We have found this only works with a personal form.  Power Automate doesn’t give the group forms as an option to choose from.

Here is the form we are using.  A mixture of multiple choice, a rating, long answer questions.

2. Create the Excel file that will store the data.  Ours in created in the documents storage in SharePoint in a Staff Microsoft Team.

• Create the headings.
• Insert Table to turn them into a table.
• Not all of the headings we have are used by Power Automate.

3. Create the Power Automate Flow.  Here is the outline of the Power Automate (Flow).  We will go through each section.

Step 1 and 2 shown below.

Name the Flow (1)

Choose the when a new response is submitted from Microsoft Forms (2)

For the condition int (rating) part put the cursor into the brackets of int( ) and then click on the green field that holds the rating value. and choose from the Dynamic Content tab.

The link to the spreadsheet in the email can be copied from the document library.  You can of course put in the pupil names if you wish into the email, we choose to make the email anonymous and ask staff to check the spreadsheet for the name.