I shall find a way…

We are now forced to use Canvas (a web based LMS) for our courses. I won’t list the reasons why I dislike Canvas and think it’s a terrible thing for education generally but suffice it to say, it’s better for both me and my students to do our work in OneNote. So… how do I leverage OneNote and never have to visit Canvas? PowerAutomate (Flow) to the rescue!

PowerAutomate lets us take content from OneNote and “push” it into Canvas through a Canvas Office365 connector, and have it done automatically in the background so once you set up the “push”, it just repeats it on a schedule. It’s auto-magical!

To keep our students (and ourselves!) organized, we always used to have a daily Course Plan as the very first Page in our ClassNotebook. You can see examples here: Page One of your OneNote Class Notebook (continuousformation.blogspot.com) It’s quick & easy to keep things up to date. We’re going to take regular snapshots of this OneNote Page and make it visible in Canvas so that the administration-required Course Plan is available in Canvas.

Our first step is to make Canvas aware of Office365. To do that, we add an Office365 App — but first, we have to grab some security so that the two can talk to one another without anyone listening in.

Visit http://office365-iad-prod.instructure.com and you’ll get a button to generate the secret codes that will let the two talk. Leave that window open so that you can easily copy & paste into Canvas.

Now, let’s head off to Canvas and go into the course you want to push into (yes, you have to go to each course to add this connection… sigh. Canvas.)

Go into the Settings area for the course and click on the Apps heading.

In the Filter box, just type office and Office365 will show up. Click on the Office365 box and you’ll get a pop-up … which is where you’re going to copy & paste the information from the security window you left open (remember, you left it open?) Okay, now at least Canvas knows that Office365 exists.

Now, we’re going to go over to Office365 and create the information we’re going to push over. We’re going to ask PowerAutomate (what we used to call “Flow”) to look at our CoursePlan page (CP) in OneNote and save a snapshot that will be available to Canvas — and then repeat that every 6 hours or so and save it to Office365. Canvas will always see the most recent copy of the snapshot. Here goes!

Read through this first ... at the end, I'll give you a short cut to make this easier.

Head on over to http://office.com and log in with your school account. You’re looking for Power Automate so click on the Waffle (upper left corner) and see if it’s listed there. If not, click on ALL APPS and you should see it.

We’re going to create a Flow, so click on Create on the left-side and then the button Scheduled Cloud Flow. In the pop-up, give it a name and then schedule it for every 6 hours (or more depending on how many changes you think you’ll make during the day. I tend to put the course plan together and then make only minor changes in class.)

Once you click okay, you’ll notice you’ve already started the Flow! There’s a Recurrence box at the top. If you click on it to open, you’ll see the settings that you put in the previous screen.

Now we’re going to add the step where it grabs the OneNote Page. Click on NEW STEP and now we’re going to look for the OneNote (Business) collector (type OneNote in the search box) and then click on the Get Page Content option that appears in the box below (you might have to scroll down a bit). It may ask you to log in with your Office365 credentials, just to be sure.

Notice there are three boxes — which Notebook, which Section, which Page.

Fortunately, it’s smart … in the Notebook dropdown, pick which ClassNotebook you’re using and then the Section box will only contain the Sections from that ClassNotebook! Likewise, the Page box will only contain the Pages in that Section. So, make the right choices so that you can choose the CP Page in the last box.

Now, let’s add the step that makes the file that Canvas is going to show. Click on ADD STEP and then in the pop-up box, look for OneDrive for Business (yes, for Business… the OneDrive we use at school is business quality!) and then choose Create File as the action in the list that appears below.

Another 3 boxes to fill in! The first is where are you going to store your file? I usually just choose the first option you get when you click (called “The Root” or the top level of your folder) but you can drill down and pick another folder if you’d rather. You can quickly jump into your OneDrive and create one folder to hold all your Course Plans for all your courses if you’re really organized. (Okay, I did do that – you can see CanvasPublic in the screenshot; it keeps your OneDrive neater).

Then give it a name. Now, it doesn’t really matter what it’s called, but the students are going to see it, so I thought I’d call it something useful! And it has to end with “.html” I went with CoursePlanforMPM2D.html (MPM2D is the government’s code for Grade 10 Math).

Lastly, what is going to be in the file? We’re going to tell the Flow to go back and use the information from the previous step, so click on the lightning rod in the corner of the box – that brings up the “dynamic” content list – and choose BODY.

