Google Sheets - sending emails on trigger

[fiza]

I've been asked to find out if there are any Google sheet extensions or Apps Script methods to trigger an email to go out based on the value of a cell in Google Sheets. The email recipient would be based on the value and the student year group the value relates to ie Y7 student hits a target or falls below a target and then email gets sent to Year Lead.

 

I have seen some examples of Apps Scripts but I think some of these are dated as they have not worked for me so far.

[jthompson]

When Apps Script sends an email, it does so as the user for whom the trigger was set (or as the user running the function directly in the editor). The first time it does that it needs to prompt for approval.

 

Are you using a time-based trigger?

[fiza]

When Apps Script sends an email, it does so as the user for whom the trigger was set (or as the user running the function directly in the editor). The first time it does that it needs to prompt for approval.

 

Are you using a time-based trigger?

 

Not hot on scripting but It's a value based trigger.

I cobbled together the below script from what I found on the net but I am sure it's all wrong!

 

[color=#185ABC][font=&amp]function[/font][/color][color=#202124][font=&amp]checkValue[/font][/color][color=#3C4043][font=&amp]()[/font][/color][color=#3C4043][font=&amp]{
[color=#185abc]var[/color] [color=#202124]ss[/color] = [color=#c92786]SpreadsheetApp[/color].[color=#202124]getActive[/color]();
[color=#185abc]var[/color] [color=#202124]sheet[/color] = [color=#202124]ss[/color].[color=#202124]getSheetByName[/color]([color=#b31412]'valuetest'[/color]);
 [color=#185abc]var[/color] [color=#202124]url[/color] = [color=#202124]ss[/color].[color=#202124]getUrl[/color]();
[color=#185abc]var[/color] [color=#202124]valueToCheck[/color] = [color=#202124]sheet[/color].[color=#202124]getRange[/color]([color=#b31412]'D4'[/color]).[color=#202124]getValue[/color]() ;
 [color=#185abc]if[/color]([color=#202124]valueToCheck[/color] == [color=#098591]5[/color])
 {
   [color=#c92786]MailApp[/color].[color=#202124]sendEmail[/color]([color=#b31412]"[email protected]"[/color], [color=#b31412]"subject"[/color], [color=#b31412]"context"[/color] + [color=#202124]valueToCheck[/color]+ [color=#b31412]"."[/color]);
 }
}

[/font][/color]

[jthompson]

That works for me when running directly in the editor.

 

Try this (obvisouly with a functioning email recipient to test with).

function checkValue(){
 var ss = SpreadsheetApp.getActive();
 var sheet = ss.getSheetByName('valuetest');
 var valueToCheck = sheet.getRange('D4').getValue() ;
 Logger.log(valueToCheck);
 if(valueToCheck == 5)
 {
   MailApp.sendEmail("[email protected]", "subject", "context" + valueToCheck+ ".");
   Logger.log("Email should be sent.");
 }
 else {
   Logger.log("Email will not be sent.");
 }
}

The Logger output should indicate whether the value you're looking to test is actually being read in, and which way the if statement is then going.

[fiza]

That works for me when running directly in the editor.

 

Try this (obvisouly with a functioning email recipient to test with).

function checkValue(){
 var ss = SpreadsheetApp.getActive();
 var sheet = ss.getSheetByName('valuetest');
 var valueToCheck = sheet.getRange('D4').getValue() ;
 Logger.log(valueToCheck);
 if(valueToCheck == 5)
 {
   MailApp.sendEmail("[email protected]", "subject", "context" + valueToCheck+ ".");
   Logger.log("Email should be sent.");
 }
 else {
   Logger.log("Email will not be sent.");
 }
}

The Logger output should indicate whether the value you're looking to test is actually being read in, and which way the if statement is then going.

 

I am getting an error

TypeError: Cannot read properties of null (reading 'getRange')

checkVale @ Code.gs:4

 

I have checked the sheet and there is a numerical value in D4

[timbo343]

@fiza

 

Ive got a working script that sends an email based on cell value if that might work for you.

[fiza]

@fiza

 

Ive got a working script that sends an email based on cell value if that might work for you.

 

That would be perfect!

[timbo343]

That would be perfect!

I'll send it over by mid afternoon.

[jthompson]

This also works. Add an "On Edit" trigger. The function will check the event to determine which cell in which sheet was edited, and fire an email only when cell D4 in the sheet 'valuetest' is edited to a value of 5.

 

function checkValue(event){
 if ((event.source.getSheetName() == 'valuetest') && (event.range.getColumn() == 4) && (event.range.getRow() == 4)) {
   // Cell D4 in sheet 'valuetest' was edited.
   Logger.log("Target cell was edited");
   var valueToCheck = event.range.getValue();
   if(valueToCheck == 5) {
     Logger.log("Email should be sent.");
     MailApp.sendEmail("[email protected]", "Test", "This is a test: " + valueToCheck + ".");
   } else {
     Logger.log("Email will not be sent.");
   }
 } else {
   Logger.log("Target cell was NOT edited");
 }
}

[timbo343]

This also works. Add an "On Edit" trigger. The function will check the event to determine which cell in which sheet was edited, and fire an email only when cell D4 in the sheet 'valuetest' is edited to a value of 5.

 

function checkValue(event){
 if ((event.source.getSheetName() == 'valuetest') && (event.range.getColumn() == 4) && (event.range.getRow() == 4)) {
   // Cell D4 in sheet 'valuetest' was edited.
   Logger.log("Target cell was edited");
   var valueToCheck = event.range.getValue();
   if(valueToCheck == 5) {
     Logger.log("Email should be sent.");
     MailApp.sendEmail("[email protected]", "Test", "This is a test: " + valueToCheck + ".");
   } else {
     Logger.log("Email will not be sent.");
   }
 } else {
   Logger.log("Target cell was NOT edited");
 }
}

 

This looks a bit better than my script as it looks up the SpreadSheet Tab.

[jthompson]

If it's a busy Sheets file with lots of editing going on, it might run into some Apps Script quota issues since this trigger would be going off a lot.

[timbo343]

We use ours for Staff / Student starters / leavers who start / leave part way through the year so it doesn't get edited that often.

[jthompson]

We use ours for Staff / Student starters / leavers who start / leave part way through the year so it doesn't get edited that often.

 

Sounds like good material for an AppSheet app!

At least, that's what I had fun with over the summer.

 

You can then use AppSheet to add automations such as emial notifications for newly added records, etc.

[timbo343]

Sounds like good material for an AppSheet app!

At least, that's what I had fun with over the summer.

[ATTACH=CONFIG]69907[/ATTACH]

 

You can then use AppSheet to add automations such as emial notifications for newly added records, etc.

 

Oooh! I think might have to be a bit of a project once i've done the whole Google Forms - Form Approvals logics.

 

Thanks for sharing.

[howartp]

@timbo343 - I was about to tag you in this thread following our conversations last week!

 

I'm liking some of the samples/examples!

[Kenji]

This also works. Add an "On Edit" trigger. The function will check the event to determine which cell in which sheet was edited, and fire an email only when cell D4 in the sheet 'valuetest' is edited to a value of 5.

 

function checkValue(event){
 if ((event.source.getSheetName() == 'valuetest') && (event.range.getColumn() == 4) && (event.range.getRow() == 4)) {
   // Cell D4 in sheet 'valuetest' was edited.
   Logger.log("Target cell was edited");
   var valueToCheck = event.range.getValue();
   if(valueToCheck == 5) {
     Logger.log("Email should be sent.");
     MailApp.sendEmail("[email protected]", "Test", "This is a test: " + valueToCheck + ".");
   } else {
     Logger.log("Email will not be sent.");
   }
 } else {
   Logger.log("Target cell was NOT edited");
 }
}

 

Can this script be modified to also display the value from another column in the email body?

 

For example, if cell D4=5 and cell A4=Jimmy, the email would be: Jimmy,5

 

Please help

Edited February 27, 2024 by Kenji

[jthompson]

Can this script be modified to also display the value from another column in the email body?

 

For example, if cell D4=5 and cell A4=Jimmy, the email would be: Jimmy,5

 

Please help

 

See if this line sets the variable otherValue to "Jimmy". If it does (i.e. the Logger.log() line writes Jimmy to the console) then you'd be able to incorprate otherValue into the Mailapp.sendEmail() line.

 

var otherValue = SpreadsheetApp.getActiveSheet().getRange(event.range.getRow(), 1).getValue();
Logger.log(otherValue);