Send Charts In Email Using Google Apps Script
Welcome, in this blog I am going to show you how to send Charts in Email using Google Apps Script. Sending charts on Email can be used to send the attendance charts of the students, sales chart for the week, share price charts, etc.
This post is inspired by Martin Hawksey’s excellent post on how to embed and send charts in the email.
In this Blog I will show you how to automate this process. The prerequisites for the blog are as follows:
- Basics of Google Apps Script
- How to create a Chart in Google Sheets
Step1: Sample Google Sheet
The sheet that I will be using in this blog, has some marks of students in various subjects. If you would prefer working with a copy of the Google Form, click here)
This is the data in my Google Sheet. In this Google Sheet I have 4 subjects and the average marks of each exam ranging over 4 different exams. So now I will be creating a few charts that I will be sending on the email.
Step2: Create a few Charts
There are two ways by which we can create charts. The first way is by going to the insert option and clicking charts option.
Here you have a variety of options to play with the data that you have. So I am going to click on Chart .
When I click on chart it will create a blank chart and on the right side it will show me something like this.
Here you can select the chart type, you can select the range of the cells that you have to use to create the chart. If you want you can even specify the x and y axis you want for the chart.
The second way to create a chart is by going to the explore option in the bottom right. When you click on the explore button, it will give you lot of suggestions for charts, you can either chose form them or specify the range and get a customised chart.
I have created two charts in my Google Sheet, that I have to send.
Now we are done with creating charts. Let’s move on to the code.
Step3: Writing the Automation Script
While you are in the sheet, launch the Apps Script Editor.
To do that:
(1) Click on Tools in the main menu.
(2) Click on Script Editor.
This brings up the Script Editor as shown below:
We have reached the Script Editor. Let’s Code.
Let’s understand the different parts of the Code.
function SendChartsinEmail(){
const sheet = SpreadsheetApp.getActiveSheet();
const charts = sheet.getCharts();
Here I have done the following things:
- I have got the active sheet from which I will be getting the charts.
- Next in the variable charts I am using the function getCharts I am going to get all the charts present in the current sheet.
const chartBlobs = new Array();
const emailImages = {};
let emailBody = "Charts<br>"; Here I have done the following:
- Here I have created an array where I am going to store all the charts in from the sheet.
- Secondly I have declared the body of the email, by putting the charts variable in the email body variable.
charts.forEach(function(chart, i){
chartBlobs[i] = chart.getAs("image/png"); emailBody += "<p align='center'><img src='cid:chart"+i+"'></p>"; emailImages["chart"+i] = chartBlobs[i];
Here I have done the following things:
- I have used the for each function . Next I converted the charts from the sheet into an image for the email body.
- Next I am going to align the image of the charts to the centre of the email body.
MailApp.sendEmail({ to: "xyz@gmail.com",
subject: "Chart for average marks per subject",
htmlBody: emailBody,
inlineImages:emailImages});
Now I have got the charts, converted them into images and now I have to send them in an email.
So do that I have done the following:
- Firstly I have used the MailApp in order to send emails.
- Second I have got the email ID of the person who is going to receive the email.
- Next I gave a suitable subject for the email that has to be sent.
- Now I have put the variable emailBody that contains all the images of the charts created in the Google Sheet.
Our code is complete. Select the correct function (SendChartsinEmail) as shown below and run the program.
The script on successful execution will send out an email to the recipient. A sample is show below:
Summary
We saw how you can automate the process of sending out charts in emails. This can be used by organisations to send their weekly sales or the weekly attendance of a school, etc.