That’s it! When you run this Flow, it will snag the OneNote Page and save it in your OneDrive called that name (and overwrite the old version — but remember, OneDrive keeps previous versions so you can go back and look at your Course Plan over time!) and then it will repeat every six hours.

Phew! Last step! Let’s get Canvas to show our Page.

We have Office365 as an App, so whenever you’re editing content in Canvas, click on the APP button (it looks like an electrical plug) and choose Office365 and you’ll get your OneDrive.

Select your Course Plan file from your folder and click ATTACH and boom! There’s a link to your Course Plan. And that link is updated as soon as the Flow runs every 6 hours.

If you want to put it into one of your Modules, that works, too. You can click the + button and then choose EXTERNAL TOOL (why not keep the App name, Canvas?!?) and then choose Office365 and you’ll get your list of files and you can attach your Course Plan file as a Module item.

Now … that’s just ONE course. You get to connect the Office365 App, create a Flow to capture the OneNote and then make the link somewhere in Canvas to the file for THIS NEXT course. Now, I did make a Flow that does all five of my courses at once — the zip is here. You can import it by visiting PowerAutomate and clicking on IMPORT at the top and uploading the Zip file.

Using this five-courses-at-once Flow, you only have to connect the Office365 Apps to each of your courses and then make the links in each course to the correct file (notice in the 5-Flow you have to connect to the notebook, then give a filename for that course, and then a different notebook, different file name). If you have fewer course, you can delete a pair — if you have more, wow, my apologies, but you can add a pair just by following the steps above.

Caveat: When your password expires, you may get an email from Flow telling you that you need to re-connect things. Visit PowerAutomate and click the Fix Connection button and you’re done! PowerAutomate does keep track of every time things work or doesn’t work and will let you know if things aren’t working.

I shall either find a way, or make one. I’m so glad that Microsoft gives educators tools with which to make their lives easier. When impediments such as Canvas are placed in our way, it’s critical to find methods & routines that will minimize our stress and the demands on our time. And now that you’ve created one Flow, think of other places where you might want to automate things!

Respectful emails

Too often, I get emails in the evening and on weekends when I’m relaxing, working on a project or even doing school work but in a focused way. Now, if it’s a student question, well, I’m okay with that (to be honest, almost all student questions are now Teams Chat — kids don’t use email!) but colleagues and administration should have better knowledge of boundaries. Fortunately, Outlook now prompts your awareness.

When you go to type a message, Outlook now queries your recipients’ work hours (How do I set my own work hours?) and pops up above the message that you may be interrupting their down time.

When you click the DELAY SEND link in the pop-up, a pane opens to the left to let you determine when the message gets delivered:

And now the text above the message indicates when the message will be delivered.

Folks should be cognizant of when they send emails (and Teams Chat for that matter) but it’s nice to see Outlook helping to remind them of being respectful of their readers. For sure, you used to be able to do this manually (and still can) but it’s automatically prompted now and far easier to get to when it’s in your face.

Now this is on Outlook Current Channel as of this week — too often, though, IT departments delay roll out of changes to Office programs, so you may have to talk to them to make sure you’re on the right channel (your updates can be delayed by up to a year by IT!)


How do I set my own work hours?

Open Outlook and go to the FILE menu and choose OPTIONS down at the bottom. Then, click on CALENDAR on the list on the left.

You can set the hours on a weekly basis. I’m a little disappointed there isn’t more granularity given our present circumstances — or even a smarter version that looks at your Outlook to gauge your work hours on a refreshed-daily basis, but this is a good start.

Extra Help w/ Bookings

Given remote learning right now and students all around the world, I’m using Microsoft Bookings App to schedule student (online) math extra help. It’s an add-on that’s included with paid Office365, although you may have to convince your IT department to turn it on for you. (There are often a lot of apps that your school has actually paid for that your IT department either hasn’t turned on, or deliberately turned off!)

Bookings lets you set up time when you are free for Extra Help and lets the students (or parents) independently schedule a meeting with you during your specified free times (so you can say you’re open from 7-8pm for extra help, but if they want to book for 830, they can’t). And, if you already have a personal meeting in your Outlook, it will close that time from booking, too.

If you go to www.office.com and sign in with your school email, click in the Search box at the top of the screen and type bookings:

