Google Sheets Automation Using Google Apps Script

Welcome. In this blog I am going to send emails using Google Apps Script. First I am going to create a Maths Quiz in Google Google Forms. In the Google Form, I am going to ask for their name, email address and ask three questions on Maths. The advantage of using the quiz feature is that it will automatically calculate the score secured by the students.

After all the responses have been collected, take them to a Google Sheet. Using the code that I have written, an email will be sent to the students that contains the details of the student, marks secured by the student and remarks made by the teacher on the marks scored by the students. So let’s get started.

If you need to refresh your knowledge on how to create a Quiz in Google Forms, click on the link given below:

Let us assume that all form responses have been received in the Sheet associated with the form. As you can see, the form contains the following:

  • Name of the student
  • Email address of the student
  • Roll Number of the student
  • Answers chosen by the student to 3 questions asked in the quiz
  • Score that has been calculated as per the points you give to each correct answer in the quiz

Here you can see all the responses. Now we have write the code, so to open the the Script Editor , go to the tool and click on Script Editor.

When you click on it, you should see something like this:

We have reached the Script Editor. Let’s Code.

Let me explain, the different parts of the code:

//Get the data starting from values (i.e. skip the header row)var data=    responses.getRange(2,1,responses.getLastRow()-1,6).getValues();Logger.log(data);

Now we are going to get the data from the spreadsheet by first specifying the range and then get the values accordingly, which I have put in the variable data specify the range you have to use the getRange function and specify the row and the columns. Since responses will keep on coming in , I have used the getLastRow function. I have written -1 in order to avoid the header .

To run the program you have to click on the play icon and ensure that the sendEmail function is selected as shown .

After the script has finished execution, we will now take a look at the output of the Execution Logs.

To do that, select View from the Main Menu, click the Logs option.

When you click on Logs you will see something like this.

If you can see the values, then you can proceed ahead.

//Iterate each row of datadata.forEach(function(row, i) {// variablesvar Marks = row[1]; // marks secured by the studentsvar Name  = row[2]; // Name of the studentvar Email_ID = row[3];// Email address of the studentvar Roll_no  = row[4]; // Roll number of the student

Here I am going to use the forEach function, that will take the data from the Sheet and put them in suitable variables. I have taken the following details from the sheet:

  • Marks
  • Name
  • Email
  • Roll number

Now I have to send out emails, with marks secured by the student. So first I am going to create a function for the body of the email, so that I don’t have to write a specific body for each of the students.

function formatEmailBody(Rating, Name, Marks,Email_ID,Roll_no) {var emailTxt = "Teachers remarks: " + Rating + "<br><br>" +" Name           :  "  + Name      + "<br><br>"   +" Score          :  "  + Marks     + "<br><br>"   +" Email Address  :  "  + Email_ID  + "<br><br>"   +" Roll Number    :  " + Roll_no   +  "<br><br>";return emailTxt;}

Here the name of the function is formatEmailBody that I have created for the body of the email. The body has been put in a variable. I have taken the following parameters :

  • Rating[Remark given by the teacher for the marks secured by the student]
  • Name of the student
  • Score[marks secured by the students]
  • Roll number of the student
  • Email address of the student

Now I have to send emails with marks secured by the student.

if (Marks <= 10){// send an email for marks less than equal to 10var body =  formatEmailBody("Bad",Name, Marks,Email_ID,Roll_no);}else if (Marks <10) {// send an email for marks greater than 10var body =  formatEmailBody("Moderate",Name,Marks,Email_ID,Roll_no);}else if (Marks <= 15) {// send an email for marks greater than equal to 15var body =  formatEmailBody("good",Name, Marks,Email_ID,Roll_no);}
else if (Marks <= 20) {// send an email for the rating 2var body = formatEmailBody("Very Good",Name,Marks,Email_ID,Roll_no);}
else if (Marks <= 25) {// send an email for marks greater than equal to 25var body = formatEmailBody("Great",Name, Marks,Email_ID,Roll_no);}

So according to the marks scored by the students, a mail will be sent to the students. There will be a special remark for the marks scored by the students.The subject is going to be same for all the students.

To send an email I have used the GmailApp.sendEmail function. This will take the email address, subject and the body for the email and send it to the students.

Our code is complete and is ready for execution.Select the correct function and run the program. After running the program an email will be sent and it will look something like this.

This can be used by the teachers. This will reduce the work of the teachers. There are 4 easy steps :

  • Create a Quiz in Google Forms
  • Link the Google Form to a Google Sheet
  • Open the Script Editor and paste the code
  • Run the program .

I hope you have learnt how to send emails from the responses of a Google Form using Google Apps Script.

Do give me feedback. My email is aryanirani123@gmail.com

Currently pursuing B Tech at MPSTME. Technical Blogger, interested in Google Workspace. Hope to remain a life long student. Created a course on Google Classroom

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store