Google Apps Script - Send email based on cell word

timbo343 · March 26, 2023

I'm hoping someone can help with a Google Apps Script.

The following code works with a Sheet name called LEAVER however i'd like to expand it so it also looks at sheet called Starter.

Basically if a cell on a specific column contains a specific word it sends an email however i'd like it to look at 2 sheets. One called Leaver and one called Starter.

The sheet Leaver would have the word Leaver in a specific column and if like the sheet Starter to have the same referenced column to have the word Starter and email from there.

I've tried the IF and ELSE IF functions but ELSE IF doesn't work and can't think where i'm going wrong.

function sendMailEdit(e){

if (e.range.columnStart != 9 || e.value != "Leaver") return;
const rData = e.source.getSheetByName('Leaver').getRange(e.range.rowStart,1,1,7).getValues();

let surname = rData[0][0]; 
let email="Email1" + "," + "Email2";
let forename = rData[0][1]; 
let dateofleaving = rData[0][6]; 

//Email to send out
let msghtml = "NOTIFICATION: Student " + forename + " " + surname + " had left on " + dateofleaving + ". "+ "
" +" See this document to perform your update [link to spreadsheet]/"

let msgplain = msghtml.replace(/\
/gi, '\n').replace(/(<([^>]+)>)/ig, ""); //clear html tags and convert br to new lines for plain mail
MailApp.sendEmail(email, "Student Leaver", msgplain, {htmlBody: msghtml,name: "DisplayName" });

}

timbo343 · March 27, 2023

I was hoping this would work but it seems it doesn't:

function sendMailEdit(e){

if (e.range.columnStart != 9 || e.value != "Starter") return
let rData = e.source.getSheetByName('Starter').getRange(e.range.rowStart,1,1,7).getValues();

let surname = rData[0][0]; 
let email="Email";
let forename = rData[0][1]; 
let dateofleaving = rData[0][6]; 

let msghtml = "NOTIFICATION: Student " + forename + " " + surname + " is due to start at the school " + dateofleaving + ". "+ "
" +" See this document to perform your update [linktodocument] "

let msgplain = msghtml.replace(/\
/gi, '\n').replace(/(<([^>]+)>)/ig, ""); //clear html tags and convert br to new lines for plain mail
MailApp.sendEmail(email, "Student Starter", msgplain, {htmlBody: msghtml,name: "displayname" });
}

function sendMailEdit(e){

if (e.range.columnStart != 9 || e.value != "Leaver") return
let rData = e.source.getSheetByName('Leaver').getRange(e.range.rowStart,1,1,7).getValues();

let surname = rData[0][0]; 
let email="email";
let forename = rData[0][1]; 
let dateofleaving = rData[0][6]; 

let msghtml = "NOTIFICATION: Student " + forename + " " + surname + " has left the school " + dateofleaving + ". "+ "
" +" See this document to perform your update [Linktodocument]"

let msgplain = msghtml.replace(/\
/gi, '\n').replace(/(<([^>]+)>)/ig, ""); //clear html tags and convert br to new lines for plain mail
MailApp.sendEmail(email, "Student leaver", msgplain, {htmlBody: msghtml,name: "displayname" });
}

Edited March 27, 2023 by timbo343

garbage46 · March 27, 2023

Try this I created to loop through multiple sheets(tabs) with a Google Sheet.

It could be that the element you're missing is using multidimensional arrays to loop through the sheets/tabs in your spreadsheet.

Configure the variables with the mda array. Then run the heyHoLetsGo() function

/** 
* 
*  mda multidimension array can be used when multiple sheets to be to processed.
*  Each array format ['Sheet Name', 'Sent Column Number', 'Date Stamp Column number']
*  'Sheet Name' = The sheet name
*  'Sent Column Number' = the column number of the column used to record if action has been taken e.g. email sent Yes/No
*  'Date Stamp Column Number' = the column number of the column used to record date stamp the action was processed.
* 
*  ***********************
*  IMPORTANT: When counting in arrays, computers start counting at 0 not 1.
*  ***********************

*/

var mda = [
 ['Starters', '20', '21'],
 ['Leavers', '7', '8'],
 ['Sheet 3', '20', '21']
 ['Sheet 4', '20', '21']
 ['Sheet 5', '20', '21']

];
var l = mda.length;