Microsoft Bookings icon is a style-ized “b”. If you don’t have the icon AND you can use the full version of Word, Excel, etc on your device from your school, then it’s very possible you can ask IT to turn it back on (Bookings is not available on the free Education plan, where you don’t get the full versions of Word, etc). There is no added cost to Bookings, and you are already paying for it if it’s there.

It is primarily made for businesses (picture “pet grooming” or “personal trainer”) so some of the language is a little non-academic.

You’re going to set up a NEW business … be sure to put your Name into the business name as this will be used in the Outlook appointments that get pushed out to both the student’s calendar and yours.

You could set this up on a Department or School basis (if not too large). You can then put all your staff in and students would have a one-stop-shop to arranging extra help.

First we’re going to set up our Bookings Page (where your students/parents will visit to book you).

A few pointers:

If you are going to include parents, do NOT click the “must have Office account from my organization” because then your parents can’t book. If you only want students, or if it’s peer tutoring, then you definitely want to have this option active (to avoid random strangers booking your students!)

Be sure to click “Send a Meeting Invite” so that your student gets a calendar entry in their calendar.

Pay attention to the very little “Set different availability for a date range” — this will let you remove Christmas or March Break from the calendar, or restrict booking to the weeks before/during Exams, say rather than all year.

The rest is up to you… I set it so they can’t book more than a week ahead of time, and they can’t book later than 2 hours before. You’ll figure out what works for you.

Save your changes!

Next, click on the STAFF option on the left side of the screen and then ADD STAFF:

Put in the teacher’s initials and then, in the box directly below the initials, use the lookup to find the teacher in your email system. I didn’t include a phone number and set myself up as Administrator — if you were doing Peer Tutoring with students, you’d set the students up as Viewers instead, so they couldn’t go in and play with the Staff information.

In the second column, set your general availability. Turn off “Business Hours” and then set up your weekly schedule — you’ll notice I’m free for math extra help on Mondays, Wednesday & Fridays from 7pm-8pm in my time zone. Remember — this is your GENERAL availability. If I accept an online Desmos PD and save the booking in my personal Outlook calendar on a particular Monday from 7:30-8:30, the Bookings app won’t let people book during that time on that Monday. So this space is your “maximum available hours” and you can still put aside your time by using your Outlook calendar to avoid anyone! And you can make any global changes here at any time and it will update automatically.

Be sure to hit the SAVE button at the top of the screen when done!

Now, set up the actual Service type (remember, pet groomer! Is it a big dog or a little dog or a cat?). In our case, there’s only Math Extra Help, but you could have them differentiate say, Essay Writing Workshop or Research Skills, if you’re an English teacher. Click on SERVICE on the left of the screen.

Be sure to click ONLINE MEETING so that you get a Teams Meeting set up for your appointment.

And I like having buffer time between meetings;, but that’s up to you.

And lastly, yes, set the price to Free! You could use this for a tutoring business but I’m sure your Department Head would want a cut.

Be sure to Save!

And then, get rid of the “Initial Consult” service that Microsoft has already provided. It’s only going to cause confusion.

Okay, we’re ready to go! Click on back to BOOKING PAGE and then click on OPEN PUBLISHED PAGE — this is what students/parents will see:

Notice it says ‘Select Staff” … in the dropdown, it only shows my name, since I’m the only staff. And at the very bottom, students who are remote and in a different time zone and change the time zone to see what my availability is in their time zone.

When they choose a time, they get an email (and a calendar invite, if you clicked on it) and you get an email and it automatically goes into your calendar as a booking, with the link to the Teams Meeting included! And, if you chose the option, the student/parent will get an email reminder in advance of the meeting to nudge them.

Let me know if this was useful. It’s a handy add-on that might prove effective.

That which doesn’t kill you…

And it’s done.

I wanted to make sure I wrote this as soon as I could, because as time passes, things will look differently.

This has been the worst academic year in my 25+ year career. People would ask “How are you doing?” and my response would always be “Well, it’s a pandemic, you know…”

The school moved to semesters, and I was given three different courses (as well as a weekly club and in November, a 3x week extracurricular and. new this year, weekly lunch duty). We had three cohorts (two who flipped day-by-day and a third remote-only, the latter which grew and shrank on a daily basis as students & parents chose to be remote-only or not) and taught the same schedule each day. The latter meant that my Grade 11 class had last period every day, and you could tell; many of them had phys-ed third period, which meant they came in tired already.

