function notifyMasterUsers(e) {
let range = e.range;
let source = e.source;
let spreadsheetName = source.getName();
let a1Notation = range.getA1Notation();
let oldValue = e.oldValue;
let newValue = e.value;
let user = e.user;
if (user != '') {
user = user;
}
else {
user = 'Unkown User';
}
let subject = user + ' edited the cell ' + a1Notation + ' on the spreadsheet ' + spreadsheetName;
let body = 'Old Value: ' + oldValue + '\n' + 'New Value: ' + newValue;
let masterUserEmails = getMasterUserEmails();
if (masterUserEmails.length != 0) {
for (let i = 0; i < masterUserEmails.length; i++) {
let masterUserEmail = masterUserEmails[i];
try {
MailApp.sendEmail(masterUserEmail, subject, body);
}
catch(error) {
ui.alert(error);
}
}
}
else {
ui.alert('No master user emails listed to nofity changes. Please add master user emails first.');
}
}
This is the function that gets the master user emails list from one of the sheets:
/**
* Gets email array from Master Users sheet
*/
function getMasterUserEmails(sheetName = masterUsersSheetName) {
let sheet = spreadsheet.getSheetByName(sheetName);
if (sheet != null) {
let dataRange = sheet.getDataRange();
let values = dataRange.getValues();
let masterUserEmailArray = [];
for (let i = 0; i < values.length; i++) {
masterUserEmailArray.push(values[i][0]);
};
masterUserEmailArray.shift();
// console.log(masterUserEmailArray.length);
return masterUserEmailArray;
}
else {
ui.alert('Couldnt get the "Master Users" sheet. Please run the function "Add Master Users Sheet" located on the "Automated Functions" custom menu and try again.');
}
}