Google Add-ons and Formulas

During this year's Teaching with Technology conference , I presented about some nifty Add-ons and other enhancements to Google forms and sheets. Even though I no longer use these tools because our school shifted to a new LMS that can do many of the functions, I wanted to spread the word. These scripts saved me so much time last year, below is a copy of the presentation.

The Magic of Google Forms for Beginners

Google Forms can greatly enhance a course. The ease of use coupled with the spreadsheet that automatically organizes the responses, make Google Forms my go-to survey-maker. Below you'll find some great uses of Google Forms that are relatively easy to use and set-up.

1) Formative assessment - after watching the video, students complete the Google Form. Some of the form tasks might be answering questions, writing a summary and asking a question. The responses get organized in a spreadsheet and there is an option to view the responses in graphs. In a traditional class, this is analogous to an exit slip. I appreciate being able to gauge what my students know and are confused about before arriving to class. This information can inform my planning and intervention.

2) Student feedback - at the end of the year, I have students complete an anonymous course evaluation. I highly recommend doing it anonymously to help get genuine answers but some might disagree. I've learned some interesting facts about my course by reading through the responses. 

3) Student reflection - I use Google forms to reinforce meta cognition skills. Regularly, I have students complete a form as a means to reflect on their learning. They may also reflect on their contribution to a group project, which helps me formulate future groups or intervene.

4) Submitting work or lab data - if you have students create online materials, like blogs, presentations or videos, it can be daunting to organize these submissions. You can send a Google form requesting the URL of their assignment. In science classes, you can also have students share their data with the class by filling out a Google Form. When it's time to review student work, all of the links or data are organized in a spreadsheet for you. 

5) Polls - whenever I need to poll students, perhaps as a pre and post discussion activity about a controversial issue, I prefer to use Google forms as well. 

Google Forms can do a lot more, especially when scripts or add-ons enhance the response spreadsheets. For more uses of Google Forms, visit this site. 

Suggestions for Flipping the Science Lab

Here are some suggestions for managing labs in a flipped science course.  

1) Labcast video: 
Students should complete a pre-lab activity. At the very least, students should watch a labcast describing the experiment, lab safety concerns and demonstration of frequently confused portions of the lab procedure. The labcast video will save the teacher so much time by minimizing the usual clarification questions and giving students the opportunity to learn about the lab outside of precious class time. 

 

Example of a Labcast

2) Google Form for data collection:

If you are combining data from multiple groups, definitely use a Google form. Make sure students do not include the units because the spreadsheet will be unable to perform calculations. 

Sample Data Form

Consider whether you want to include required questions. Required questions defend against blank data entries. However, if there are legitimate reasons for skipping trials, then students will be unable to submit their data until you change that option. 

Finally consider adding an identifier question like student name or group number. If students make a mistake submitting data, you will need to identify and delete this data. (I had to learn this the hard way, notice the image above violates this suggestion!)

3) Publishing data: 
Add sheets to the Google form responses spreadsheet. You can use the array formula to send updated data from the responses to a calculation sheet. The calculation sheet can calculate and automatically update the mean, standard deviation, confidence intervals, etcetera. You can use array formula one more time to send the calculations to a published data sheet.

 Multiple sheets for calculations & data publication

The neat thing about Google spreadsheets is the option to publish just one sheet. I typically publish one final sheet and share the link with students.

Published Data

This workflow has been successful in my class. In most cases, students seamlessly move from different steps in the learning cycle to labs without much interruption of other students. The struggle I have with labs is more global and related to running an asynchronous flipped course, rather than merely a flipped course.

Leveraging the Power of Google Forms, Scripts and Formulae

Last week, I led two workshops at my school's professional development day. One was relevant to the flipped class. It was a presentation inspired by a webinar led by Ramsey Musallam. He framed the use of his tools as solutions to design problems. I took his lead and discussed VLookUp, Formemailer, editing the form confirmation page, and Flubaroo as solutions to four design problems in my flipped course. Incidentally, I learned about Formemailer and editing the form confirmation page from Ramsey as well.

Here is a link to my presentation and below is the YouTube playlist of my tutorial videos demonstrating how to use the relevant hacks, scripts and formulae in Google Forms. 

Getting into a #Flipclass Groove: Week 3 reflections

This is probably the first full week that accurately captured the essence of the flip class. Students took a quiz at the beginning of the week and individually mapped out their best use of class time - the week contained one video with guided notes and Google form, problem set and a lab challenge. 

Successes: 

Back to School Night happened this week. This event has been on my radar since before the school year. Members of my PLN have shared horrific stories of parents heckling or chastising them because of the flip class. Minutes prior to my first presentation, a colleague warned me that there will be a lot of parent questions. But I felt confident because I planned my brief presentation assuming the worse. I started with a talk about why I chose to flip and the problems that were solved by the flip. The mood changed when I addressed the concerns before they were even stated. I could feel the logic of my argument swaying opinions and was relieved by the sea of head nods accompanied by smiles and audible affirmation. All I kept thinking about was a comedian in a movie who hugged his agent after standup performances and said, "they didn't boo." My #eduwin this week was the parents didn't boo me either!

I've been worried about my workload as the course approaches the asynchronous units. I needed a way to keep on top of student video watching and form submissions. Up until this week, I double checked each form response, gave feedback and updated my records. Since there were only two videos, this wasn't a difficult task. But it is a terrible waste of time to check each response for 5-6 videos form spreadsheets. After some research, I found several scripts in Google Apps including "VLookUp", "ImportRange" and "Array."These gems have drastically reduced my workload and increased my ability to respond to student needs - so much so that I'll write a future blog post about them. 

I graded the first batch of quizzes and lab assignments. The first quiz average is higher than last year's first quiz. The first quiz of the year is always the lowest for me. Students expect factual recall and not application questions. Every year I have the conversation with students that I never ask for definitions and other lower Blooms questions. Instead, I incorporate the terminology into my questions, while asking them to use what they learned in a different setting. If they haven't learned the lower level information than they can't even begin to make applications. This year, the students in 2 out of 3 classes were apparently more prepared this year to make applications on the quiz. And don't forget that this all happened without any direct instruction of facts during class time, all instruction was delivered via video outside of class time! Rather, class time was almost exclusively used for application and exploration. 

The ratings on the videos have increased from 2.7 to 2.85. I attribute this increase to greater comfort with the flipped process rather than better videos - especially since the earlier videos were made most recently, after I suffered through the video production learning curve. 

Students are adapting and adjusting differently to flipped learning. Some students are watching videos in class, some are mapping the week out to watch videos at home and using class time to get help from me.  Some students are watching videos and taking notes in the hallway. The best sight of the week was the formation of impromptu tutorial groups. Some students who were a step or two ahead used class time to help their peers. One of these groups politely dismissed me because they figured out how to learn a bit more on their own. I think my students are starting to get into a groove. I have a handful of students who completed the learning cycle prior to the end of the week and took the quiz before others. This marks the beginning of a shift, where differentiation becomes a reality. I will pay close attention to this development and continue to solicit student feedback. 

I'm really enjoying the structure of the week: initial exploratory problem-based lab with data collection, a video with associated guided notes and formative assessment Google form, application practice and revisitation of the original lab challenge. The students really are thinking their tails off. I've redesigned many of the labs by taking out mandatory steps, withholding information and giving them the freedom to problem solve and apply what they learned so far. This week's Corn lab is a prime example of the shift in pedagogy. This is a traditional lab where students tally the different offspring and are told information about the inheritance of the traits and the identity of the parents. They test whether or not the observed offspring fit this pattern. The traditional lab is a decent practice of Punnett Squares (and either percent error or chi square statistics) but it's not a challenge to their thinking. In my revision, students record the data after figuring out an effective method for doing so. Then they use the data to figure out how the trait is inherited, the possible identity of the parents and use percent error and Punnett Squares to justify their reasoning. So this adjustment is nerve wrecking because the students don't already know the answer. They are not merely performing or practicing algorithms. They are problem solving by determining which algorithms are needed and how to apply them to the problem. It's a giant shift that required a change in my thinking. Students who are successful really demonstrate that they can perform the algorithm(s) AND have internalized their purpose and how to apply them. The other benefit is that they instantly see why the information from the video and other learning activities are important. 

