PowerAutomate, Forms->Excel Part 2

So last post I went over how I used PowerAutomate (Flow) to push stuff form Forms to Excel. But a colleague had a different question so I thought I’d go over that scenario here.

I have two grade 10 math sections, and I used the Microsoft SEL Form (from here) during Remote Learning for them, too. But that means I have TWO spreadsheets to look at. And I want to look at one spreadsheet with the classes’ responses on different sheets. Well, Flow can do that, too!

First, I should make up the Excel spreadsheet that’s going to contain all my info. I made it in my Math Faculty Teams FILES folder for my MPM2D course although I could have made it in my OneDrive or any Sharepoint site I had access to.

Then, I renamed the sheets “Section 3” and “Section 4” (I could have named them anything and if I had more sections I could have add more sheets).

In “Section 3” I put the headings from my Microsoft Form and then highlighted the row of headings and went up to INSERT and chose TABLE. That makes a special “area” on the sheet called “Table1” that Flow can find. Now, when you’re in Online Excel, you don’t get to rename the Table (yet) but you can always open it in Desktop Excel and you can rename it to something better than the sequential “Table1”, “Table2” you get in Online.

Now I repeat that with Section 4 — I put the headings in, highlight, and Insert the Table.

(Well, wait, Cal, how did you get it to say “Table 3”? Well I made two Tables and CTRL-Z undid them so that Excel thought it was on the third table. Sometimes, you have to be smarter than Excel…)

By creating a Table on a particular sheet, you’re going to be able to let Flow know to put the Form results on the correct sheet. That’s the key to all this!

Alright, so my Excel sheet is now ready to hold my data. So off I go to Flow (flow.microsoft.com) signing in with my school/work account. I click on MY FLOWS on the left of the screen and then NEW. From the dropdown, I pick “Automated from blank”. Now, I’m sure there’s a template but I’m doing this from scratch.

So a Flow starts with a trigger — what’s going to start this Flow? Someone answering my survey! So I add a Forms Trigger — notice it’s at the top of the list (it’s popular as a trigger). I give the Flow a name (“Send 2D3 to Excel” since my course is MPM2D and the Section Number is 3).

After I hit create I get the box for the trigger and it asks me which of my Forms do I need? I get the full list of all Forms in my collection and I choose the 2D3 Form we’ve been using.

What’s the next step? Well, I need to get the contents of the Form, so my Action is “Get Response Details” (Now, I’m a little annoyed that this isn’t automatic, because if the Form is triggered, then obviously, I want the contents, right? Well, apparently not… I have to be explicit.) So I choose GET RESPONSE DETAILS.

So the last step to add is actually take the Response Data and plug it into our Excel Sheet. So I click on the EXCEL ONLINE (Business) button and choose ADD A ROW TO A TABLE (you have to scroll down the list a bit in this pic). What’s interesting (well, to me) is that we used the “Update a Row” in a different flow… it will actually find the row with, say, a unique ID and change information in that row based on a Form/email. Quite cool. Anyways. Let’s keep going.

So in this Step, we need to know where we want to put the data. Here’s where our Excel comes into play; we’re going to find it again. Mine is in my Teams Documents library so that’s what I’ll choose from the first three dropdowns.

So I choose my Mathematics Faculty Team from the Dropdown … what’s listed in that Dropdown? My personal OneDrive, every Team I am a member of (Team “Files” tabs are really just “OneDrives”) and every Sharepoint Site I have access to (Sharepoint Sites all have their own “OneDrives”). So it’s a long list.

Then, within my Mathematics Faculty Team, there are a number of Channels, so when I go in to Documents (really, it should be called FILES) I can find my MPM2D Channel and the Excel spreadsheet I made above.

When I choose that spreadsheet, it automatically finds the Tables I created in the Spreadsheet. This is Section 3 so I choose Table 3 (when you create the Flow for Section 4, choose Table 4!).

This fills in the rest of the Flow box with all the columns in my Excel Table (which are just the Questions in my original Form!)

Now I go through and click the Lightning above each box (the CS person in me wants to call them “fields” but no one calls them that anymore) and choose the Form entry I want to put in that Excel column

