Email notifications with body/subject containing c

Question

I would like to create a Google Sheets with event triggers. I'm using Google Apps Script.

When a cell has value Ok, a mail would be sent to warn a colleague (validation).

I found a script and it works.

Now I would like the mail body and mail subject to include cell values of the row where the trigger cell has the value OK.

Here is an example of the sheet:

When I write "Ok"in K8, a mail is sent to a colleague. And I would like the body mail to contain information in F8, G8, and J8. Likewise, if I write "Ok" in K7, I would like the sent mail to contain info from F7, G7, and J7. (Example: "Your request has been validated. Necessity is level 5. Quantity needed is 1. Price is 17,94€.")

The script I'm using:

/**
* add trigger for onedit - 
* see menu -> Resouces -> Current project's triggers
*/
function Initialize() {

var triggers = ScriptApp.getProjectTriggers();

for(var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}

ScriptApp.newTrigger("sendNotification")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onEdit()
.create();

};


/**
* 
*/


function sendNotification(e) {


if("K" == e.range.getA1Notation().charAt(0)) {

if(e.value == "ok") {



//Define Notification Details
  var recipients = "[e-mail]";
  var subject = "Validation" ;
  var body = "Achat valider, à procéder";

//Send the Email
  MailApp.sendEmail(recipients, subject, body);
}
}
}

How do I get Var subject and Var Body to contain cell values?

Answer 1

Problem solved.

To summurize, when you want Email notification when cell value is changed and you want the mail to contain cells value from the same row you've edited :

/**
* add trigger for onedit - 
* see menu -> Resouces -> Current project's triggers
*/
function Initialize() {

var triggers = ScriptApp.getProjectTriggers();

for(var i in triggers) {
ScriptApp.deleteTrigger(triggers[i]);
}

ScriptApp.newTrigger("sendNotification")
.forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
.onEdit()
.create();

};


/**
* 
*/



function sendNotification(e) {


if("L" == e.range.getA1Notation().charAt(0)) {

if(e.value == "oui") {



//Define Notification Details
var recipients = "[e-mail]";
var subject = "Request" + " - N° " + e.source.getRange('A'+ e.range.getRow()).getValue() ;
var body = "lolcat : " + e.source.getRange('B'+ e.range.getRow()).getValue()  ;

//Send the Email
MailApp.sendEmail(recipients, subject, body);
}
}
}

Working fine for me. Thank you for the help :)

Answer 2

Short answer

Use one of the Class Spreadsheet methods to get the desired ranges and then the getValue() method of the Class Range.

Brief example

The following code will show a "toast" (pop-up window) displaying the value of the cell in the F column and in the same row of the cell edited.

function onEdit(e){
  var s = e.source;
  var row = e.range.getRow();
  var fCell = s.getRange('F'+row);
  var fValue = fCell.getValue();
  Spreadsheet.getActive().toast(fValue);
}

References

• Class Spreadsheet
• Class Range