function heyHoLetsGo() {

 for (var c = 0; c < l; c++) {
   var currentTab = mda[c][0];
   var firstCheckCol = mda[c][1];
   var secondCheckCol = mda[c][2];

   // test2(currentTab, firstCheckCol, secondCheckCol)
   checkForNewReport(currentTab, firstCheckCol, secondCheckCol)
 }
}

function test2(currentTab, firstCheckCol) {
 Logger.log("THIS IS THE TEST2 FUNCTION");
 Logger.log("Current Sheet: " + currentTab);
 Logger.log("data[i][" + firstCheckCol + "]");
}

function checkForNewReport(currentTab, firstCheckCol, secondCheckCol) {

 var sheetyMcSheet = currentTab;
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //Get the active Spreadsheet.
 var sheet = spreadsheet.getSheetByName(sheetyMcSheet); //Find the sheet we want.
 sheet.activate() //activate that sheet.
 var startRow = 3; // First row of data to process
 var numRows = sheet.getLastRow(); // Number of rows to process
 var dataRange = sheet.getRange(startRow, 1, numRows - 2, 56);
 var data = dataRange.getValues();

 // Loop through all rows
 for (var i = 0; i < data.length; ++i) {
   var firstCheckCol = parseInt(firstCheckCol); // converting the col number into an integer
   var secondCheckCol = parseInt(secondCheckCol); // converting the col number into an integer

   if (data[i][0] != '') { // check timestamp column
     var emailAlreadySent = data[i][firstCheckCol];
     Logger.log(getSheetName());
     if (emailAlreadySent != 'Yes') {

       // send email notification to all staff listed in the function
       sendEmailNotification(currentTab, data[i]);

       // Set "Date Sent" cell in column to current date
       // Set "Email Sent" cell in column to "Yes"
       sheet.getRange(i + startRow, secondCheckCol + 1).setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy HH:mm")); // +2 to account for computer starts at zero and header row
       sheet.getRange(i + startRow, firstCheckCol + 1).setValue("Yes"); // +2 to account for computer starts at zero and header row
     }
   }
   SpreadsheetApp.flush();
 } // End of Loop
 sheet.getRange('AH3').activate();

}

function sendEmailNotification(currentTab, data) {
 var surname = data[7];
 var forename = data[8];
 var role = data[11];
 var subject = 'New Activity';
 var message = 'Hello, \n\nA new thing has happened on the ' + currentTab + ' tab regarding ' + forename + ' ' + surname +'. \n\nPlease see the new thing on the linked Google Sheet for more details: https://docs.google.com/spreadsheets/d/.......';

 MailApp.sendEmail('[email protected]', subject, message);
 MailApp.sendEmail('[email protected]', subject, message);
 MailApp.sendEmail('[email protected]', subject, message);

 console.log(message);
}

/**
* Returns the name of the active sheet.
*/
function getSheetName() {
 let activeSheet = SpreadsheetApp.getActiveSheet();
 return activeSheet.getName();
}

Edited March 27, 2023 by garbage46

timbo343 · March 27, 2023

Try this I created to loop through multiple sheets(tabs) with a Google Sheet.

It could be that the element you're missing is using multidimensional arrays to loop through the sheets/tabs in your spreadsheet.

Configure the variables with the mda array. Then run the heyHoLetsGo() function

/** 
* 
*  mda multidimension array can be used when multiple sheets to be to processed.
*  Each array format ['Sheet Name', 'Sent Column Number', 'Date Stamp Column number']
*  'Sheet Name' = The sheet name
*  'Sent Column Number' = the column number of the column used to record if action has been taken e.g. email sent Yes/No
*  'Date Stamp Column Number' = the column number of the column used to record date stamp the action was processed.
* 
*  ***********************
*  IMPORTANT: When counting in arrays, computers start counting at 0 not 1.
*  ***********************

*/

var mda = [
 ['Starters', '20', '21'],
 ['Leavers', '7', '8'],
 ['Sheet 3', '20', '21']
 ['Sheet 4', '20', '21']
 ['Sheet 5', '20', '21']

];
var l = mda.length;

function heyHoLetsGo() {

 for (var c = 0; c < l; c++) {
   var currentTab = mda[c][0];
   var firstCheckCol = mda[c][1];
   var secondCheckCol = mda[c][2];

   // test2(currentTab, firstCheckCol, secondCheckCol)
   checkForNewReport(currentTab, firstCheckCol, secondCheckCol)
 }
}

function test2(currentTab, firstCheckCol) {
 Logger.log("THIS IS THE TEST2 FUNCTION");
 Logger.log("Current Sheet: " + currentTab);
 Logger.log("data[i][" + firstCheckCol + "]");
}

