Centrally Manage your Google Classrooms from a Google Sheet

Update 08/02/2021: I’ve updated this approach in an all new workshop video. Please check out the video below for links to the latest templates. This workflow replaces the one described in the post.

As the new school year approaches, I’d like to share a free DIY approach to simply managing your school’s Google Classrooms using Google Sheets. I have worked with free add-ons in the past that have helped with this, but many of them are no longer supported, are unavailable, or require an annual subscription. My goal is to give you the know-how to build a resource you can use year after year for free. This blog post will break down the code and workflow step-by-step with companion videos and screenshots to support your understanding. The following tutorial requires you to be logged in to a Super Admin account. At various points throughout the project you will need to authorize the functions.

Step 1: Creating a Project on Google Cloud Platform

The Classroom API is accessible via the Google Cloud Platform, so the first thing we need to do is create a project. Navigate to https://console.cloud.google.com/ to get started.

Look for a NEW PROJECT button and click it.
Give your project a name and click CREATE.
Search for Google Classroom API in the search box and click the enable button.
Click on Credentials to Configure the Consent Screen.
Click CONFIGURE CONSENT SCREEN
Select the Internal User Type and click create.
Complete all required fields, including uploading an application logo, then Save.
Return to your Project’s Home Page and copy your Project Number to your clipboard.

Step 2: Copy the Google Sheet Template and Connect it to your newly created Google Cloud Platform project.

Here is the Google Sheet Template you will need to begin setting up your workflow. Cells with a yellow background indicate there is a formula there.

Open the Script Editor.
Click Resources, then Cloud Platform project.
Paste your stored Project Number from Step 1 into the “Enter Project Number here” field, then click Set Project. Once your project appears in blue text you’ll know you’ve successfully connected your project to Google Cloud Platform. Then X out of the window.

Step 3: Creating the Google Classrooms

Below is the Apps Script associated with creating the Google Classrooms. Please watch the video to understand how this code interacts with your data.

//Creates and assigns Google Classrooms to teachers.  Must be super admin account to do this.
function createClassrooms() {
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Classes");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Fvals = sheet.getRange("F1:F").getValues();
  var lastRowCourseId = Fvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 6).getValues();
  Logger.log(data);

  for (var i = lastRowCourseId+1; i <= data.length; i++){

  var create = Classroom.Courses.create({
  "ownerId": sheet.getRange(i, 4, 1, 1).getValue(),
  "name": sheet.getRange(i, 1, 1, 1).getValue(),
  "section": sheet.getRange(i, 2, 1, 1).getValue(),
  "room": sheet.getRange(i, 3, 1, 1).getValue(),
  "courseState": sheet.getRange(i, 5, 1, 1).getValue()
  })

 
  Logger.log('Course created: %s (%s)', create.name, create.id);
  sheet.getRange(i,6,1,1).setValue(create.id);
}
}

Step 4: Adding an Admin Account

Now that you’ve created all of your classes, it’s important to assign a user account to be a co-teacher of every classroom that will serve as your Google Classroom admin account. I recommend something like classroomadmin@yourschool.org. Administrators and Tech Support should be given the password to this account in order to monitor and support all of the Google Classrooms. This account will also have posting privileges directly from the sheet to pre-populate topics for the teachers or even send announcements to multiple classes at once.

Please share edit access of your Google Classroom Template Sheet with this account.

Here is the Apps Script for adding the Admin account. Please watch the video to understand how this code interacts with your data.

//Adds an Admin account as a Co-Teacher to every class.
function addAdmin() {
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Admin");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Cvals = sheet.getRange("C1:C").getValues();
  var lastRowStatus = Cvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 3).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++){
  
    var students = Classroom.Courses.Teachers.create({
      "userId": sheet.getRange(i,2,1,1).getValue(),},
      sheet.getRange(i,1,1,1).getValue());
      sheet.getRange(i,3,1,1).setValue("ADDED");
    }
}

Step 5: Enrolling students in your Google Classrooms

