In the following tutorial, you will learn how to check the value of a cell in Google Sheets. If the value is over a certain threshold limit, you can automatically send an alert email to any email address you like. There are many uses for this script. You could receive an alert if the daily earnings in your sales report dip below a certain level. Or you could get an email if your employees report that they’ve billed the client for too many hours in your project tracking spreadsheet. No matter the application, this script is compelling. It’ll also save you the time of having to monitor your spreadsheet updates manually.
Step 1: Sending an Email with Google Sheets
Before you can create a Google Apps Script to send an email from Google Sheets, you’ll also need a Gmail email address, which Google Apps Script will access to send out your alert emails. You’ll also need to create a new spreadsheet that contains an email address. Just add a name column and an email column, and fill them out with the person you want to receive the alert email.
Now that you have an email address to send an alert email to, it’s time to create your script. To get into the script editor, click on Tools, and then click Script editor. You’ll see a script window with a default function called myFunction(). Rename this to SendEmail(). Next, paste the following code inside the SendEmail() function: Here’s how this code works:
getRange and getValues pull the value from the cell specified in the getRange method.var message and var subject define the text that’s going to build your alert email.The MailApp.sendEmail function finally performs Google Scripts send email feature using your connected Google account.
Save the script by clicking the disk icon, and then run it by clicking the run icon (right arrow). Keep in mind that Google Script needs permission to access your Gmail account to send the email. So the first time you run the script, you may see an alert like below.
Click on Review Permissions, and you’ll see another alert screen that you’ll need to bypass. This alert screen is because you’re writing a custom Google Script that isn’t registered as an official one.
Just click on Advanced, and then click the Go to SendEmail (unsafe) link. You’ll only need to do this once. Your script will run, and the email address you specified in your spreadsheet will receive an email like the one below.
Step 2: Reading a Value From a Cell in Google Sheets
Now that you’ve successfully written a Google Apps Script that can send an alert email, it’s time to make that alert email more functional. The next step you’ll learn is how to read a data value out of a Google Spreadsheet, check the value, and issue a pop-up message if that value is above or below an upper limit. Before you can do this, you’ll need to create another sheet in the Google Spreadsheet you’re working with. Call this new sheet “MyReport.”
Keep in mind that cell D2 is the one you’re going to want to check and compare. Imagine that you want to know every month whether your total sales have dropped below $16,000. Let’s create the Google Apps Script that does that. Go back into your Script Editor window by clicking on Tools and then Script Editor. If you’re using the same spreadsheet, you’ll still have the SendEmail() function in there. Cut that code and paste it into Notepad. You’ll need it later. Paste the following function into the code window. How this code works:
Load the value from cell D2 into the monthSales variable.The IF statement compares the monthly sales in cell D2 to $16,000If the value is over 16,000, the code will trigger a browser message box with an alert.
Save this code and run it. If it works correctly, you should see the following alert message in your browser.
Now that you have a Google Apps Script that can send an email alert and another script that can compare a value from a spreadsheet, you’re ready to combine the two and send an alert instead of triggering an alert message.
Step 3: Putting It All Together
Now it’s time to combine the two scripts you’ve created into a single script. By this point, you should have a spreadsheet with a tab called Sheet1 that contains the alert email recipient. The other tab called MyReport contains all of your sales information. Back in the Script Editor, it’s time to put everything you’ve learned so far to practice. Replace all of the code in the script editor with your two functions, edited as shown here. Notice the edits here. Inside the IF statement, paste the SendEmail script inside the CheckSales() function inside the if statement brackets. Secondly, concatenate the monthSales variable to the end of the email message using the + character. The only thing left to do is trigger the CheckSales() function every month. To do this, in the script editor: Click Save to finalize the trigger.
Every month, your new script will run and compare the total monthly sales amount in cell D2 to $16,000. If it’s less, it’ll send an alert email notifying you of the low monthly sales.
As you can see, Google Apps Scripts packs a lot of functionality in a small package. With just a few simple lines of code, you can do some pretty amazing things. If you want to experiment some more, try adding the $16,000 comparison limit into another cell in the spreadsheet, and then read that into your script before making the comparison. This way, you can change the limit just by changing the value in the sheet. By tweaking the code and adding new code blocks, you can build upon these simple things you learn to build some amazing Google Scripts eventually.