The physical arrangements, from a medical perspective, were excellent — there was plexiglass and tape delimiting student & teacher areas. There were anti-viral wipes every class, beginning and end (I introduced my Grade 11s to worker-health-and-safety as we worked our way through the official Material Safety Data Sheet for those wipes after there was a conspiracy theory that they were bleaching people’s clothes. They were not.) Although my classes were some of the largest I’ve ever had, less than half were in the physical classroom at any time so they were well-distributed with set-in-concrete seating plans. There were only 6 reported cases at my school over the 4 month period so we appeared to avoid symptomatic spread. There was a daily screening of everybody before they arrived at school (which had its own personal information & privacy issues, but that’s a topic for another day.)

The department office was problematic; it had little ventilation, the door was often closed and the room overly warm (for my preference) so during my prep period I tended to work in my car.

As a school, we were well prepared technologically for this hybrid simultaneous remote-F2F teaching — with everything in OneNote and everyone having a Surface device — digital ink meant we didn’t have to worry about paper or pictures or PDFs — the content and student work was easily created and distributed. However, the process of teaching and learning was, well, problematic. Everything hybrid (juggling F2F & remote simultaneously) meant everything took twice as long to prepare for, to do, and to assess. Just taking attendance was a constant struggle. (“Oh, Tim, you’re now remote only?”, “Jane, you came online 10 minutes late, that’s why your parents got an email, sorry, we were discussing a problem and didn’t see you come online after I did attendance…” etc)

There was no time, and little energy. I would come home and work for 3-4 hours and then try to get some exercise, fall in bed and repeat the next day. I would reach Thursday and I would be out of energy; I remember putting my feet on the floor coming out of bed Friday mornings and praying that I’d find the strength to make it through the day. Three preps meant a lot of work trying to find ways for students to develop understanding that were completely different than what I’d spend 3 decades thinking deeply about. Friday nights and Saturdays I mostly slept and took care of home things, and then Sunday afternoon & evenings were for prep. Feedback to students was minimal compared to a regular year; there was no time to engage a lot with their work.

I used a lot of self-checking materials to help students. DeltaMath and Desmos activities, and OneNote/Microsoft Whiteboard content that gave them feedback on their work as they did it helped to make up for the lack of my own feedback. When I think of how much feedback they would get when working up on whiteboards in a regular class… Breakout rooms and Microsoft Whiteboard/OneNote, as best-of-alternatives still don’t compare to groupwork on a whiteboard.

My students were incredible — they worked very hard (okay, there’s always a distribution on effort and some kids were on the opposite tail) and kept their positivity up through most of it — okay, that’s a bit of rose-coloured glasses. There were tears and students were often challenged by all the stresses they had to encounter. But we got through it, mostly. In particular, my grade 9 and 10 classes impressed me with their participation — for coming new into the school, the grade 9 students were a complete surprise. Compared to last year, I was working with twice the number of Grade 9 and I really enjoyed learning with them.

One thing I did do was encouraged students to ask questions any time — using Teams Chat they would message me after class and I wanted to make sure they stayed connected with the class and the content when it worked for them. Students who took advantage of it did very well (students who never asked a question for the most part did not). It did mean a lot of interruptions throughout the evening & weekend but given the dis-connected construction of the classroom, I felt it was important and necessary. Since we have to book our gym time ahead, I would often be at the gym in the evenings, swiping furiously on my phone answering student questions in the evening as students asked about quadratic word problems, or equations of lines.

I seldom saw any of my colleagues and any kind of pedagogical discussion was limited to brief discussions of tests and pacing. There was no time and little energy to expend. For some reason, my school decided to dedicate a week to “Imagining the School of the Future” or something like that — I hid the Team and deleted the emails. I was barely keeping going day-to-day and the thought of giving a meaningful response to such a deep question was just overwhelming. The school also decided to implement a new LMS and a new student information system in September, which meant entering the same information into three different systems until we managed to work out something more reasonable. There were times I questioned the tone-deafness of emails sent down from above but I tried to ignore them and keep going. Whenever any head or director asked how things were going, I always replied “I’m struggling” and they seemed okay with that response.

