Skip to content

Instantly share code, notes, and snippets.

@palumbo
Created May 28, 2018 22:25
Show Gist options
  • Save palumbo/5364a6726f39d09419eb5d68d3ccf865 to your computer and use it in GitHub Desktop.
Save palumbo/5364a6726f39d09419eb5d68d3ccf865 to your computer and use it in GitHub Desktop.
Code that emails notifications from my Customer Success Tracker made in Google Sheets
function onEdit(e) {
// setting common variables
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var user = Session.getActiveUser();
var cell = sheet.getActiveCell().getA1Notation();
var val = sheet.getActiveCell().getValue();
var range = sheet.getRange("A1:Z182"); // creates range needed to use getCell() later
var url = ss.getUrl();
// initializing event variables
var col = e.range.getColumn();
var row = e.range.getRow();
var cell = e.range.getA1Notation();
var cust = range.getCell(row,1).getValue(); // pulls cust name by pulling row from event and using column 1
var csm = range.getCell(row,6).getValue(); // added this 2017.09.25
var pot = range.getCell(row, 2).getValue(); // added 2017.09.25 | get's company name from potential 4-star
// health status change
if (col == 9 && sheet.getName() == "Master List") {
// Browser.msgBox(user + ' changed health to ' + val); <- used to test functionality
MailApp.sendEmail('[email protected]', 'Health Change', user + ' has changed health for ' + cust + ' to ' + val + " :: " + url + "#gid=0&range=" + cell);
}
// health toast
if (col == 9 && sheet.getName() == "Master List" && e.value == "GOOD") {
ss.toast('good customer health?! \n\n this pleases palumbo');
}
if (col == 8 && sheet.getName() == "Master List") {
// Browser.msgBox(user + ' updated the engagement date for ' + cust + ' to ' + val);
MailApp.sendEmail('[email protected]', 'VPS Engagement', user + ' updated the engagement date for ' + cust + ' to ' + val + " :: " + url + "#gid=0&range=" + cell);
}
if (col == 8 && sheet.getName() == "Possible 4-star" && val == "NEEDS VALIDATION") {
// Browser.msgBox(pot + ' has been identifed as a potential 4-star and assigend to ' + csm);
MailApp.sendEmail('[email protected]', csm + ' has been assigned a potential 4-star ', csm + ", you've been assigned " + pot + " as a potential 4-star. Please investigate." + url + "#gid=542068316&range=" + cell);
}
if (col == 8 && sheet.getName() == "Possible 4-star" && val != "NEEDS VALIDATION") {
// Browser.msgBox(csm + ' marked ' + pot + ' as ' + val);
MailApp.sendEmail('[email protected]', 'Possible 4-star update', csm + ' marked ' + pot + ' as ' + val + ". " + url + "#gid=542068316&range=" + cell)
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment