Google Scripts is a powerful tool you could use toautomate Google productivity toolslike Sheets and Docs.
The ability to automatically send emails makes it anextrapowerful tool.
The Google Scripts function you’ll learn in this article will help you accomplish these tasks and much more.
With just a little effort one time, you’ll never have to manually send out data again.
Let Google Scripts act as your own personal assistant, doing all of the work for you.
In this new Send-Emails sheet, you’ll want to create a header.
Each row in this spreadsheet will represent an individual email that will get sent out.
The static information is just text that I’ve typed into the cell.
This won’t change from month to month.
It’s just a part of the email message that always stays the same.
You’ll see how to customize this new menu item at the bottom of this article.
Writing the Automated Email Script
To write your script, you should probably use the script editor.
You’ll find the script editor under theToolsmenu by selectingScript editor.
The script editor will look something like below.
You’ll need to crate a new function at the top of the pane on the right.
Just copy and paste the script below into the script area.
This script may look somewhat complicated, but it actually isn’t at all.
Let’s break it down so you know exactly what each line is doing.
Otherwise everything that comes after won’t work.
Thesheet.GetRange()method will extract information from whatever range of cells you provide within quotes.
Next, thedataRange.getValues()method actually extracts the values and stores them into a two-dimensional array calleddata.
Each element of the array is a row that contains a one dimensional array of column elements.
you’ve got the option to reference each column using a number.
As you’re free to see above, I pulled the column elements into a one-dimensional array calledrowData.
Here is what the concatenation of the email body looks like:
The+character isthe concatenation command.
You put actual static text inside of single quotes.
Next, you add two carriage returns onto the end.
This will start the actual message inside the body two lines down.
This will load a custom menu item into your sheet every time your sheet opens.
This will run your new function and send all of those emails for you.
A job that used to take you several hours will now only require a single click of the mouse!
For example, you canuse Google Scripts to automatically generate a Google Analytics report.
Just think of all of the other wonderful things you could do with those hours!