Now that you’ve created your classes and established an admin account. It’s time to add your students. Below is the Apps Script for adding students to their classes. Please watch the video to understand how this code interacts with your data.

//Adds students in bulk to Google Classrooms
function addStudents() {
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Students");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Dvals = sheet.getRange("D1:D").getValues();
  var lastRowStatus = Dvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 4).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++){
  
    var students = Classroom.Courses.Students.create({
      "userId": sheet.getRange(i,2,1,1).getValue(),},
      sheet.getRange(i,3,1,1).getValue());
      sheet.getRange(i,4,1,1).setValue("ADDED");
    }
}

Step 6: (Optional) Adding Co-teachers to your classes.

Should you have a need to add a co-teacher, for example if your lead teacher has an assistant you may want to add him or her to a class as a co-teacher. Below is the Apps Script for adding Co-Teachers to classes. Please watch the video to understand how this code interacts with your data.

//Adds Co-Teacher to Classes
function addCoTeacher() {
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Co-Teachers");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Dvals = sheet.getRange("D1:D").getValues();
  var lastRowStatus = Dvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 4).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++){
  
    var coTeacher = Classroom.Courses.Teachers.create({
      "userId": sheet.getRange(i,3,1,1).getValue(),},
      sheet.getRange(i,2,1,1).getValue());
      sheet.getRange(i,4,1,1).setValue("ADDED");
    }
}

Step 7: Inviting guardians

Guardian Summaries are a great automated way to keep parents/guardians informed of classroom activities. Think of them as a digital Friday folder. Here is the Apps Script to invite Guardians. Please watch the video to understand how this code interacts with your data.

//Sends an email invite to one Guardian
function inviteGuardianOne() {
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("GuardianOne");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Cvals = sheet.getRange("C1:C").getValues();
  var lastRowStatus = Cvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 3).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++)
  try{
if (sheet.getRange(i,2,1,1).getValue() != ""){  
var guardianInvite = {invitedEmailAddress: sheet.getRange(i,2,1,1).getValue()};
Classroom.UserProfiles.GuardianInvitations.create(guardianInvite, sheet.getRange(i,1,1,1).getValue());  
sheet.getRange(i,3,1,1).setValue("INVITED");
}
    else
    {sheet.getRange(i,3,1,1).setValue("NO ADDRESS FOUND");
} 
} catch(e) {sheet.getRange(i,3,1,1).setValue("ALREADY INVITED")}
}  
//Sends an email invite to a second Guardian
function inviteGuardianTwo() {
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("GuardianTwo");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  
 var Cvals = sheet.getRange("C1:C").getValues();
 var lastRowStatus = Cvals.filter(String).length;
  
  var data = sheet.getRange(2, 1, lastRow, 3).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++)
  try{
if (sheet.getRange(i,2,1,1).getValue() != ""){ 
var guardianInvite = {invitedEmailAddress: sheet.getRange(i,2,1,1).getValue()};
Classroom.UserProfiles.GuardianInvitations.create(guardianInvite, sheet.getRange(i,1,1,1).getValue());  
sheet.getRange(i,3,1,1).setValue("INVITED");
}
    else
    {sheet.getRange(i,3,1,1).setValue("NO ADDRESS FOUND");
}
} catch(e) {sheet.getRange(i,3,1,1).setValue("ALREADY INVITED")}       
}                                  

  

         

Step 8: Listing your Google Classrooms and their Status

At this point you’ll want to be sure you’re logged in as your Google Classroom Admin account. Before you can post topics or even send announcements via the Google Sheet, you’ll need to to know which classes are active. This script will list all of the ACTIVE courses accessible by the admin account. In theory, this should be all of them since you’ve added the account as co-teacher. If there is a class missing, it likely means the assigned teacher has yet to accept the class. Here is the Apps Script for listing your Google Classrooms and Status. Please watch the video to understand how this code interacts with your data.

