Hacking G Suite – 4 Creative uses of Google Sheets in the Classroom
Google Sheets in the classroom Featured

Google’s G Suite is a proven platform that allows schools, teachers and students to maximize the potential of a 1:1 classroom environment.  Sheets, one of the tools on the G Suite platform, is traditionally viewed as a data collection and organization tool.  From teacher-created gradebooks, to data collection in a science classroom, to resource management and inventory, using Google Sheets is an exceptionally flexible and powerful tool.  With a bit of creative perspective, using Google Sheets in the classroom can benefit both teachers and students in a 1:1 environment.

Google Sheets in the Classroom Scenario 1: Digital Portfolios with Google Forms

Google Sheets in the Classroom 1
Rethinking the combination of Google Forms with Google Sheets can transform the use of these tools into a simple digital portfolio tool.  The process relies on a recent update to Google Forms, which allows a form responder to upload a file as a response type within the form itself.  The process, as outlined in the image above, consists of a student creating a Google Form as the entry tool to archive elements and artifacts for their digital portfolio.  Because students can now upload files (image, document, audio, video, etc.), there is increased flexibility around the types of artifacts students can include.  As the responses are collected on a spreadsheet, the student’s digital portfolio can easily be shared with a teacher for review and even added feedback to the student.  

Note that all uploaded files from the form are automatically added to a folder in Google Drive.  That folder also needs to be shared with the teacher if the files are going to be opened and viewed by the teacher.  Alternatives or extensions with this process could include a teacher making an entry on a student form to assist in the archiving process in the portfolio.  Further, a teacher could create one form with a drop-down menu question of student names.  This would allow the teacher to archive student work onto one spreadsheet that could easily be sorted later when examining the work of one student.

Google Sheets in the Classroom Scenario 2: Digital Rubrics and Rubric Portfolios

Rethinking Google Sheets can also assist in the process of creating, using and sharing rubrics with students in a classroom using G Suite. Originally created by my former colleague Katrina Kennett ( @katrinakennett ) in an effort to provide timely feedback on student writing, rubrics created in Google Sheets improve upon paper-based rubrics in a number of important ways.  

First, once a Sheets-based rubric is created, a master rubric sheet can be duplicated for any future assignment.  Also, within a sheet that has a master rubric, the master rubric tab can be duplicated within the sheet to evaluate each student submission.  This process allows the teacher to have one Google Sheet for each assignment evaluated with the master rubric.  Within that sheet, tabs are created for each student, allowing the teacher to have all student rubrics in one organized spreadsheet for each assignment.  Further, because these rubrics are digital, teachers can take advantage of conditional formatting and formulas.  The former allows teachers to create rules on the sheet rubric that can change both the cell and text color.  A helpful rule for example, “When a ‘.’ appears in a cell, turn the cell purple and the text bold.”  By simply adding a period to each cell, the rubric instantly becomes color coded.  The latter, including formulas on the sheet rubric, allows teachers to have the score instantly calculated in a “scoring” column.  By aligning the formula with the conditional formatting trigger, the teacher simply needs to add a “.” to a cell to create the color coding conditional formatting trigger and to initiate the auto grading formula.

View Only Live Rubrics Template: https://goo.gl/OE4VkW

Link to Published Slides: https://goo.gl/17FXql

Google Sheets in the Classroom Scenario 3: Class Resources Sheet

Google Sheets in the Classroom Resources

While Google Classroom is an outstanding tool to manage digital workflow in a 1:1 G Suite classroom environment, there is an alternative approach to posting and sharing class resources and assignments using Google Sheets.  The process consists of a teacher structuring a sheet to resemble a course calendar with columns such as date, date, unit and chapter.  By including columns such as: assignments, notes, slides, homework and video playlists, a teacher can efficiently organize an entire course into one sheet.  As the class progresses, additional sheets can be added as tabs across the bottom to differentiate between quarters, semesters or units of study.  To easily distribute and make the course content available to the students, the teacher has to simply create one course folder in Google Drive that has “anyone with the link can view” privacy status.  This allows all course materials to be opened, and copied by students that have “view only” access to the course spreadsheet.  This approach can also integrate with Google Classroom.  The course sheet can be added to the About page in classroom as a permanent course resource.  Also, when assignments need to be collected, students can make a copy of the original template file from the course sheet and turn in their copy through Google Classroom.  This strategy could decrease the number of posts a teacher makes in Google Classroom over the length of the course.

Google Sheets in the Classroom Scenario 4: Research Planning & Archiving

Google Sheets in the Classroom Research

Helping students organize an extensive research process is a challenging task and Google Sheets can provide students with a structured approach to organizing, categorizing, and tracking their research process.  By creating headings such as “Source,” “Original Text,” and “Summary,” students can effectively keep track of the sources they are discovering, as well as the most critical elements within those sources, bolstering their overall  understanding of the concept.  Additionally, by adding columns like “Sub-topic,” “Planned Usage,” and “Key Words,” students are essentially creating a digital outline of their research.  When the research process is complete and students transition to synthesizing their research into a final research paper, the Sub-topic, Planned Usage and Key Words columns can be used in conjunction with filters in sheets to limit the view of their research.  Students can also then use the “find on page” function in the Chrome browser to find keywords.

Google Sheets is an exceptionally helpful component of G Suite and with a creative perspective, it can become a regular additional to any classroom using Google Classroom, G Suite or Chromebooks.  Rethinking Google Sheets is part of a three part series on rethinking the use of G Suite tools: Docs, Slides & Sheets.  With a big of creative thinking, the efficient, collaborative and classroom ready G Suite platform can become even more powerful in the classroom.

 


Share this:

AI Essential Course Promo Image

AI ESSENTIALS:

10-Week Accelerator Course for Educators

Join us for ten 90-minute virtual sessions over ten weeks that will each dive into specific aspects of AI in education!