0

I'm looking for an script can automatic send emails when checkbox is True. This is my format example: enter image description here

What i want is when im clicking on checkbox = True at F2, i can recieve an email with excactly contents from A2 to E2, looks like this:

  • Email subject= [Approved] Form

  • Email message= contents below

  • Timestamp: 01/06/2020 + times
  • Machine/Equiment require: PU1602
  • Plant to require: RF
  • Team to fix: Ele
  • Form no: F01

The emails address can be set on the scripts too. So please help to this. I lost my hair to try someway, but its hard to me. This is the sample spreadsheet of my project:

my samle spreadsheet project

  • 1
    Welcome to Web Applications. Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Jun 2 at 2:26
  • Does this answer your question? Trying to send email based on checkbox selection from StackOverflow. – Tedinoz Jun 3 at 7:34
  • @Tedinoz i just go to u link, but what i want is about var message. I want the message send to my email is contents from Col A to Col E, same row with checked box – NKlaus Jun 4 at 8:39
0

The following (untested) script is designed to be installed as an installable onEdit script.

It will detect whether:
1 - a checkbox is marked as true, and
2 - the sheet is the form response sheet, and
3 - the checkbox is in Column F

If all of those conditions are met, then an email will be sent.


function wa14285401(e) {

  // this script is installed as an installable onEdit trigger
  /*
   if the checkbox is ticked, send this email
   Email subject= [Approved] Form
   Email message= contents below
   Timestamp: 01/06/2020 + times
   Machine/Equiment require: PU1602
   Plant to require: RF
   Team to fix: Ele
   Form no: F01
  */

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "May.2020";
  var sheet = ss.getSheetByName(sheetname);

  // the checkboxes are in column F
  var eCol=6;

  // get the key event obvjects
  var erange = e.range;
  var eRow = erange.rowStart;
  var eCol = erange.columnStart;
  var eSheet = erange.getSheet().getName();
  var eVal = e.value;
  // Logger.log("DEBUG: The edited sheet is "+eSheet);
  // Logger.log("DEBUG: The edited row is "+eRow);
  // Logger.log("DEBUG: The edited Column is "+eCol);
  // Logger.log("DEBUG: The edited_value = "+eVal)
  // Logger.log(JSON.stringify(e)); DEBUG

  // apply logic to detect if check box is ticked.
  if (eSheet == sheetname && eCol== eCol && eVal == "TRUE" ){
    // the stars are in alignedment, send the email
    // Logger.log("DEBUG: send the email");

    // get the data column A to column E
    var data = sheet.getRange(eRow, 1, 1, 7).getValues();
    // Logger.log("DEBUG: the data range is "+sheet.getRange(eRow, 1, 1, 5).getA1Notation())

    // get the email address
    var emailaddress= sheet.getRange("H2").getValue();
    Logger.log("the email is "+emailaddress);

    // get the message information
    var timestamp = data[0][0];
    var machine = data[0][1];
    var planttorequire = data[0][2];
    var teamtofix = data[0][3];
    var formnum = data[0][4];

    var emailSubject = "[Approved] Form";

    var emailMessage= "Timestamp: "+timestamp+"\n"+
      "Machine/Equiment require: "+machine+"\n"+
      "Plant to require: "+planttorequire+"\n"+
      "Team to fix: "+teamtofix+"\n"+
      "Form no: "+formnum;
    // Logger.log("DEBUG: the message is:"+emailMessage);
    MailApp.sendEmail(emailaddress, emailSubject, emailMessage);

  }else{
    // something doesn't match
    // Logger.log("DEBUG: do not send the email");

  }

}
| improve this answer | |

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.