I go through and do this for all the Form entries in the Form:

Where it says “Table5” it should say “Table 3”

And that’s it!

But I should test it … so I click on TEST in the upper right corner:

So I go off to my Microsoft Form and pretend I’m a student and fill out the form. And sure enough, when I check the Excel Spreadsheet, it’s on the Section 3 sheet! The Flow webpage shows me it completed successfully — if it had failed it would show a red mark on the box which it failed at so you can troubleshoot.

So, that takes care of the Section 3 Form –> one spreadsheet. Now, create the SAME flow for the Section 4 Form, but this time, push it to Table 4 (which is on Section 4 sheet in Excel). You can copy this Flow for any Form you want, sending ALL of your results into one Spreadsheet! Takes about 2 minutes to get this all done (well, another couple minutes if you haven’t made the Excel sheet yet.) You’ll have as many Flows as Forms you want to push into the Spreadsheet.

Hope that helps.

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

So … Flow should help you add some power not only to Forms & Excel but a lot of other different things. Take a look!

Card Sorts in Microsoft Whiteboard

So in this time of remote learning, I like to do activities in small groups that are self-checking so that I don’t have to rely on automation. We did a Clue (or Cluedo if you’re British) for our last unit review and this time for our Quadratic Formula mini-unit I thought I’d do a card sort.

Now, I admit… I put more work in this than necessary. I could have just made rectangular cards and had the students lay them in a circle. But I go down rabbit holes, so here is the drawn out process…

Step 1: I used Publisher to make my cards. Again, it would have been easier to rectangles but I used a trapezoid to make things fit together nicely. This let me do 20 questions that would work around an icosagon (for math geeks, I used Geogebra to make sure I got the angle right; while the drawing tools in Publisher are many & diverse, it doesn’t really have any angle measuring tools so I screen-shotted a 81Β° angle from GeoGebra and pasted it in front of my trapezoid on the Master Design Page, made it transparent and then lined everything up. Like I said, rabbit-hole.)

Step 2: I printed the Publisher file into OneNote before I re-arranged the answers. This made my Answer Sheet.

Step 3: I went back into Publisher and shifted the answers one card down (and the last one went to the first card, obv.)

Step 4: I printed the Publisher file into OneNote again. Now, I used Windows-Shift-S to screen clip the cards into their own image file. I put them all into a folder. If you haven’t used Windows-Shift-S to screen clip, give it a try! It’s awesome! So quick & easy! (Link to my use of it.)

Step 5 : I opened up SnagIt Editor and choose EXPORT and pointed to the folder of images — SnagIt re-saved them as transparent gifs (so that the pieces would fit together without overlapping — I should have been lazy and just left them as rectangles, but I got hung up on the icosagon.) Again — this step is completely unnecessary if I had used rectangles, or if I hadn’t been so finicky. Or I could have used any number of other desktop or online tools to make transparent gifs — there are lots! But often I find that people forget that there are ways to do repetitive tasks in a batch format with some software — never hesitate to google “batch <whatever I want to do>” and make your life easier! 20 transparent images in 5 clicks. Thank you Techsmith Snagit (currently free in Covid19 times).

Step 6a: I dropped them all on a Microsoft Whiteboard. — OR —

Step 6b: I dropped them all on a OneNote Page — my colleague hasn’t used Microsoft Whiteboard yet with her students so they are just going to line them up (in a really long line) on a OneNote page.

Voila! Card Sort!

If you’re doing this with Microsoft Whiteboard, keep one as your Source. Then, copy all the images in the pile and paste them into a new Whiteboard and share it with a group of students and let them play. Whiteboard works very slickly synchronously and you can use your fingers to rotate and swing the cards around on the page.

If you’re doing it with OneNote, a similar process. Keep one OneNote page as the Source. Then, create a Section in your CollabSpace for your students and paste a copy of the Source OneNote page as the top page in the Section. Create a Page below that with the name of each of your kids. You want each kid to do their own math work on a separate page (it avoids syncing errors). They can move the cards around on the top page. They have to line them up horizontally or vertically because there’s no free rotate in OneNote. Add another Section for another group (you can password protect Sections so that groups can’t see each other’s work).