I worried a lot about my colleagues. There was only me at home but folks with kids had to juggle everything with them, too, along with the academic responsibilities. We had a new colleague join us and the ramping up was painful to watch on her. And then when their kids would get sick and they would have to stay home — which meant that the teacher had to teach from home, look after the kid and keep up with everything else, too. (I pulled my back one weekend and had to teach from home one day — it ended up more work than rest and so I just went to school the next day and suffered through it). I could at least collapse into bed and not worry about anybody else; they had to make sure their kids were safe, secure and happy.

I was absent on Twitter and Facebook and here blogging. There was no time and little energy. The worst night was when I was working on setting a test and I fell asleep at my desk — I woke up when my phone dinged with a student question. I remember writing up a problem situation and the next thing I knew I was sitting up in my chair with a start when the phone went off. (That said, I did fall asleep in my car during my prep a few times, too, but I had reclined my seat and set my alarm — there’s a difference between a nap and spontaneous sleeping.) I don’t know how I kept my emotions in check during the day but they certainly came out on the drive and in the privacy of my own home.

I saw my family once, at Canadian Thanksgiving in October. The concept of “bubble” was out the window once I started teaching so I kept away from my mom (elderly and immuno-comprimised) except for that one patio-dinner at a restaurant. Christmas is still in question, since we’ll only have a week between my last day teaching and the 25th; I’m not sure how comfortable I feel. As a single person, the house gets quiet and lonely even with all the technological access to the outside world. Alexa has turned out to be the conversation I often need.

I will look back at this like all the challenging episodes in life and both learn from it, forget much of it and remember the successes. We finished yesterday at 4 and I went to bed at 9 and have had a good night’s sleep and the future looks brighter. There are vaccines on the way, I only have 1 prep for two courses next semester, and I lived through this. I didn’t die, and that is a pleasant surprise.

I recognize that my situation was challenging from my perspective. I had enough food to eat (too much, in fact; it’s a way of coping). I had my job, so there was money. I never got sick. Believe me, I count my blessings every day, too.

Dominos – a self-checking activity

So if I were a trendy “influencer” I’d call this app-smashing — but instead I’ll just say here’s a cool way to use PowerPoint to make a self-checking activity for almost any subject area’s content knowledge (although in this case, there’s a little mathematical skill involved, too). My colleagues Thao and Seane were kind enough to do all the questions for me in this example for math review.

The students get a pile of dominos in their Microsoft Whiteboard, with an answer on the right and the question on the left and they re-arrange them into a square (or a line or whatever). They’ll know they’re right because every domino matches up!

So you start out in PowerPoint – you can use the template here and copy/paste or write your own questions on the slides:

On each slide, put a question on the right and the answer to the previous slide on the left. Now I did find it handy to create all the questions and answers on the same slide first — this let me re-arrange the questions into a reasonable pattern, as I didn’t want two Pythagorean questions side-by-side, but that’s up to you. Then, when I was ready, I shifted all the answers down one slide and moved the last one to the first slide. What’s nice is you can include text or images or graphs as necessary! Nothing interactive, though (yet).

Now, the template above I’ve set so that if you go to Layout, you get a nice mix of colours so your dominos will look more interesting than the mono-chromatic example above, but that’s up to you!

So add questions and slides until you’re done! If you want them to form a square, you’ll want 16, 25 or 36 slides, but you can also give them an early clue and have a START and END slide so they can just do it in a line. I also did an example where it formed an icosagon (20 sided figure — you can read about it here) but I was overly zealous and no one is going to be that foolish again.)

So, now you’ve got all your slides done! We need to make them into dominos. We’ll use a seldom-realized feature of PowerPoint — save the slides as JPGs! Click on FILE and then EXPORT and choose JPGs

It will pop asking you where — pick a spot you’ll remember. It will create a folder with the same name as your PowerPoint File and then put a copy of all the slides as JPGs.

Okay … ready to make the Whiteboard! Head on over to the Microsoft Whiteboard program and create a new Whiteboard.

Go to your folder and CTRL-A every JPG file and then CTRL-C to copy them all into the clipboard at once. Click into your Whiteboard and CTRL-V paste… boom! All your jpgs are now free-floating dominos! You can drag and drop and rotate wherever you want!

Now, you likely want to create a few Whiteboards, one for each group or students, but it’s as easy as CTRL-V paste the dominos into each Whiteboard and then using the People icon in Whiteboard to create a sharing link or to post on Teams.

This was a fun review activity to work in trios inside a Teams Channel meeting for our Grade 9s, and as I mentioned, I created a more intricate one for my Grade 10s earlier (link here).

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!