Adjustments: 

As I mentioned in last week's reflection, students requested changes to workflow and I complied by switching to an assignment sheet rather than the MentorMob playlists. Some students prefer the playlists but I'm forcing all students to make the adjustment. At the end of the current learning cycle, the Mentormob playlists will be deleted. A student suggested that I keep two systems to give students choice. I considered it for a couple of days but vetoed the idea for two reasons: 1) sometimes introducing unnecessary choice creates confusion, which I'm trying to avoid; 2) it is a needless workflow addition for me to update the playlists and assignment sheet. So I kept the current playlist on the site and deleted it once all of the students finished the current learning cycle. For those who continued to use the playlist, they will have to adapt to the change. 

While most of the tech issues have been resolved, printing seems to be a hassle. The flow of this asynchronous class gets ruined by needless pauses and tech trouble shooting. At least until this issue gets resolved, I'll revert to old school photocopying of handouts.

My final adjustment this week is my approach to quizzing. A few students were able to take their Moodle quiz today. I'm glad it was only a handful of students because I became aware of a few glitches. I definitely needed to work through the Moodle quiz options. For example, the quiz is password protected but it is easy for that password to spread. I initially thought changing the password between periods would be enough. Apparently, I need to hide the quiz even during the same period. Some students took the quiz without permission. They mistakenly thought they could take the quiz. In addition, I enabled the force time break between submissions because some students took the quiz a second time right after taking it. I added a 24 hour break between submissions and a total of three attempts per quiz. I don't want students rushing through attempts without pausing to reflect on how they did on the quiz. Some students ignored my advice and took the quiz without having pen and paper. Since several quiz problems entail math, they did not do so well when they tried solving the problems in their head. In addition, I have to set a better tone for the quiz taking. The class is an "organized chaos" with students moving around and collaborating. This atmosphere is not conducive to taking quizzes. In the later periods, I had students taking quizzes separately and mandated that they give me their scrap paper after taking the quiz. I'm hoping this will reduce the sharing of quiz questions. Although I hope I have enough questions in the test bank to prevent or discourage sharing.

Importance of Adjusting to Student Feedback - Week 2 reflections

This was a shortened week since we had our annual overnight grade trip. Even so, we managed to get an exploration activity completed and some tweaks to workflow. 

Successes: 

Hands down the highlight of the week was when a student encouraged me to patent this way of teaching. He sounded disappointed when I informed him that thousands of teachers flip their class. That is my #eduwin for the week!

A new student joined the 8th grade and it became obvious during the exploration activity that he already knew the content for the upcoming week. We agreed that he didn't need to watch the next video, take notes nor submit the associated Google Form. He'll skip to the problem set to demonstrate proficiency, and if successful, work on an alternative project or move on to the next learning cycle. In the traditional model, he would've suffered through one period of lecture, demonstration and sample problems. Flipping the class will allow this student to use class time much more effectively. 

After some anxiety about students understanding the content presented in the videos, I'm relieved to share high ratings regarding the helpfulness and clarity of the videos with average ratings of 2.7 out of 3. (The average would've been higher but I neglected to have a 3 rating as a choice and was made aware by students who wanted to use a rating of 3.) In addition, students answered the understanding level questions correctly on the Google form responses. A handful of negative reviews can be attributed to students assuming the video would give information about something that I opted to put into a different video or an upcoming activity. A few incorrect answers on the Google form appeared to be careless arithmetic errors. Two students mentioned that they preferred the old method and one wanted to a question answered in the moment. 

On a personal but related note, I'm becoming less concerned with perfecting videos; rather, I'm focusing on getting useful videos published in a timely manner. I can now appreciate Jon Bergman's joke, "do I need the video to be perfect or do I need it on Tuesday?" This new focus should reduce stress and anxiety. The hours I put into earlier videos created an unsustainable workflow. The encompassing theme of the Flip seems to be intentional use of time and energy for students and teachers alike. Think I've internalized that message, finally!