Breakout Rooms in Microsoft Teams

So one of the challenges folks have is that there isn’t yet an easy way to assign students to breakout rooms so that they can work on a problem together. Each breakout room is a mini-Team with a shared chat space, a Microsoft Whiteboard, audio/video conferencing and a file space. If your school allows students to do Chat (and that may be a big if — we’re lucky that my school does) here is the way I create breakout rooms for my classes/projects. You can download the (Windows Desktop) Excel file here:

If you’re using a Mac or just Online, you’ll need to use the file below (as those versions don’t have all the functionality of Windows Desktop Excel):

If you’d rather watch a video, here are the steps in, well, video.

You should only touch things that are green. Of course, if you want to re-work this, feel free! You can always download another copy πŸ™‚

Now, I tend to use Visibly Random Grouping from Peter Liljedahl (link to pdf) in my classrooms — I roll the dice and whatever grouping I get, that’s the groups the students will work in (even if it teams up Evil Eddie with Dangerous Dan… although to be fair, my kids are all great and easy to get along with so it’s never too much of a hassle). The spreadsheet I use, though, will let you set the groups manually (or you can copy the random grouping to the manual space and then adjust as you see fit).

First, fill in your school email address and the number of students in the class (if you have more than one class, just copy the sheet and fill it up, too. Worksheets work independently.) Fill in the student emails and the names you want to use (first, last, nickname, whatever… it’s really just to help you distinguish who is in the group. It doesn’t go anywhere you don’t put it).

You can also fill in the Group Names — I *really* encourage good group names because they show up in your Microsoft Teams Chat List. I try to use some aspect of my course code or the project name or something that will be memorable with the least amount of thought.

If you want to do things manually, make sure the Randomize list Dropdown under Visibly Random Grouping is set to FALSE. You can set the number and arrangements of groups and the lists will fill in on the far right. Notice that the heading of the middle column will show you that it is “Manually Assigned”. You are automatically included in each chat but aren’t shown in the list.

If you want visibly random groups, change the Dropdown to TRUE. I’ve left the randomization visible only out of interest (it’s supposed to be visibly random) and the column is red so that you know not to touch it! Only touch things that are green.

If you want an initial random sort and then play with it (to separate Evil Ed from Dangerous Dan, I suppose) just highlight the red random sort and paste it into the Manual sort column and then adjust from there.

If you’re doing Visibly Random Grouping, the middle column of the Group Table will let you know that you’ve used Visibly Random Grouping. Again, you are automatically included in each group. If you want a different sort, just flip the VRG dropdown again — although VRG says you shouldn’t re-sort πŸ™‚

Regardless of whether you went with Manual or Visibily Random, eventually you’ll be happy with the groups.

What you really want is the link in the last column — copy that URL and paste it into a web browser and hit ENTER. That creates the Breakout Chat for everyone in the Breakout and they’ll see it with the Group Name (1) you gave in their Chat List (hence why you want to give it a good name!) and also along the top row. You’ll see the list of people in the team if you click on the little people icon in the upper left corner (2) and your initial message is ready to send in the lower chat box (3) … you still have to click Send though.

Now, what I do is copy and paste the whole table into my Planning OneNote. It lets me see the group plan for my whole class and gives me a quick way to jump into the groups in case I’m working. The link opens up exactly in that group’s chat.

You can also right-click the blue link in OneNote (or email or Word or wherever it is) and change the text to the Group Name to make it look better to the students (and yourself!). Funny enough, you can’t do it in Excel because there’s a character limit in Excel that’s not true elsewhere.

