Google

How to Trigger Emails in Google Sheets

How to Trigger Emails in Google Sheets

Microsoft Excel has been the king of computer spreadsheets for decades, both for personal and business use. While most large businesses will probably remain loyal to Microsoft, others may turn to Google. With Google Sheets you get most of the tools commonly used in Excel, and this application is completely free.

Although Google Sheets may lack some of the advanced features provided by Excel, I’m surprised at how much you can do with Google tools. One example is sending emails directly from your spreadsheet.

Send Emails Directly from Google Sheets

If you’ve searched through the Google Sheets menu to find e-mail delivery options, you might realize it’s not there. Although this might seem like a spreadsheet software tool should, this requirement is a bit more complex. The reason is because it requires too many variables that cannot be handled by simple tools.

And this is precisely where Google Sheets is close to Excel, because it also allows you to write and edit your own scripts. With only a few lines of code, you can use Google Sheets to do more than you might initially see.

1(4)

Preparing Sheets

This is the simplest and best scenario to help you understand how Google Sheets coding works. There are three pieces of data that are all needed for this: email address, email subject, and message text. The best way is to use your own email address here, so you can check whether the script has done its job or not.

To get started, first create a spreadsheet that looks like this:

2(4)

Writing a Script

Now it’s time to write the code for the spreadsheet. In the menu at the top of the page, first click the “Tools” tab, and then “Script Editor”. When the editor opens, you will see the initial template for coding. This looks like this:

function myFunction () {

}

To create this script yourself, you need to change its name. To do this, simply replace the text “myFunction” with “sendEmail” for example. Of course, you can name this function whatever you want, just make sure it’s intuitive enough so you know what it is.

Now that you have changed the name of the function, it is time to write the code. In this case, simply copy the code below and paste it between two curly brackets for your function. The end result will look like this:

function sendEmail () {

var ss = SpreadsheetApp.getActiveSpreadsheet()

var sheet1=ss.getSheetByName(‘Sheet1’);

var emailAddress = sheet1.getRange(2,1).getValue();

var subject = sheet1.getRange(2,2).getValue();

var message = sheet1.getRange(2,3).getValue();

MailApp.sendEmail(emailAddress, subject, message);

}

The next step is to save this script. Click the “File” tab from the top menu, and click “Save”. In the “Save” menu, set a name for your script. For example, you can use the function name for reference, so type “sendEmail” and click the “Save” button.

Examining Manuscripts

To check for script errors, click the play button from the toolbar above the Script Editor main screen. As a precaution to prevent running dangerous scripts, the application will ask you for permission to run your scripts. To do this, follow these steps:

  1. Click “Review Permissions”.
  2. A warning appears, letting you know that Google doesn’t recognize the script writer.
  3. Because you are a script maker and you know that it’s okay to run it, click “Advanced”.
  4. Scroll down through the options and click “Go to sendEmail”.
  5. A confirmation screen will appear now, so click “Allow”.

After it’s done, a yellow label will appear at the top of the screen, letting you know that your script is running. When the label disappears, your script has completed its task. Now open your inbox and check if you have received the email. Also, check that the subject and text of the message are exactly what you defined in your spreadsheet.

Create an Action Button

If everything is going well with the test, now you need to add an action button that will allow you to trigger sending emails manually.

  1. Click the “Insert” tab from the top menu.
  2. Click “Image”.
  3. When the drawing window opens, click the “Shape” button from the window menu.
  4. Click “Shape”.
  5. Select the “Rounded Rectangle” option, the second icon from the left.
  6. Click and drag the rectangle to the size you want from your action button.
  7. Now double click the rectangle to add text. You can type “sendEmail”, such as the name of your script.
  8. When finished, click the “Save and Close” button in the upper-right corner of the window.

3(4)

Now you need to connect your script to this button.

  1. Click the “sendEmail” button.
  2. Click the three dots in the upper right corner of the button.
  3. Click “Set script”.
  4. Type the name of your function, which in this case is “sendEmail”.

This will eventually allow you to manually trigger email delivery to your address whenever you click on this button.

From Sheets to Email

Although this feature is not available as a menu option, it’s good that there is a way to make it work. Even though you don’t know how to code, this guide shows you how to create your own scripts. Now that you know how to automatically trigger your spreadsheet to send e-mail, make sure you experiment with this option.

Related posts

How to Attach Any File to Google Meet Video Chat

Howto

How to enter a text box in Google Docs

Howto

How to Make a Quiz on Google Classroom

Howto

How to Google Meets Records Go and See Them

Howto

How to change margins in Google Docs

Howto

How to Set Multi-Step Directions on Google Maps

Howto

How to Fix Green Cell or Green Line Error on Google Sheets

Howto

How to Use Google’s Assistant Action Block for Accessibility

Howto

How to turn off Google Assistant

Howto