0

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",                
                   })

 }
} 
  • I don't think your question is 'Could this code be modified to do that.', because the obvious answer to that is YES. So what do you really want the code to do? What should be in the email? – Ron Kloberdanz Aug 26 '18 at 7:36
  • Have just added more information on what I want the code to do. – user2240778 Aug 26 '18 at 19:10
  • Better. Now, do you have specific formatting you would like for those cells? Do they need to be in a specific order? Do you just want them listed one after another as text, or in a table? Do you need linker text? Is the email recipient always the same, or will it be picked from the spreadsheet? – Ron Kloberdanz Aug 27 '18 at 17:29
  • Have made it more specific :-) – user2240778 Aug 27 '18 at 20:55
  • Can you change the sharing on the sheet? It's currently 'View Only'. – Ron Kloberdanz Aug 28 '18 at 17:17
1

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.

  • Thanks a ton Ron, I've updated what you've suggested but slightly stuck on the split and get by particular sheet. – user2240778 Aug 28 '18 at 10:04
  • For split, here's a hint: var name = "1.John Doe"; var array1 = [{}]; array1 = name.toString().split("."). You could also use substring: var n = name.substring(2, name.length), or slice: var n = name.slice(2, name.length);. – Ron Kloberdanz Aug 28 '18 at 14:12
  • For getting a particular sheet, there are lots of ways to do it. getActiveSpreadsheet, getActiveSheet, openByID, openByUrl, getSheetByName, getSheets. I'm probably forgetting some... – Ron Kloberdanz Aug 28 '18 at 14:18
  • Forgot to mention. The numbers (2) in the above methods are the number of characters to separate by. So change that to match you situation. – Ron Kloberdanz Aug 28 '18 at 14:21
  • Thanks Ron, I've updated the code, it still however doesn't work, I've shared a copy of the sheet if you'd like to take a look. – user2240778 Aug 28 '18 at 16:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.