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:
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->ExcelPowerAutomate, Forms->Excel Part 2 […]