How do I send the students’ grades/marks from Google Sheets by e-mail?
Let us assume that the marks obtained by the students of a class are in Google Sheets. If the Google sheet is being shared with all the students with “viewer” permission, each student will learn about the marks of other students in the class. Sometimes, the students don’t want their marks viewed by other students in the same class and want their marks to be shared individually through e-mail.
This article explains how to send the grades/scores obtained by the students, which are available in Google Sheets via e-mail.
Let us assume that the Google sheet contains the following information. i.e., ROLLNO, NAME, EMAIL-ID, CIA-1(A), CIA-1(B), CIA-2, and Total of all the students in the class as shown in the below screen.
First, get the necessary add-ons by clicking the Extensions | Add-ons menu. See the screen below.
It will take you to the Google Workspace Marketplace. From this type Yet Another Mail Merge: Mail Merge for Gmail on the search bar. YAMM is a popular add-on for Google Sheets and Gmail that allows users to send personalized bulk emails directly from Google Sheets. Click Mail Merge for Sheets (YAMM) and follow the installation instructions.
Once installed, a menu item, Yet Another Mail Merge: Mail Merge for Gmail, will be available on the Extensions menu, ensuring the installation.
After successfully installing, open your Gmail account and compose a mail. While composing, don’t specify any recipients’ e-mails in the “To” address. When starting the Mail Merge option, the recipients’ e-mails will be taken from the Google Sheet. Type the subject you wanted to provide for the recipients. Draft the body of the content as follows.
Dear {{NAME}}
Your CIA-1(Part A) score is {{CIA-1(A)}} out of 20, CIA-1(Part B) score is {{CIA-1(B)}} out of 10, CIA-2 score is {{CIA-2}} out of 20 and Total score is {{Total}} out of 50.
Best wishes…
Regards,
Dr. KALPANA P
Course Instructor
Note the {{}} acts as a placeholder for the personalized data. For example, the {{NAME}} will map the value of the NAME column for each e-mail. NAME inside the double flower brackets is the column name in the Google sheet. Note: The variable names/column names are case-sensitive. In the same way, whatever value we want to take from the Google sheet has to be placed inside the placeholder.
Once the e-mail has been drafted, choose the Start Mail Merge option from Yet Another Mail Merge: Mail Merge for Gmail.
Type the Sender Name, choose the Email template (the subject specified in Gmail) from the auto-populated values, and click the Send Mail button to send the mail to the recipients.
Once sent, a column named “Merge Status” will be auto-populated in the Google sheet stating the mail status. The status may be EMAIL_SENT once sent, EMAIL_OPENED once the email has been opened by the recipients, and RESPONDED if any reply from the recipient for the sender.
Note: The students’ e-mail IDs are mandatory.
#HAPPY LEARNING