How to have your spreadsheet automatically send out an email when a cell value changes

By Marketing Analytics, Uncategorized

We get this question all the time: how do I make my spreadsheet automatically send out an email when a cell value changes?

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 Excel to automatically send out an email?

The short answer to this question is yes, this is possible BUT it does require a moderate to advanced user to pull this off.

I should mention that to do this in Factivate, this type of functionality can be added within 2 clicks. To do so, 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 onceB2 changes below 10%. Easy…Fast…and Effective. Check out the video below to see how to send an email from your spreadsheet:

 


 

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

Screen_Shot_2016-01-14_at_2.14.10_PM.png

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(“*******8@gmail.com”, “Campaign Balance”, “Balance is currently at: ” + valueToCheck+ “.”);

}

}

}

If you have any questions about how this should work, feel free to email us at info@factivate.com or submit a comment below. We’re happy to help with all spreadsheet automated reactions.

 

If, on the other hand, you don’t want to build these queries, you can sign up for a free trial for Factivate and use our automated spreadsheet reactions. To sign up, simply click on the button below:

 

 


About Factivate:

Factivate is a software company part of the Google for Entrepreneurs community in Durham, NC. Factivate provides business users with a cloud-based data analytics platform that can sync with different online tools to create automated, real-time spreadsheet reports and web dashboards with just-in-time business reactions. The result of our intelligent spreadsheet is accurate data analysis that drives better business decisions while reducing manual spreadsheet report time and errors by a factor of 150+ hours/employee/year. Factivate requires no learning curve or programming knowledge. In sharp contrast to the complex and expensive business intelligence tools on the market today, if you know spreadsheets, then you know 95% of what you need to use Factivate–we’ve got the rest covered.