Google Sheets is a great tool that many teachers are not using to the max. This post will outline some of the most creative and innovative ways to use Google Sheets in your classroom.


Data Tracking is easier than ever when you use Google Sheets. They sync immediately with your sheets so you can get real time data. I create one Google sheet for all of my form responses to easy my navigation and streamline my grading. To set that up, see below:
  • When you are editing your Google form, click on change response destination
  • Click on create a new spreadsheet and title it gradebook

  • NOTE* after you have created the gradebook, click on add a new sheet to an existing spreadsheet and choose the Gradebook




Treat it like Excel! Google sheets is simply an online, cloud based Excel. With that being said, not all formulas work as well in Google as they do in Excel. Use formulas to copy columns, auto fill the same information, create visual notices for you, and much more. Below are some example formulas you can swipe:
  • Add two cell texts together for a sentence
    • Include the space!
    • =(A1&" "&B1)
  • Copy the same cell from a different sheet  
    • green=sheet name  /  red=cell to copy
    • =(Readingprojectmar6!A1)
  • Copy the same row from a different sheet  
    • green=sheet name  /  red=row to copy
    • =(Readingprojectmar6!1:1)
  • Copy the same column from a different sheet  
    • green=sheet name  /  red=column to copy
    • =(Readingprojectmar6!C:C)
  • Grab a random name from column A
    • =INDEX($A:$A,RANDBETWEEN(1,COUNTA($A:$A)),1)




So how do you take full advantage of what you learned above? Google also offers a script option. It is a bit scary but, what I will walk you through is very simply and friendly. There is a script out there that lets you send an email to someone right within sheets. Here is a link for the tutorial. Here are the steps you should take:
  1. Create a new sheet in your gradebook or on your daily reading summary sheet (use whichever sheet has information regarding your students success)
  2. In the first column, have the emails for the parents
  3. In the second column is where your message will go (example below)
    1. The message can be as complicated or as simple as you like but make sure that it coordinates with the correct parent email (you would not want to send an email to the wrong parent)
    2. I used Excel formulas to create a message like this:
    3. Your student read "Chapter 1" today I learned "The drawbridge was designed to keep out enemys during midevil times"
      • The quoted responses where student responses
    4. The formulas I used were "A1&" "&B1&" "&C1&" "D1 (cell numbers are subject to change)
      • A1 - I wrote the text "Your student read"
      • B1 - This column was the response students wrote on a google form asking what they read today
      • C1 - I wrote the text "today I learned"
      • D1 - This column was the response students wrote on a google form asking what they learned today
  4. When my formatting was done and the correct message was in the correct row. I would click on Tools => Script Editor
  5. Copy and paste the following script:
    function sendEmails() {
     
    var sheet = SpreadsheetApp.getActiveSheet();
     
    var startRow = 2;  // First row of data to process
     
    var numRows = 2;   // Number of rows to process
     
    // Fetch the range of cells A2:B3
     
    var dataRange = sheet.getRange(startRow, 1, numRows, 2)
     
    // Fetch values for each row in the Range.
     
    var data = dataRange.getValues();
     
    for (i in data) {
       
    var row = data[i];
       
    var emailAddress = row[0];  // First column
       
    var message = row[1];       // Second column
       
    var subject = "Sending emails from a Spreadsheet";
       
    MailApp.sendEmail(emailAddress, subject, message);
     
    }
    }
  6. Save the Script
  7. Select the function sendEmails in the function combo box and click "Run"
Parents will receive an email about what their child read that day and what they learned. Repeat this action daily for a digital check in.

EXAMPLE: