CODEWITHSUNDEEP
google-apps-script
Kris Hermans
Kris Hermans

Reputation: 1

Send an email when a cell value in a row changes to the email address in the same row of the changed cell

I have a spreadsheet with an "email address", a "contract title" and a "YES/NO" cell in every row. Every time the YES/NO value changes in one of the cells, the entire row has to be emailed to the email address in that row. I can do this in Excel but it seems mission impossible to get this to work in Google Docs. Has anyone tried this before or knows how to script or configure this?

I've tried to play around with the OnEdit() and SendMail function combined with a trigger but it is all way to limited in Google Docs to get this working.

Upvotes: 0

Views: 238

Answers (2)

Kris Hermans
Kris Hermans

Reputation: 1

The script fails on this line nr 7: if(e.range.getColumn()==columnYesNo){

I get this error message: TypeError: Cannot read property 'range' of undefined (line 7, file "onEdit")

Upvotes: 0

ziganotschka
ziganotschka

Reputation: 26796

OnEdit() is the right approach

What you need is to

  1. write a script bound to your spreadsheet of interest
  2. check onEdit() either the edit took place in the column of interest (the one containing Yes/No)
  3. retrieve the row in which the edit took place
  4. send an email with the data in this row

Sample:

function onEdit(e) {
  var sheet=SpreadsheetApp.getActive().getSheetByName('Sheet1');
  if(e.source.getActiveSheet().getName()==sheet.getName()){
    var columnEmail=1;
    var columnContractTitle=2;
    var columnYesNo=3;
    if(e.range.getColumn()==columnYesNo){
      var row=e.range.getRow();
      var YesNo=e.range.getValue();
      var email=sheet.getRange(row, columnEmail).getValue();
      var ContractTitle=sheet.getRange(row, columnContractTitle).getValue();
      MailApp.sendEmail(email, YesNo, ContractTitle)
    }
  }
}

Keep in mind that you have to run the onEdit() function once manually - even if it throws you an error.

Useful references:

Upvotes: 1

Related Questions