Email notifications with body/subject containing cell value

2
votes

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: 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 = "[email protected]";
  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?

2
emaileventsgoogle-apps-scriptgoogle-sheets

2 Answers

1
votes

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

0
votes

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 = "[email protected]";
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 :)