We get this question all the time: how do I make my spreadsheet automatically send out an email when a cell value changes? How do I make my spreadsheet send a screenshot of itself when a value changes? How do I send myself an email when someone else changes the value of a spreadsheet?

Say you’re tracking bounce rates for a particular campaign and you want your spreadsheet to automatically send you a email when bounce rates go above 10% or maybe send you an email when your budget goes below $1,000 — how do I do this in Excel or Google Sheets? Is it even possible for Google Sheets to automatically send out an email?

The short answer to this question is yes, this is possible BUT it does require a moderate to an advanced user to pull this off. If you want the quick way, you can always do this with our Factivate platform (a marketing decision engine that automates your spreadsheets, dashboards, and KPI alerts in one place to help you become even better marketers).

The Factivate way (#easy)

Here’s a quick video on how to use Factivate’s spreadsheet actions to trigger an email using Factivate’s engine or the Google Sheets Add-on:

For information on singing up for either Factivate or the Factivate Google Sheets Action add-on, you can sign up here: Spreadsheet Trigger Actions https://factivate.com/spreadsheet-actions/

The beauty of using this functionality inside Factivate is that its triggers fire off from real-time data (meaning data is automatically updating itself).

Step by step guide for Spreadsheet triggers / actions using Factivate

Go to the actions panel, select the email icon, then add your formula (example: B2<10%), and then add an email subject, the person who will get the email and click on save. Once that’s done, your Factivate spreadsheet will automatically generate an email once B2 changes below 10%.

Easy…Fast…and Effective. Check out the video below to see how to send an email from your spreadsheet with a SaaS metrics example:

The Old Complicated Google Sheets way (#harder)

If you’re dead set on learning how to do this with Google Sheets, however, you will need the following Script:

Here’s a step by step guide on what you need to do to implement this script that auto sends email when a spreadsheet value changes:

1) You need to decide how you want this script to run; with a button click or automated. It’d recommend automated so you don’t have to worry about it.

2) When you’re in the script editor, go up to the clock icon *should say triggers when you hover/click). You’ll want to set up a condition that will run your function.

Clock icon > Click to set up new trigger > “Run checkValue”

Change “time driven” to “Spreadsheet”

Change “on open” to “on edit”

3) Now that you have the script running by itself, we need to make some adjustments to the code. First we need to pass the “edit argument”. So instead of :

function checkValue()

we’ll have

function checkValue(e)

This will pass along information about what was edited within your spreadsheet. Since we only care about once cell, even if it’s edited, we’ll have to define the (cell location) of the ‘edit’ variable (‘e’).

var rangeEdit = e.range.getA1Notation();

Then, before we check to see if the Cell value C7 is over your max, we’ll first check to see if C7 was even the most recently updated cell (this way you won’t get an email every time you update your spreadsheet).

if(rangeEdit ==C7) [ //do Mailapp.send ; ]

This is how the script will look in the end:

function checkValue(e)

{

var ss = SpreadsheetApp.getActive();

var sheet = ss.getSheetByName(“Money Transfers”);

var valueToCheck = sheet.getRange(“C7”).getValue();

var rangeEdit = e.range.getA1Notation();

if(rangeEdit == “C7”)

{

if(valueToCheck > 100)

{

MailApp.sendEmail(“*******[email protected]”, “Campaign Balance”, “Balance is currently at: ” + valueToCheck+ “.”);

}

}

}

If you have any questions about how this should work, feel free to email us at [email protected] or submit a comment below. We’re happy to help with all spreadsheet automated reactions. If you have any new actions you’d like us to add, please email [email protected] so we can add these to our backlog.

About Factivate:

Factivate is a business decision engine for marketers and advertisers that automates their spreadsheet reports, dashboards, and user actions in a beautiful package to help them make optimal decisions. Factivate’s ease of use and advanced capabilities require no learning curve or programming knowledge and have saved users an average of 250 hours per user in manual reporting tasks and while improving decision timelines by a factor of 25x in some cases.