Google Sheets – Send Email When a Particular Column Changes

google sheetsgoogle-apps-script

I want an email to be sent when a cell is changed from "No" to "Yes" in column C from a particular sheet – sheet1.

The email body would need to include information from cells in columns D,E,F,G,H,I,J from the same row as the cell that changed to Yes, in addition to of course the email subject and email recipient.

I'd like this script to be modified to do that or if there is another script that could do the above.

function sendNotification(e){
if(e.range.getColumn()==3 && e.value=='YES'){
 var recipients = "***********@gmail.com";
 var subject = "Update"+e.range.getSheet().getName();
 var body = "This cell has changed";
 var valColB=e.range.getSheet().getRange(e.range.getRow(),2).getValue();
 MailApp.sendEmail(recipients, subject, body)
 }
}

Hello XYZ,

Please find order details below.

{Cell D} (Its in this format xxxxxx-ABC0001, I need the second part after "-" from this cell here)

Size – Width: {Cell E} x Height: {Cell F}

Quantity – {Cell H}

  • It needs to be tube {Cell I}.
  • Shipment to {Cell J}.
  • Order image is {Cell G}

The order is for a customer from {Cell J}

Thanking you,

ABC

The subject needs to have

{Cell J} New Order No. {Cell D}

The recipient is always the same

Any help would be highly appreciated. Thanks.

Update – Solution – Thanks to Ron

 function sendNotification(e){
  var s = SpreadsheetApp.getActiveSpreadsheet();
  var ss = s.getSheetByName("Order Details")
if(e.range.getColumn()==3 && e.value=='Yes'){
  var cell = ss.getActiveCell();
  var row = cell.getRow();
  //var row = ss.getRange().getRow();
 //var sku = ss.getRange("D2").getValue();
  var sku = ss.getRange(row,4).getValue();  //Column D
  var array1 = [{}]; array1 = sku.toString().split("-")
 //var sku = "D2"; var array1 = [{}]; array1 = name.toString().split("-") 
 var sizewidth = ss.getRange(row,5).getValue();  //Column E
 var sizeheight = ss.getRange(row,6).getValue();  //Column F
 var qty = ss.getRange(row,8).getValue();    //Column H
 var country = ss.getRange(row,10).getValue();  //Column J
 var tube = ss.getRange(row,9).getValue();   //Column I
 var paintingimage = ss.getRange(row,7).getValue();  //Column G
 var orderlink = 'HYPERLINK("http://testly/Km45S", "Order Details")';


 MailApp.sendEmail({
  to: "xxxx@gmail.com",
  subject:  country + " New Order No. " + array1[1], // note the spaces between the quotes...
 htmlBody: "Hello XYZ,  <br>"+
   "Please find order details below. <br><br>"+
    sku + "<br><br>" +
   "Size - Width: " + sizewidth + " x " + "Height: " + sizeheight + "<br><br>"+
   "Quantity - " + qty + "<br><br>"+
   "- It needs to be tube rolled"+
   "- Shipment to " + country + "<br>" +
   "- Order image is " + paintingimage + "<br>" +
   "The order is for a customer from " + country + "<br><br>" +
   "Thanking you, <br>" +
   "Abc",                
                   })

 }
}

Best Answer

Ok. Now that we have the info needed, we can construct an email...

You have:

function sendNotification(e){
if(e.range.getColumn()==3 && e.value=='YES'){
 var recipients = "***********@gmail.com";
 var subject = "Update"+e.range.getSheet().getName();
 var body = "This cell has changed";
 var valColB=e.range.getSheet().getRange(e.range.getRow(),2).getValue();
 MailApp.sendEmail(recipients, subject, body)
 }
}

You want the email sent when 'Yes' is in Column C, so Line 2 is good.

You want the Subject and body to contain data from cells D - J in a row. This means you will need to collect the data in an array, or you could create a variable for each item, i.e.: 

var customer = ss.getRange("J2").getValue();
var tube = ss.getRange("I2").getValue();
var qty = ss.getRange("H2").getValue();

For the variable for cell D, where you want just the portion after the dash, look into split.

You can also use hyperlinks for variables, to allow for clickable links in the email.

Once you have your data together, you can work on the email.

The recipient never changes, so Line 3 is ok. But to keep things easy, I would put it below all of the data variables, and keep it with the rest of the email variables.

Lines 4 and 5 (Subject and Body) are what needs the most work.

Here is how I would format the email portion:

MailApp.sendEmail({
  to: "********@gmail.com",
  subject:  customer + " New Order No. " + {var for cell D}, // note the spaces between the quotes...
 htmlBody: "Hello XYZ,  <br>"+
   "Please find order details below. <br><br>"+
   "{Cell D} <br><br>"+
   "Size - Width: {Cell E} x Height: {Cell F} <br><br>"+
   "Quantity - {Cell H} <br><br>"+

etc...

Check out the enum glyphtype for bullets.

Line 6 is useless. Delete.

I should have provided enough to get you there. Let me know if you have any more questions.

Related Topic