Google Sheets Email when box is checked

[Jenny Fossi]

Trying to create a script to send an automated email when a box is checked in a row. This is for new employees - send a notification to other departments of the new hire. 

I want to send columns A-E in the body of the email and the Checkbox is in Column H. I would like for it to popup once the box is checked to confirm sending the email. 

I have the email address for the department heads listed in tab "Formulas" (3) in Column 1. 

I have tried several sample scripts and can't quite get this right. I'm new to scripting! 

Any help would be appreciated! 

Test Employee Checklist - Sheet

CONFIDENTIALITY NOTICE: This communication is for the sole use of the intended recipient(s) and may contain information that is confidential, privileged, or otherwise exempt from disclosure under applicable law. If you are not the intended recipient(s), the dissemination, distribution, or copying of this message is strictly prohibited. If you have received this communication in error, please contact the sender immediately and destroy all copies of the original message and any attachments. Receipt by anyone other than the named recipient(s) does not constitute a waiver of any applicable privilege.

[Fer Tovar]

Do you speak spanish?

Is more Easy for me.

When mark the H column, send email?

Could you create a script that check all the rows, but if you have many rows Is low

And you need a new row that the script mark send for dont send again.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/7d09a421-3cac-412e-a462-98d4c66ee3d7n%40googlegroups.com.

[Jon Couch]

Jenny, the easiest solution for this task is a Google Sheets addon called Form Mule (https://cloudlab.newvisions.org/form-mule) from a group called New Visions Cloudlab which was formed to develop Google apps for New York City public schools. It's pretty straightforward and you could get it working pretty quickly. I used to use that extensively until the university determined they had issues with the license and restricted access to it. Once it was no longer available to us, I wrote a similar application for new implementations. If you're unable to use Form Mule, let me know and I can give you some code and instructions to implement an apps script instance. It's just a little more involved than the Form Mule route. Good luck with it.

Jon Couch

--

[Braja Patnaik]

Jenny

Can you pls try this code in NewEmail.gs.

You will have to set up an onedit trigger for the function MyEdit. 

You have to go to triggers, then add triggers, then choose which function to run  =>   MyEdit, then event source => spreadsheet, event type => onEdit.

Pls handle errors if required.

let sheetName = "New";

const NOTIFICATION_COLUMN = 8;

let activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

let activeCellOfActiveSheet = activeSheet.getActiveCell();

function MyEdit(e) {

  if (e.range.getColumn() != NOTIFICATION_COLUMN) { Logger.log("returning"); return; }

  if (activeCellOfActiveSheet.getValue()) //checkbox is true

  {

    sendEmailWhenBoxIsChecked();

  }

}

function sendEmailWhenBoxIsChecked() {

  if (activeCellOfActiveSheet.getValue()) //Checkbox is TRUE

  {

    var emp_val = activeSheet.getRange(activeCellOfActiveSheet.getRowIndex(), 1, 1, 5).getValues();

    var ln = emp_val[0][0];

    var fn = emp_val[0][1];

    //var mn = emp_val[0][2];

    var title = emp_val[0][3];

    var bldg = emp_val[0][4];

  }

  let msg = ln + " " + fn + " has been hired as a(n) " + title + " at " + bldg + " ."

  showAlert(msg);

}

function showAlert(msg) {

  var ui = SpreadsheetApp.getUi();

  var result = ui.alert('Please confirm to send mail', msg, ui.ButtonSet.YES_NO);

  if (result == ui.Button.YES) {

    ui.alert('Confirmation received...Mail being sent');

    var mail_to = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Formulas").getRange(2, 1, 3).getValues(); //gets 3 values of email from the sheet

    for (var i = 0; i < mail_to.length; i++)

      GmailApp.sendEmail(mail_to[i][0], "New Staff", msg);

  }

  else {

    ui.alert('Confirmation denied...Clearing checkbox');

    activeSheet.getRange(activeCellOfActiveSheet.getRowIndex(), 8, 1).setValue('FALSE');

  }

}