The FormEmailer Google Form script works marvelously. This script sends email responses to form entries directly from the spreadsheet, rather than necessitating the crafting of separate email messages to each student. One concern I had about the Flip is the inability to answer questions during video-viewing or shortly after. One way to alleviate the concern was the addition of a Google Form, where students can submit questions. Adding the FormEmailer script greatly decreased the turnaround time for a response to student questions or concerns. I typed my responses into the spreadsheet, clicked a button or two, and students got an email response. There's some setup required but it is well worth it. Some of my email messages were sent minutes after the students submitted their form. I plan to enhance this with other scripts that can indicate which students have not submitted a form response. 

Gradebook Pro (on the iPad) has proved useful as well. If you take the time to input or import student email messages and determine values for each assignment, you can send a grade report to students directly from the app. The report can inform the students which assignments they are missing, current grade and even notes that you recorded about assignments and/or conduct. After sending these periodic messages, my inbox shortly gets flooded with Google Drive share notifications, indicating students submitting their work. On one or two occasions, the message prompted a student to correct a mistake I made in record keeping. 

Since we're on the topic of Google Drive documents, I reminded students in class that I needed to see their video notes. Apparently, I must have mentioned printing the notes as a submission option. A student replied (and I'm paraphrasing), "it's better to share on Google Drive with Mr. Wilson because his best use of class time is working with us, not checking assignments." Wow...this student is really starting to internalize my daily message - think about YOUR best use of class time. 

After class, I informally chatted with two students and asked whether the new assignment sheet format (more info on that later) was an improvement. They both agreed, one more emphatically than the other. The one criticism was deadlines were missing from the assignment sheet. Before I responded, the other student said, "that's the point, you're working at your own pace." Sounds like more students are starting to buy into the philosophy here. I met with the student who desired due dates and helped him set daily deadlines. This doesn't have to be an "all or nothing" approach. It's about giving each student what they need to be successful. If some students work more effectively with deadlines, then I can make adjustments for that student without handcuffing the others.

Adjustments:

I got some really helpful feedback about a video. A student mentioned on their Google form evaluation of the video that they would love more practice on the concept, while others felt they understood the concept just fine. Since there is always an application in class, I took this to mean that the student wanted just a bit more practice before coming to class. Typically in the form confirmation page, I share the answer key to the form questions, in order to give students instant feedback. Now, I'm adding a link to additional practice problems with answers. This way, students can choose whether they want the extra practice or not.  

The great thing here is that some students are submitting questions with answers in their Google form response. These questions can behave as formative assessment for the students who submitted them and extra practice for future students.

I'll need to add a table of contents to videos. On several occasions, I referred to a specific part of an instructional video. This will be helpful to allow students to pick parts of videos they need, instead of watching the entire video. 

On my tracking sheet, I have students input the date on a completed cell and change it from red to green. Even though this seems like a simple 2-step task, is it really necessary? It occurred to me that they can input the date and I can set up conditional formatting to automatically change cells with dates into green and set empty cells to red.

I also found a simple solution to the ridiculous issue in the mobile version of Google Drive Sheets, where students accidentally erase other entries because the entire row gets highlighted when a student edits a cell. 

   I switched the rows and columns so if a student edits a cell, they are only editing their row. I suspect they will be more careful and aware of their actions if their entire row can get ruined due to careless editing.

After a brief love affair with Mentormob playlists, I've opted to use an online assignment sheet with links to all resources. [I alluded to this change earlier in this blog post.]

Multiple Playlists vs...

One Assignment Sheet

Even though the playlists are aesthetically pleasing, it became clear that the extra step of navigating through a playlist was an unneeded obstacle. In addition, a student pointed out that a web page with multiple playlists takes quite a bit time to load. I should have expected this because last year's evaluation responses indicated students found the online assignment sheet with links to documents all on one page to be useful. I'm unsure how I got to this point; perhaps it's because I loved using Mentormob to learn about different topics. It's still a great tool but the online assignment sheet just works better for my students. 

Related articles

• The Potential of the FLIP Meets Workflow & Tech Issues Head-on: week 1 reflections
• Reflections on my First Attempt at Flipped Learning