PowerAutomate, Forms->Excel

So during RemoteLearning in order to be able to manage things, we combine our Grade 9 classes across three teachers into one class. Now, before everyone panics, that meant a total of 30 students in one “class” -= our sections are small and at least two of the three teachers were always in the class – but it meant when we were doing attendance using Forms, we wanted to make sure we only dealt with our own students.

We were using the Microsoft Social-Emotional Check-In that was shared at the beginning of Remote Learning : https://education.microsoft.com/en-us/resource/51059d22 – I made a copy of that Form in my own Form collection and created a sharing link for the students. I also added a Question 1 (not visible) asking who is their teacher.

So we shared that link at the beginning of each class and the students filled it out. But that meant there was one big Excel that had everybody’s data in it. Now, we could filter by teacher but you’d have to do it each time and it just wasn’t what the teachers wanted to do. So, PowerAutomate using a Flow to the rescue!

Flow is like IFTTT (“If this, then that”) which is a web-service that, if something happens it triggers something else to happen. With Flow, if something happens in Office365, then do something about it! So, if someone fills in the SEL Form, then push the information into an Excel spreadsheet. There, that’s my pseudocode 🙂

You can find Flow with all the other PowerAutomate options by visiting http://flow.microsoft.com and signing in with your Office365 (you can just go to http://office.com but if you haven’t used it before, it may be hidden under “All Apps”. Now, some PowerAutomate options may be turned off by your IT — for example, we can’t use the AI aspects of Office365 🙁 but it is unlikely that Flow will be.

So remember my pseudocode above? Here it is in Flow:

Exactly the same! If someone submits a response (box 1), get the response (box 2) and stuff it into an Excel spreadsheet (box 3 — it’s called “switch because it has to decide which teacher sheet to stuff it into).

Lets dig into it. If you have ever used coding like Scratch this is similar — you select options based on what you have. There are also a large number of templates you can start with. I started with an Automated Flow (but you can also have one that you deliberately call or one that is scheduled for a particular day/time). There’s a nice search, too, so you can filter by triggers (“I want to know what ifs I can have with Excel!”) and by name. So here, I asked for all the triggers for Form.

If I expand my Trigger in Box 1 – “When a new response is submitted”, it asks me what Form I want to use… since I’m logged in with my account, the drop-down shows me all the Forms I have (I have a lot — too many, in fact. You’re only allowed 200 and I had to get rid of some during Remote Learning). So it’s pretty easy to do this step. Pick the right form 🙂

Box 2 – this is likely the easiest one. Really, all we’re doing is telling Flow to get the actual data within the Form that the user just input and stuff it into a temporary variable. But, since this is “low code” you’ll notice there’s no assignment of variables or anything… it’s just holding it for you. Very simple!

Box 3 – okay, so SWITCH is a Flow option that lets you do different things based on one of your variables. For this, the variable is the teacher they selected in the Form question “Who’s your Teacher” and the switches between Case 1 (on the left), Case 2 (in the middle) and Default (I could have created a Case 3, but if it’s not the first two teachers, it’s got to be the third since there are only 3 options in the Form question!

This image has been cut-up to fit — it looks much more symmetric in real life.

If we expand Case 2 we can see what happens once we decide on the teacher.

So… if the Teacher is Ms. Thorman, then open up the Excel spreadsheet I have stored in our Teams Files (you can do this with OneDrive or Sharepoint Excel files too!) — then, which Sheet does it go in? Well, we defined a TABLE in the Excel spreadsheet so it would know where to go. And so each Sheet in the Excel has a table on it with the teacher’s name. The only tricky thing here is that when you define the Table, you highlight not only the first row with all your Headers but also the empty 2nd row (so that it fills that in and then creates a new empty row in the table for you).

Once you’ve specified the Table you created in the Excel spreadsheet it actually pulls your headers from your Excel table (i.e. make the Excel spreadsheet first! I just copied the Form’s Excel sheet headers and made 3 sheets) so all you do is drop in the Forms questions that the Flow collected in Box 2.

Now, repeat for the other two teachers in the other Switch Options. And you’re done!

There is a nice “Test” option in the upper right corner – you basically go into your Form while you’re sitting in Flow and fill it and submit and watch it go through. This helps you troubleshoot. Love it.

You do get a heads-up in your email that a Flow has failed — over the course of 3 months, we did have it fail once. A permission had expired and so I just clicked Update Permission and all was good.

Now the result is below. What I wanted to point out is that I added 2 columns afterwards that aren’t part of the Flow. They are instead an XLOOKUP result — I grab the student email (blurred in column 2) and use it to lookup their full name (blurred penultimate column) and the name of their Advisor (homeroom teacher). Since this is an SEL check in, if someone showed up “unhappy” we’d be checking in with them a lot further! (No one ever did and we did find out some great things about our students and how they were doing).