'Trigger an Email when text changes in formatted cell?
I've been tasked with setting up a GoogleSheet that contains information about my team's software and the expiry date of their licenses. I have set up the Sheet so when it reaches an expiry date it changes a column to read "Expired".
What I'd like to happen is that when the cell text changes to "Expired" it send me an email to alert me. I found some tips online and tried a script but can't seem to get it to function as expected.
Here's what my Google Sheet looks like:
Here's the code i've been trying to adapt:
function sendMailEdit(e){
if (e.range.columnStart != 6 || e.value != "Expired") return;
const rData = e.source.getActiveSheet().getRange(e.range.rowStart,1,1,5).getValues();
let lictyp = rData[0][2];
let swpi = rData[0][1];
let name = rData[0][0];
let msg = "The " + lictyp + "license for " + swpi + "will expire in 10 days time for " + name + "." + "Contact IT to renew licence asap. Kind Regards, LICENSE NINJA!";
Logger.log(msg);
GmailApp.sendEmail("[email protected]", "License Expiry Alert", msg)
}
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|