function checkForNewReport(currentTab, firstCheckCol, secondCheckCol) {

 var sheetyMcSheet = currentTab;
 var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //Get the active Spreadsheet.
 var sheet = spreadsheet.getSheetByName(sheetyMcSheet); //Find the sheet we want.
 sheet.activate() //activate that sheet.
 var startRow = 3; // First row of data to process
 var numRows = sheet.getLastRow(); // Number of rows to process
 var dataRange = sheet.getRange(startRow, 1, numRows - 2, 56);
 var data = dataRange.getValues();

 // Loop through all rows
 for (var i = 0; i < data.length; ++i) {
   var firstCheckCol = parseInt(firstCheckCol); // converting the col number into an integer
   var secondCheckCol = parseInt(secondCheckCol); // converting the col number into an integer

   if (data[i][0] != '') { // check timestamp column
     var emailAlreadySent = data[i][firstCheckCol];
     Logger.log(getSheetName());
     if (emailAlreadySent != 'Yes') {

       // send email notification to all staff listed in the function
       sendEmailNotification(currentTab, data[i]);

       // Set "Date Sent" cell in column to current date
       // Set "Email Sent" cell in column to "Yes"
       sheet.getRange(i + startRow, secondCheckCol + 1).setValue(Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy HH:mm")); // +2 to account for computer starts at zero and header row
       sheet.getRange(i + startRow, firstCheckCol + 1).setValue("Yes"); // +2 to account for computer starts at zero and header row
     }
   }
   SpreadsheetApp.flush();
 } // End of Loop
 sheet.getRange('AH3').activate();

}

function sendEmailNotification(currentTab, data) {
 var surname = data[7];
 var forename = data[8];
 var role = data[11];
 var subject = 'New Activity';
 var message = 'Hello, \n\nA new thing has happened on the ' + currentTab + ' tab regarding ' + forename + ' ' + surname +'. \n\nPlease see the new thing on the linked Google Sheet for more details: https://docs.google.com/spreadsheets/d/.......';

 MailApp.sendEmail('[email protected]', subject, message);
 MailApp.sendEmail('[email protected]', subject, message);
 MailApp.sendEmail('[email protected]', subject, message);

 console.log(message);
}

/**
* Returns the name of the active sheet.
*/
function getSheetName() {
 let activeSheet = SpreadsheetApp.getActiveSheet();
 return activeSheet.getName();
}

WOW! That is some code and honestly i'm not sure what i'm supposed to be editing for it to pick up what i need it to pick up.

timbo343 · March 27, 2023

Why can't this script with a few edits just work?

function sendMailEdit(e){

//Starters
if (e.range.columnStart != 9 || e.value != "Starter") return
var rowData = e.source.getSheetByName('Starter').getRange(e.range.rowStart,1,1,7).getValues();

let surname = rowData[0][0]; 
let email="email";
let forename = rowData[0][1]; 
let dateofleaving = rowData[0][6]; 

let msghtml = "NOTIFICATION: Student " + forename + " " + surname + " is due to start at the school " + dateofleaving + ". "+ "
" +" See this document to perform your update [linktodocument] "

let msgplain = msghtml.replace(/\
/gi, '\n').replace(/(<([^>]+)>)/ig, ""); //clear html tags and convert br to new lines for plain mail
MailApp.sendEmail(email, "Student Starter", msgplain, {htmlBody: msghtml,name: "Starter" });

//Leavers
if (e.range.columnStart != 9 || e.value != "Leaver") return
var rData = e.source.getSheetByName('Leaver').getRange(e.range.rowStart,1,1,7).getValues();

let leaversurname = rData[0][0]; 
let leaveremail="email";
let leaverforename = rData[0][1]; 
let leaverdateofleaving = rData[0][6]; 

let msghtmlleaver = "NOTIFICATION: Student " + leaverforename + " " + leaversurname + " is due to start at the school " + leaverdateofleaving + ". "+ "
" +" See this document to perform your update [linktodocument] "

let msgplainleaver = msghtmlleaver.replace(/\
/gi, '\n').replace(/(<([^>]+)>)/ig, ""); //clear html tags and convert br to new lines for plain mail
MailApp.sendEmail(leaveremail, "Student Leaver", msgplainleaver, {htmlBody: msghtmlleaver,name: "Leaver" });

}

If i take out one of the sections such as //Starters the //Leavers works as it should. If i take out the //Leavers the //Starters works.

timbo343 · March 27, 2023

I'm hoping it works something like this

but across multiple sheets.

IrritableTech · March 27, 2023

I don't like the look of your if statements.

I'd usually be using

if (var1 == 1 || var2 != 1) {

do a thing

}

return will stop the processing.

IrritableTech · March 27, 2023

I'm not at my pc so this could be wrong but I'd get two sheets from the same spreadsheet like this. Although I'm no expert.

function myFunction() {

const ss1 = SpreadsheetApp.getActive().getSheetByName('sheet1');

const ss2 = SpreadsheetApp.getActive().getSheetByName('sheet2');

const data1 = ss1.getActiveRange().getValues();

const data2 = ss2.getActiveRange().getValues();

var1 = data1[0][0];

var2 = data2[0][0];

}

garbage46 · March 27, 2023

OK, that video helps clarify what you are trying to achieve. You want to copy the video and have code which does the following:

Trigger the script each and every time there is an edit anywhere in the entire Google Sheet.
Check the column of the edit is Column I (9) AND the edited cell value is equal to "Starter" OR "Leaver".
Send an email to alert selected recipients of a new starter/leaver is the above criteria is met.

This script doesn't care what the name of the sheet (tab) is or how many sheets there are. The sheet name is included as part of the event (e).

The code snippet below returns edited sheet name to the variable currentTab as it is simply the active sheet at the time of the edit.

    var activeSheet = SpreadsheetApp.getActiveSheet();
   var currentTab = activeSheet.getName();

This script relies on checking column I which is referenced in the script as either:

e.range.columnStart == 9 (9 here because rows/cols start at 1) or
data[8] (8 here because arrays start at 0)

At this stage, to keep the IF check simple, the Starter/Leaver column on all sheets must be the same column for this script.

function heyHoLetsGo(e) {
 // Check if edit was made in column I and contains keyword Starter or Leaver
 if (e.range.columnStart == 9 && e.value == 'Starter' || e.value == 'Leaver') {
   var data = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 15).getValues();
   var activeSheet = SpreadsheetApp.getActiveSheet();
   var currentTab = activeSheet.getName();
   Logger.log("Sending email") // for debug
   sendEmailNotification(currentTab, data[0]);
 } else {
   Logger.log(e.value + " is not a valid trigger") // for debug
 }
}

function sendEmailNotification(currentTab, data) {
 var surname = data[0];
 var forename = data[1];
 var startDate = Utilities.formatDate(data[4], "GMT+1", "dd/MM/yyyy");
 var subject = '';
 var message = '';

 if (data[8] == 'Starter') {
   var subject = 'New Student Starter';
   var message = 'Hello, \n\nStudent ' + forename + ' ' + surname + ' is due to start at the school on ' + startDate + '. \n\nPlease see this document to perform your update: https://docs.google.com/spreadsheets/d/.......';
 }

 if (data[8] == 'Leaver') {
   var subject = 'New Student Leaver';
   var message = 'Hello, \n\nStudent ' + forename + ' ' + surname + ' is due to leave school on ' + startDate + '. \n\nPlease see this document to perform your update: https://docs.google.com/spreadsheets/d/.......';
 }

 // MailApp.sendEmail('[email protected]', subject, message);
 // MailApp.sendEmail('[email protected]', subject, message);
 // MailApp.sendEmail('[email protected]', subject, message);

 /** Below print email to the executions logs for debug */
 Logger.log("Subject:" + subject);
 Logger.log("Body:" + message);
}

You will need to uncomment and add the email addresses for email to actually send once testing is completed.

NOTE: This script will run every time there is a change and could fail when changing cells in quick succession if the script hasn't finished running from a previous trigger.

Edited March 27, 2023 by garbage46

garbage46 · March 27, 2023

I guess a crucial step is also creating the "on edit" trigger to run the heyHoLetsGo function

garbage46 · March 27, 2023

I don't like the look of your if statements.

I'd usually be using

if (var1 == 1 || var2 != 1) {
do a thing
}

return will stop the processing.

@timbo343 this is why your existing code only works for one or the other sections of Starters or Leaver. The IF statement ending with return.

timbo343 · March 27, 2023

THANK YOOOOU!!! To both of you!

This could open the door to all sorts at our place!

timbo343 · March 27, 2023

@garbage46 and @IrritableTech..

I know i've said thank you already but this is working a treat!!

After a few little modifications it's working as i wanted it to!

jthompson · March 27, 2023

Google Apps Script is ace. It's basically our secret sauce here.