You can also go back into the group and remove a student (mouse over the student and an “X” will show up. You can also add people to the group if necessary.

Let me know how it goes!

Quick Poll in Microsoft Teams using the Forms Bot

In Teams, click on the APPS button in the lower left corner of the Teams App.
In the Search Box, type form and a card will show up with FORMS on it.
In the drop-down menu next to Open, you can add it to a Team, or a Chat. Once you choose one or the other, you get a list of all your Teams (or Chats) and can decide which one you want to add it to.
Now, go to the Team or Chat you added Forms to, and type @FORMS in the chat box. You can then click on the FORMS nametag that pops up.
It will give you a list of things you can do– in this case, Create a New Poll to tap on that!
You’ll get a pop-up window asking you to create the new poll … it’s strictly multiple choice and there’s no option for “Other”.
So I filled in an example…
The bot will then show you a preview of the poll.
Here’s what it looks like in the Chat window after you click SEND. Any participant in the Chat can respond and a running total shows up.
If you go to your FORMS on Office.com, you’ll see a card with the survey from Teams. Notice the icon in the lower right corner.
When you click in to the Form, you see that it’s not editable anymore!
And the summary you’re used to!

Delay Post to Microsoft Teams

So one of the things I see requested is a delayed-post to Microsoft Teams (like, you want an announcement to go out on a Birthday, say… like April 23rd for someone special?) That functionality doesn’t exist at the moment so I figured that PowerAutomate, Microsoft’s scripting engine, should likely be able to do it.

And it works! This surprised me… It took about an hour and it’s only a rough draft but I thought I should share the outline. The Flow comes in 5 parts as shown below, but I’ll dig into the pieces.

There’s a Microsoft Form that asks for the message you want, and when you want to post it:

When the user submits the Form it kicks off the whole routine — the Flow is activated and begins by concatenating the date and the time together (since that’s what the Flow needs) and puts it into the Standard form for Flow.

The Concat details are below… there is the Date field (which gets chosen from the Forms response list) then a SPACE (‘ ‘) and then the Time field (also chosen from the Forms response list) then a Z.

concat(body('Get_response_details')?[<Date field chosen from the list>],' ',body('Get_response_details')?[Time field chosen from the list],'Z')

Then, the Flow waits until the set date and time; you can plan a post up to 30 days in advance (although I haven’t tested more than a few hours because I’m impatient!). Once the time is up, the Flow continues with the actual posting of the message:

Now, the observant amongst you will notice that the Team & Channel are hard-coded. Unfortunately, right now the Team & Channel are based on the GUID (“big long number&letter name”) of the Team & Channel so the user would have to track that down. I’m looking to see how we can get that into a drop-down on the Form, but we’ll see.

Anyways, I’ll keep poking away on it. It wouldn’t take much for someone more talented to make this a universal Flow that anyone can use.

#remotelearning bona fides

So, I’m a classroom teacher — but I’ve also taught online each summer for the past six years, first in French (!) and then mathematics. Before that, I tutored students online through our government online chat/whiteboarding system. And I’ve been working in a 1:1 environment for 25 years. While not “paperless” as a goal, I try to make sure everything is digitized so I can find it, re-use it and distribute it. When people asked what I wanted teachers to learn, my response was always “to save time” — I don’t have a pedagogical drum to beat with other teachers, I only want to save them time so they can make the improvements that they want to make. So everything I mentioned will (a) save time (b) be simple & quick and (c) be free.

This will be Microsoft focused. But I’m a Gdocs user!? THAT’S OKAY. The teacher can use whatever they want to produce their content — we’re going to use Microsoft because, in a distance format, we need to provide the students with as much structure as possible, without cost, on any device. We have to make things obvious & clear with as many breadcrumbs as possible so that a student (and parent) left at home can follow along. We don’t want files, we don’t want folders — we want a book for them to work through, with outlines & links and formats. And that’s OneNote.


I designed the first OneNote ClassNotebook — I built it for teachers to maximize their work with students. It was DESIGNED with a distributed system where you didn’t always have wifi, you wanted rich & diverse multimedia options and feedback was a driving focus. It was meant to be driven by the teacher (I’m a high school math teacher so my pedagogical focus has never been discovery — I’m much more for guided instruction, good questions, and discussion) and having access to the complete archive of teacher & student work was essential. The OneNote ClassNotebook is the BINDER — the Trapper Keeper — older folks grew up with — it contained everything the student did in class, all the handouts, the class schedule, the review. And, the sections & pages acted as a time-organized map of the course from beginning to end.

Feel free to ask any question, either here or on Twitter @sig225 . As I mentioned in my last post, my motto used to be Aut inveniam viam, aut faciam but over time, I began to use I learn, I help others learn. And since my own March Break travels have been curtailed, I have time to work on things.

#remotelearning Introduction

You can’t tell what’s going to happen but it looks like many schools may experience some closures over the next few weeks or months, and so folks are considering how you can have students continue to learn while at home. I’m actually on March Break — and I’m catching up on my blog posting — so I thought I’d lend my expertise to this. Here comes a whole series on #remotelearning.

The Bar: students will need internet; really there’s no way around that without turning to paper and that has its own issues. But, so long as they have a smartphone, they can access your content, learn from it, share their learn, engage in discussions, receive feedback from you and others. How do I know that? Because of observations made around the world with student learning with devices. Is it the best case? No… but this isn’t a time to whine about not having a limo when you want to get to A & B.

I’d start by being proactive in getting ANY smartphone to a student — it doesn’t need a sim card so long as they have access to wifi, and again, that’s something that folks should begin to be proactive about. Look to Facebook, Kijiji, Craiglist and find used, older smartphones where you can just use wifi. Talk around the school; talk to other schools. Talk to the local private schools. Check out your school’s lost&found. Talk to the police; where do they put their turned-in phones? Be creative. My credo has always been Aut inveniam viam, aut faciam — I shall either find a way, or make one. And remember that smartphones are only one option — iPads, Chromebooks, Android tablets, Win10 laptops, heck even Win7/8 laptops. Desktops — students around the world are still using Windows XP and so can yours. It means they have to tie into their router but again, hopefully you have time to set & check this up. Anything they can use a browser on. Get them set up BEFORE your school closes, if possible, so students can test and try and learn using the device.

Any device ABOVE a smartphone is (usually) better. We really do want to have access to a picture-creating device. But when I discuss something in forthcoming blogs, I’ll assume they have at least the capabilities of a smartphone — everything else is ICING.

If you get brave, call in someone do use older laptops and re-install them with Linux. At least, students have to be able to get to a browser, so refresh the laptops with Linux, autoboot with a browser and let them go from there. Andrew Dobbie from Ontario, Canada, is a model for this. (Link)

More than 95% have some form of wifi access at home (link) but how do teachers, parents and students fill in for the missing 5% — there will be a blog post about that later. Your ideas, of course, are welcome in the comments.

My apologies for following off the blogging wagon last month; I fell sick myself. And in an odd turn, I got better just as vacation was starting, instead of the other way around.

You don’t know what you don’t know. Part 2

So this is the end of a very busy four day week; next week I’ll get back to the fun IT stuff!

I had a meeting last night where one of the participants couldn’t physically attend so we used Microsoft Teams to bring him in digitally. Now, the host of the physical get-together hadn’t used Teams to “skype” someone in so I watched her learn about Teams before he arrived — she went through and clicked EVERY button, flicked every switch and slid every slider figuring out what happened and what it would mean for a meeting/presentation. On a couple of options she asked me a question and occasionally I would feed in an anecdote of how I would have used it but generally she satisfied her curiosity by playing. I mean, learning.

We should encourage such bold experimentation in all our students. I mean, teachers.

You don’t know what you don’t know

(another quick thought because it’s been another 14 hour day)

Twice in the past two days I’ve had users of Office365 respond with “What? I didn’t know Office365 had its own YouTube!”

If you are an Office365 user, visit http://www.office.com and sign in with your credentials. Hit ALL APPS — these are all the apps you have access to without your Office365 login.

One of them is Microsoft Stream with the red arrow looking icon– it’s a YouTube for your school (or work). It’s only downside is that it is internal only. When you upload a video, only those people on the same address as you can see the videos. No one outside. No how. (Yet.) This means that Microsoft doesn’t lose money with viral videos but we get all the benefit of auto-captioning, auto-transcripts, Forms-quizzing, face-recognition and the like without having to deal with YouTube.

Give it a try! It’s free, unlimited and has the same security as your OneDrive files. You can go directly there by visiting http://stream.microsoft.com and signing in with your regular Office365 login.

As I always tell people, if you’re sitting there and thinking “there has to be an easier way… ask! Because there likely is!