Up next in 10
Google Apps Script Tutorial to Schedule & Send HTML5 Template Emails inside Google Sheets
Dec 21, 2025
Buy the full source code of application here:
https://buy.stripe.com/4gwaHYfbH1So43m5Lg
Show More Show Less View Video Transcript
0:00
Uh hello friends, today in this tutorial
0:02
I will be showing you that how basically
0:04
we can u schedule our emails inside
0:08
Google appcript and we will be able to
0:11
now
0:12
send out email addresses using Google
0:14
app script and for this we will be using
0:17
our uh Google sheet. You can see all
0:21
this data will be returned to us inside
0:23
the email address. This is we have three
0:25
rows out there inside of a Google sheet.
0:27
You can see name, age and country. So
0:29
all these data we will be sending it as
0:32
a email address inside Google appcript
0:34
and we will also beuling that email. So
0:36
let let's suppose you want to execute
0:38
that code at a specified date and
0:40
specified time. You can even mentioned
0:42
it it inside the trigger option. So
0:45
inside Google appcript we have a trigger
0:47
option. Basically I have basically made
0:49
a trigger here. You will see that uh if
0:53
I if I edit this you will see this is it
0:56
will only run at this specific date and
0:58
time. You can even run it as hourly
1:01
timer. You can put a every hour, every
1:03
minute, every second. You can send out
1:06
using this trigger option. You can
1:08
schedule your emails inside Google
1:10
appcript. So I will be taking a complete
1:12
example on how to send the Google sheet
1:15
data as email address and schedule it.
1:17
So for that process guys, I have written
1:19
a complete blog post on my tutorial
1:20
website.com.
1:22
So if you want the full source code of
1:24
this tutorial, I will definitely
1:26
recommend that you go to the description
1:28
of this video and follow this blog post
1:30
alongside watching this video. So let's
1:32
start building this application guys. So
1:34
first of all we need to do we need to go
1:36
to uh Google sheet. So here we will
1:39
create brand new sheet here. So just go
1:42
to your uh go to sheets here and here we
1:45
will create a blank sheet and you can
1:48
rename your sheet here accordingly. Let
1:50
me rename this to
1:53
uh student data.
1:58
So basically guys now we can have some
2:00
columns here name of the student age and
2:04
the subject.
2:07
So we can give some names here guys.
2:13
Let me rename this to name and here
2:17
mark 23. Uh this is maths. Let me add
2:21
some more entry here. Adams
2:24
uh 34 uh science. So we have these two
2:28
two columns out there. Three columns and
2:30
two rows. So now what we will do guys
2:32
basically we need to create a page range
2:35
out there. So you need to select these
2:38
rows of data that you need to send out
2:40
inside the email address. So just select
2:42
it using right click. So it will select
2:44
and now go to the data option. And here
2:46
guys we have this option called as named
2:48
range. You need to create a named range
2:50
out there. So it will automatically
2:52
select the columns from A1 row which is
2:55
A1 to C3. You will see that A1 to C3 is
2:58
selected. Now you can name your range
3:00
here. Let's suppose I say student data
3:03
and click on done. So our range is
3:05
created guys. So only these rows of data
3:07
will be uh sent to the email address. So
3:11
now what we need to do? We need to go to
3:12
extensions and go to appcript. And here
3:15
we will write this code to execute to
3:18
actually send out the email address. So
3:20
here we just need to write the function
3:23
guys. It's very simple. So let me zoom
3:25
in and show to you how basically it is
3:27
done.
3:29
So we will write a function guys which
3:31
will called as send email
3:34
and which will actually send the email
3:36
to the
3:39
uh Gmail ID. So here first of all we
3:42
will say we will construct a variable
3:44
student data and we will fetch all the
3:46
data guys which from the spreadsheet we
3:49
will write a function here for that
3:51
which is called as get data. Let me
3:53
write this function and inside this
3:55
function guys we will fetch all the data
3:57
which is present inside the current
3:59
spreadsheet. So for that we will first
4:01
of all fetch the values and for that we
4:04
will use the spreadsheet app library and
4:08
inside this we will get the active
4:10
spreadsheet and after this inside this
4:12
we will get the sheet by its name. So
4:15
get sheet by name and you can see every
4:20
every data has a sheet accordingly. You
4:22
can see this is sheet one. You can even
4:24
add sheet two as well. Sheet one sheet
4:26
two. So this is sheet one. So we need to
4:28
only focus on sheet sheet one and inside
4:32
this we have created this range out
4:34
there guys you can see student data. So
4:36
we need to get the data inside this. So
4:38
we will simply say get range. This is a
4:40
method and we will put the name here
4:42
which is called as student data and
4:45
inside this there is a method out there
4:48
which that's it. I think that's all.
4:51
Yeah the get values. We need to get all
4:54
the values which are present inside this
4:56
range. That's it. This is the function.
4:59
That's it. So now this returns a object
5:00
guys. So now what we need to do is that
5:03
we need to shift this data. So values
5:05
dot shift basically
5:09
it removes the first element from an
5:11
array and returns it. So it returns let
5:15
me declare uh students here and empty
5:19
array and for this values for each guys
5:23
values for each this is an array and
5:26
basically we have the value here. So
5:30
inside we will declare student object
5:34
guys this will be empty object. Now we
5:36
will be assigning all the values which
5:37
are coming. So we have three values.
5:39
First is student dot uh name
5:44
and this will be coming through value
5:46
zero and then we got the student age and
5:50
this we will get using value one and
5:54
then we got the student
5:56
subject this we can get using the value
6:00
two because indexing starts from zero
6:03
that's why you can see this will be the
6:05
zero index 1 2 like this after getting
6:09
all this information. Now we need to
6:11
push the data inside the array. So we
6:12
will use the push method of JavaScript.
6:16
So this will push this object inside
6:18
this array. That's it. Now we simply
6:20
need to return the
6:23
we need to return the data which is
6:25
return players. That's it. So that's all
6:27
that we need to do guys. So now after we
6:31
get this data we need to construct a
6:33
email. So for this we will construct the
6:36
body of the email. So we will say that
6:38
get email text we will define this
6:40
function and here we will pass the data
6:42
which is student data. So now we need to
6:46
construct this function guys which is
6:47
get email text and here we will get the
6:50
data here inside this we will
6:54
have the student data here as a argument
6:57
and here we will be constructing HTML 5
7:00
template formatted email. So for that we
7:04
will be constructing a simple for each
7:07
loop. So student data and for each and
7:12
basically for each student we have these
7:15
three properties name, age and subject.
7:18
So we will construct this text object to
7:20
be like this text plus and here we will
7:24
construct the embed the name and then we
7:27
will insert a new line character. So
7:29
this is a new line character. This will
7:31
coming on new line player.h
7:35
again we will insert a new line
7:36
character
7:41
and then we will have the player
7:44
country.
7:47
That's it.
7:50
[snorts] So some more new line you can
7:52
add this character here like this.
7:57
That's all that you need to do. So
7:59
simply close this. And now we have
8:02
constructed this text here guys. So now
8:05
we can simply return from this function
8:07
return text. That's it. So now we have
8:10
constructed the body of the email guys.
8:12
So now we can use simply the mail app
8:15
and we can send the email address. Send
8:17
email. We can use this function. And
8:19
here we can provide the options. Two
8:21
option will be the email address to
8:22
which we are sending it. So we are
8:24
sending to this email address. Set
8:26
suppose this one.
8:30
You can even add multiple email
8:32
addresses as well. Just put a comma
8:33
here. And you can add two email
8:35
addresses. Let me add two one.
8:39
So you can just add multiple emails as
8:42
well. After put a comma and now we need
8:45
to put the subject field. Subject will
8:48
be simply player
8:50
sorry student
8:53
data. And then we have got the body. So
8:56
body is equal to body that we have
8:58
constructed. That's it guys. So we have
9:01
now written this Google app script which
9:03
will actually first of all extract the
9:05
data from the spreadsheet whichever data
9:08
is present and then it will construct
9:09
the uh custom template
9:13
HTML template for the email and then it
9:15
will automatically send to these two
9:17
email addresses which I mentioned. So
9:19
now we need to simply save the project
9:22
and it will save this project. So let me
9:26
run this for normally guys. If I run if
9:28
I run it normally click on run option.
9:30
So as soon as you run this you will need
9:33
to first of all grant the permission for
9:36
using the spreadsheet and the mail app.
9:39
So it will take some time if you run it
9:42
for the very first time but subsequently
9:44
it will not take some more time. So you
9:46
need to review permissions. You need to
9:48
select your account and then you need to
9:50
go to advance and
9:52
grant the per access. So you can see
9:55
Google sheet access and send email
9:57
access. Simply click allow and as soon
9:59
as you click allow guys now your
10:01
authorization is completed. Execution
10:03
started and it is saying players is not
10:05
defined. Uh where uh I think 31 line.
10:12
Okay. Okay. So this needs to be
10:14
students. Sorry.
10:16
So students dotpush and this needs to be
10:19
student not player and here also this
10:22
needs to be students.
10:26
So just make sure that you not spell it
10:28
wrong. So now if you click again run it
10:31
will run your function guys. So it will
10:33
take some time. Execution started again
10:36
at line number 15. Sorry, [snorts] I
10:40
have this needs to be student
10:46
student
10:50
and student
10:54
and this needs to be subject not
10:57
country.
11:02
So that's all guys. Now if you save your
11:05
project and uh now run this project here
11:09
execution started execution completed.
11:11
Now if I check guys go to my email
11:14
address you will now see email address
11:16
will be there student data subject is
11:18
there. If you open this you will see
11:20
your custom HTML template of email. You
11:22
can see marks 23 maths Adams 34 science.
11:26
So whatever data is present inside your
11:28
Google spreadsheet is mailed here
11:30
automatically.
11:32
So now if I check the second one which
11:34
is this one here also if I open Gmail
11:38
you will see a email address will be
11:40
received here also if you see student
11:43
data. So automatically it can uh send it
11:46
to multiple people you just need to put
11:47
a comma and now we will make sure guys
11:49
we will schedule this email. Soululing
11:52
is very important. Let's suppose you
11:53
don't need to manually click this run
11:55
button every time you want to run the
11:57
script. You can even put a trigger
11:59
option also. So there is a triggers
12:01
option inside Google appcript. Simply go
12:03
to this option and here you can add a
12:06
trigger. Simply click this button and
12:08
here basically we can simply say we need
12:10
to run this function here which is send
12:12
email and this head will be the same
12:14
here and the event source. This needs to
12:17
be important. You need to select here
12:19
timed driven and select time of so here
12:23
we can even every minute you can even
12:26
send this email or every hour as well
12:28
every day as well you can select the
12:32
time of day at which time you need to
12:34
send out you can even customize this
12:36
accordingly weekly you can even say
12:39
which day of week you need to send out
12:41
every Tuesday or every like this also
12:44
and uh every monthly also
12:48
at which day of month you need to send
12:50
out this email. And if you want to
12:53
customize this specific date and time,
12:55
you can even write the specific date as
12:57
this format 2022.
13:01
Uh today's date is 27.
13:04
Sorry, month is 11:27. And then you need
13:07
to write the time. So current time is
13:10
5:53 a.m. Let me send this to 555.
13:16
Let me select this. So this needs to be
13:18
zero.
13:20
So now if I click the save option guys.
13:22
So whenever the clock reaches 555 in 1
13:25
minute time you will see automatically
13:27
the email will be sent. Let me make some
13:30
modification here.
13:33
Let me add the third row.
13:37
So now we have added the third row as
13:39
well. But this row will not be added
13:41
because we have put a range here. That's
13:44
why this row is will not be there inside
13:47
it. Let me add this row.
13:52
Only this these rows which we have
13:54
selected inside the date range will be
13:56
mailed. Apart from that no else row will
13:59
be there. So this is the advantage of
14:01
using ranges. So you can see it is 5:54
14:04
here. So automatically whenever the time
14:08
reaches 5:55
14:10
automatically the email will be sent. So
14:13
now the time is exactly 5:55.
14:17
So just wait
14:21
it will automatically the email will
14:23
automatically get received. So just wait
14:26
here.
14:34
So 555.
14:41
So this is basically a time based
14:43
trigger option and it will execute this
14:45
function at the specific time that you
14:47
have.
14:49
You can see we have put this time here
14:51
5:55.
14:52
So you can see this email is received
14:56
here at 5:55
14:58
and if I check this email address you
15:00
can see student data 555 a.m. So you can
15:04
schedule your email guys like this you
15:06
can see that basically
15:10
you can see
15:12
the data is received here you can see
15:16
23 maths Adams 24 science.
15:21
So in this way guys you can uh schedule
15:23
your emails using Google app script
15:26
based upon a specific date and time and
15:29
you can use their Google sheet data. So
15:32
thank you very much for watching this
15:33
video. If you like this video, then
15:35
please hit that like button and also go
15:36
to the description of this video to get
15:38
all the source code and I will be seeing
15:41
you in the next