Get Classroom Data into your Google Sheet using Google Apps Script

Welcome!

In this blog, I am going to show you how to get Google Classroom Data into your Google Sheet using Google Apps Script. Using this, you can keep track of all the classes that you have in your Google Classroom.

Sample Google Sheet

The Google Sheet that I will be using for this blog contains the following details.

1. Name of the Class
2. Section
3. Room
4. Description of the Class
5. Enrollment Code of the Class

Using Google Apps Script, we are going to get data about all the classes in the Google Classrooms and set them in the Google Sheet.

Write the Automation Script

While you are in the Google Sheet, let's open up the Script Editor to write some Google Apps Script. To open the Script editor follow these steps.

1. Click on Extensions and open the script editor.

2. This brings up the Script Editor as shown below.

We have reached the script editor, let’s code.

Step1: Add the Google Classroom API

Before writing the code, we have to add the Google Classroom API to our Apps Script project, to do that follow these steps:

1. Click on the plus sign, next to services.

2. Scroll down and click on the Google Classroom API.

3. After selecting the API, click on Add.

function getData(){
   
   const CLASS_DATA = Classroom.Courses.list().courses;
   //console.log(CLASS_DATA);
   const DATA = CLASS_DATA.map(c => {
           return [c.name, c.section,c.room,c.description,c.enrollmentCode];
   });
   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const sheet = ss.getSheetByName("Sheet1");
   const START_ROW = 2;
   const START_COLUMN = 1
   sheet.getRange(START_ROW,START_COLUMN,
               DATA.length,DATA[0].length).setValues(DATA);
}

Our aim for this blog is to get all the classes data in the Google Classroom into our Google Sheet using Google Apps Script. Here I have created a new variable called CLASS_DATA, which is going to be containing all the data about the classes in the Google Classroom. To do that we are going to be using the Classroom.Courses.list().courses function which is going to get all data of classes in the Google Classroom.

const DATA = CLASS_DATA.map(c => {
           return [c.name, c.section,c.room,c.description,c.enrollmentCode];
});

In the CLASS_DATA variable, there are a lot of unwanted details that we don't require. As we had discussed previously, we want some specific details from the classes in Google Classroom, such as the name of the classroom, section, room description, and the enrollment code of the classroom.

To get specific details from the CLASS_DATA variable, we are going to be using the map function and then return the specific details that we want from the CLASS_DATA variable. Here we are going to open the map function and use the dot operator and get the name, section, room, description, enrollment code of the Google Classroom by writing c.name, c.section, c.room, c.description, c.enrollmemtCode, respectively.

After successfully running the code, we will have all the required details of the classroom in the DATA variable, such as the name, section, room, and more.

   const ss = SpreadsheetApp.getActiveSpreadsheet();
   const sheet = ss.getSheetByName("Sheet1");
   const START_ROW = 2;
   const START_COLUMN = 1

Now that we have got all our classroom data, it's time to set the values back to the Google Sheet. Here I have got the active spreadsheet using the getActiveSpreadsheet() function. After getting the active spreadsheet, we have to get the active sheet that we need to work with. There are various ways by which you can get the active sheet, but for this case, we are going to be getting the sheet using the getsheetByname() function inside which we are going to be passing the name of the sheet as a parameter.

Next, I have declared two variables that will be used to set the values in the Google Sheets. The first variable START_ROW will be used to make sure that no change is made to the first row of the Google Sheet, which has the headings of the details that are going to be pasted in the Google Sheet. The second variable START_COLUMN, will be used to make sure that the values to be set will start from the first column.

sheet.getRange(START_ROW,START_COLUMN,DATA.length,DATA[0].length)
.setValues(DATA);

Now that we have got everything ready, it's time to set the values to the Google Sheet. To do that we are going to first start by getting the range where we have to paste the values in the Google Sheet. To do that we are going to be using the getRange() function in which we will be passing the variables that we declared previously such as the START_ROW, START_COLUMN, and more. After getting the range where we have to set the classroom data, we are going to be using the setValues() function, in which we will be passing the DATA variable that will be containing all the classroom details.

We are done with our code, let's move towards the output.

Step2: Check the Output

Now that we are done with the code, its time to check the output. Select the correct function that we have to run (getData) and click on run.

On successful execution of the script, the code will set all the details in the Google Sheet, shown as follows.

Here you can see all the details have come into the Google Sheet successfully.

What is Google Classroom?

Google Classroom is a free learning platform developed by Google for schools that aim to simplify creating, distributing, and grading assignments.

Interested in learning Google Classroom? check out this course to know more.

Summary

We saw how you can get classroom data into your Google Sheet using Google Apps Script and the Google Classroom API.

I hope you have understood how to get classroom data into your Google Sheet. You can send your feedback to aryanirani123@gmail.com.