function listCourses() {
  var user = Session.getActiveUser();
  if (user == "classroomadmin@yourschool.org"){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheetByName('ClassStatus');
  var response = Classroom.Courses.list();
  var courses = response.courses;
  var arr=[];//You could put column headers in here
  for (i = 0; i < courses.length; i++) {
    var course = courses[i];
    var ids = course.id;
    var title = course.name;
    var sec = course.section;
    var state = course.courseState;  
    arr.push([title,sec,state]); 
  }
  sh.getRange(1, 1, arr.length, arr[0].length).setValues(arr);   
}else{
SpreadsheetApp.getUi().alert('Please use the Google Classroom Admin account to perform this function.');
                                 }
                                 } 

Step 9: Adding Topics to Google Classrooms

Again while being logged in with your Google Classroom Admin account, you can pre-populate topics in your teachers’ Google Classroom. This will help give all of your Google Classrooms a uniform structure within which to share resources with students. Here is the Apps Script for adding topics to Google Classroom. Please watch the video to understand how this code interacts with your data.

//Creates Topics in the specified class.  Must run from the Google Classroom Admin/Co-Teacher account.  Super admin does not work.
//All courses must first be accepted before any topics can be created.
function createTopics() {
  
  var user = Session.getActiveUser();
  //change to your own Google Classroom admin account
  if (user == "classroomadmin@yourschool.org"){
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Topics");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Dvals = sheet.getRange("D1:D").getValues();
  var lastRowStatus = Dvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 4).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++){
if (sheet.getRange(i,2,1,1).getValue() != ""){ 
var topics = Classroom.Courses.Topics.create({
      "name": sheet.getRange(i,3,1,1).getValue(),},
      sheet.getRange(i,2,1,1).getValue());
sheet.getRange(i,4,1,1).setValue("CREATED");
}
    else
    {sheet.getRange(i,3,1,1).setValue("NO TOPIC FOUND");
}
}
  }else{
    SpreadsheetApp.getUi().alert('Please use the Google Classroom Admin account to perform this function.');
                                 }
                                 }  

Step 10: Send Announcements to Multiple Google Classrooms

Another feature available to your Google Classroom Admin account is being able to send announcements to multiple Google Classrooms. These could be used send reminders about upcoming field trips, any forms that may be due, etc. Here is the script for sending announcements. Please watch the video to understand how this code interacts with your data.

function createAnnouncements() {
  
  var user = Session.getActiveUser();
  //change to your own Google Classroom admin account
  if (user == "classroomadmin@yourschool.org"){
  var ss= SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Announcements");
  var Avals = sheet.getRange("A1:A").getValues();
  var lastRow = Avals.filter(String).length;
  Logger.log(lastRow);
  var Dvals = sheet.getRange("D1:D").getValues();
  var lastRowStatus = Dvals.filter(String).length;
  var data = sheet.getRange(2, 1, lastRow, 4).getValues();
  Logger.log(data);

  for (var i = lastRowStatus+1; i <= data.length; i++){
if (sheet.getRange(i,2,1,1).getValue() != ""){ 
var topics = Classroom.Courses.Announcements.create({
      "text": sheet.getRange(i,3,1,1).getValue(),},
      sheet.getRange(i,2,1,1).getValue());
sheet.getRange(i,4,1,1).setValue("SENT");
}
    else
    {sheet.getRange(i,3,1,1).setValue("NO ANNOUNCEMENT FOUND");
}
}
  }else{
    SpreadsheetApp.getUi().alert('Please use the Google Classroom Admin account to perform this function.');
                                 }
                                 }       

Supporting this project.

If you found this project helpful, please consider a small voluntary donation in helping make projects like this one freely available.

Venmo: @richard-anderson1978

Paypal: https://paypal.me/richardandersonMD

2 thoughts on “Centrally Manage your Google Classrooms from a Google Sheet

  1. Ellen

    This was an amazing resource for my school! We used the “guardian invite” step and it saved us a ton of time! Richard was able to help with a few hiccups, too. Highly recommended this – many thanks, Richard!

    • Richard Anderson Post author

      You’re welcome! Glad you found this helpful.

Comments are closed.