timbo343 · March 27, 2023

Google Apps Script is ace. It's basically our secret sauce here.

It is when the one trying to get it to work knows how to code #imnotacoder

Here is my finished code in testing with a few tweaks if anyone wants to use it.

function heyHoLetsGo(e) {
 // Check if edit was made in 9 (column I) or 15 (column O) and contains keyword Starter or Leaver
 if (e.range.columnStart == 9 && e.value == 'Leaver' || e.range.columnStart == 15 && e.value == 'Starter') {
   var data = e.source.getActiveSheet().getRange(e.range.rowStart, 1, 1, 15).getValues();
   var activeSheet = SpreadsheetApp.getActiveSheet();
   var currentTab = activeSheet.getName();
   Logger.log("Sending email") // for debug
   sendEmailNotification(currentTab, data[0]);
 } else {
   Logger.log(e.value + " is not a valid trigger") // for debug
 }
}

function sendEmailNotification(currentTab, data) {
 var surname = data[0]; //Column A
 var forename = data[1]; //Column B
 var yearout = data[2] //Column C
 var yearin = data[3] //Column D
 var date = data[5] //Column F
 //var startDate = Utilities.formatDate(data[4], "GMT+1", "dd/MM/yyyy");
 var subject = '';
 var message = '';

 //Starters - Data[14] is Column O
 if (data[14] == 'Starter') {
   var email = "EMAIL"
   var displayname = "Student Udpate - Starter"
   var subject = 'Student Starter';
   var messagehtml = "NOTIFICATION: Student " + forename + " " + surname + " will go into " + yearin + " is due to start at the school on " + date + ". "+ "
" +" "
 }

 //Leavers - Data[8] is Column I
 if (data[8] == 'Leaver') {
   var email = "EMAIL"
   var displayname = "Student Update - Leaver"
   var subject = 'Student Leaver';
   var messagehtml = "NOTIFICATION: Student " + forename + " " + surname + " in " + yearout + " has left the school on " + date + ". "+ "
" +" See this document and the  Leavers  tab to confirm your update [LinkToDocument]"
 }
 
 //Strip out the html tags and convert br to new lines for plain mail
 var message = messagehtml.replace(/\
/gi, '\n').replace(/(<([^>]+)>)/ig, "");

 //Send Email
 MailApp.sendEmail(email, subject, message, {htmlBody: messagehtml,name: displayname });

 /** Below print email to the executions logs for debug */
 Logger.log("Subject:" + subject);
 Logger.log("Body:" + message);
}

Edited March 27, 2023 by timbo343

IrritableTech · March 27, 2023

Glad to hear you've got it working.

We've been creating a few scrips here over the last couple of months integrating systems, automating notifications, creating/editing/disabling accounts. It's become very useful very quickly to us.

I wish I'd taken the time to use it well before now.

Perhaps it's worthy of a dedicated thread - ideas for automations? With so many jobs and so little time, it's worth automating all those repetitive actions.

timbo343 · March 27, 2023

Glad to hear you've got it working.

We've been creating a few scrips here over the last couple of months integrating systems, automating notifications, creating/editing/disabling accounts. It's become very useful very quickly to us.

I wish I'd taken the time to use it well before now.

Perhaps it's worthy of a dedicated thread - ideas for automations? With so many jobs and so little time, it's worth automating all those repetitive actions.

Sounds a good idea! Obviously would need stating if it was a Google code or MS (VBA) code.

We are trying to get more out of Forms, especially the form approvals addon along with Form Ranger and form limiter. The voting form that is on here works well for us. It's had to have a few modifications over the last couple of months but it works well.

I always say, if you can think it, there has to be a way to achieve it, it's just knowing what to put in to get what you want out.

jthompson · March 27, 2023

I've been working on a few little projects that combine Apps Script with AppSheet lately. AppSheet allows you to build custom database apps (without having to write any code) on top of Google Sheets, with API functionality baked in and plenty of automation options (e.g. send an email if a new row is added and it contains a particular value).

Google Apps Script - Send email based on cell word

Recommended Posts

timbo343

timbo343

garbage46

timbo343

timbo343

timbo343

IrritableTech

IrritableTech

garbage46

garbage46

garbage46

timbo343

timbo343

jthompson

timbo343

IrritableTech

timbo343

jthompson

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Confirm acknowledgement of parental consent requirement for the Additional Google services

Lenovo Tablet Firmware

Outloook 365 - Classic

Home

Forums

What's New