Automate Sending Email with Apps Script and Google Sheet

Introduction

It has been too long that many people uses Microsoft Excel in day-to-day computing tasks. It's so big that it almost resemble a programming language where non-technical people can create their own spreadsheet programs.

It has many uses with just the default grid-type data entries.

But Microsoft Office developers did not stopped there. They gave it more power by adding a scripting capability to it with VBA or Visual Basic for Applications. Most of the office apps of Microsoft has this VBA at their disposal but I most used it with Microsoft Excel. It was the most appropriate application for me to use it.

But then come the big competition. I'll skip the open source apps that may compete with Microsoft Office and go directly with the big one. This is the Google Sheet from Google.

Introducing Google Sheet

Google Sheets is an online spreadsheet application that allows users to create, edit, and format spreadsheets to organize and analyze information. It is similar to Microsoft Excel but offers the advantage of cloud-based collaboration, making it easy for multiple users to work on the same spreadsheet simultaneously.

Well this was before. As of this writing, Microsoft Excel is now also capable of this with the cloud with OneDrive. Much like a Google Drive but with Microsoft and with Office 365 subscription. But this is not the main focus now. In this post, we will be using the Apps Script that comes with Google Sheet to send messages to a list of emails that are in a Google Sheet table.

Google Apps Script

Google Apps Script is a powerful tool that allows you to automate tasks and extend the functionality of Google Workspace applications like Google Sheets, Docs, Forms, and Gmail. Here's a breakdown of what it is:  

Scripting Platform:

• It's a cloud-based scripting language based on JavaScript.
• This means you can write code to control and interact with Google Workspace apps.  

Automation:

• A key purpose of Apps Script is to automate repetitive tasks. For example, you can:
• Automatically send emails.
• Generate reports.
• Update spreadsheets.
• Manage calendar events.

Extending Functionality:

• Apps Script lets you add custom features to Google Workspace apps. You can:
• Create custom menus and dialog boxes.
• Write custom functions for Google Sheets.
• Build add-ons that enhance the capabilities of Google apps.

Integration:

• It enables you to connect Google Workspace apps with other Google services and external applications.

Development Environment:

• You can write and run Apps Script code directly in your web browser, and your scripts are stored in Google Drive.
 

In essence, Google Apps Script empowers users to tailor Google Workspace to their specific needs, boosting productivity and streamlining workflows.

Creating the List of Email Addresses

For the creation of email addresses, we will put the list on a table in a Google Sheet file. It doesn't have to be a table really, but putting data in a table gives us a sense of organizational paradigm. It tells us that we are somehow organized in a way so it is a very good practice.

I assume that you are familiar with Google Sheet and know how to input data. For this we will make a table. First let's create a text saying "Emails" at cell "A1". This will be the first row and first column. And then we will add emails under it. Then highlight the range that has data and go to Format, then click, "Convert to table".

Now that we have a sample data, we will now go with Apps Script.

On the menu of Google Sheet, click on the Extensions menu and click on the Apps Script menu item. This will open a new tab on the browser. Copy the code shown below:

The code above will log all the emails on the list. Assuming that the sheet name is correct which is "Sheet1" in this example. Now if we want to send a message to each of the emails, we will invoke a command for this.

Modify the code as below:

Then save the file. Now try to click the play button at the top toolbars. It's a triangle button. It will ask you some permissions. such as:

Just click on "Review permissions" then click on the link that says "Go to". This will accept the permissions and go back to your Apps Scipt source code. Then you will see some messages at the log saying "Execution started" and "Execution completed". If the program runs correctly, you will receive emails to the emails you provided on the Google Sheet table.

Conclusion

Now as this article gives you a successful execution, it should also show you the potential that it can contribute to us. With this simple example, we can go for more automation. We can for example send timely emails which is triggered by a timer for example. There are so many potential for this technology that we can definitely get the most of.

I hope that you get value from this content!