I'd like an email to be sent whenever a checkbox is checked in a google sheet. Does anyone have good code I can use?

Example: like when column F check box have been checked it will auto send an email like attached picture.

Sorry as i totally noob about scripts and don't no where to start.

enter image description here

CodePudding user response：

Send Email When Check Box in A1 Is Checked

function sendEmailWhenCheckBoxIsChecked(e) {
  const sh = e.range.getSheet();
  if(sh.getName() == 'Send Emails' && e.range.columnStart==1 && e.range.rowStart == 1 && e.value == "TRUE") {
    e.range.setValue('FALSE');
    GmailApp.sendEmail("your email addres","The subject","The message");
  }
}

• Create installable trigger

  function createTriger() { if(ScriptApp.getProjectTriggers().filter(t => t.getHandlerFunction() == 'sendEmailWhenCheckBoxIsChecked').length ==0) { ScriptApp.newTrigger('sendEmailWhenCheckBoxIsChecked').forSpreadsheet(ss.getActive()).onEdit().create(); } }

Installable Triggers

CodePudding user response：

This runs with an installable trigger. If you don't want an installable trigger, you can change the function sendEmailWhenBoxIsChecked() to onEdit(), delete the onOpen() function and get rid of wholeSheet variable.

Let me know if you have any questions.

const wholeSheet = SpreadsheetApp.getActive();
let sheetName = "name of your sheet";
let activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);

function sendEmailWhenBoxIsChecked() {
  let columnIndexOfCheckbox = 6; //column index of where your checkbox is located
  let rowIndexOfCheckbox = 2; //column index of where your checkbox starts
  let activeCellOfActiveSheet = activeSheet.getActiveCell();
  if (
    activeCellOfActiveSheet.getColumn() === columnIndexOfCheckbox
    && activeCellOfActiveSheet.getRowIndex() >= rowIndexOfCheckbox
    && !activeCellOfActiveSheet.isBlank()
  ) {
    let checkbox = activeSheet.getActiveCell();
    if (checkbox.isChecked()) {
      GmailApp.sendEmail("recipient email", "Email header", "Email content or body");
    }

  }

}

function onOpen() {
  let createTrigger = true;
  ScriptApp.getProjectTriggers().forEach(trigger => {
    if (trigger.getEventType() === ScriptApp.EventType.ON_EDIT && sendEmailWhenBoxIsChecked.name === trigger.getHandlerFunction()) createTrigger = false;
  });
  if (createTrigger) {
    ScriptApp.newTrigger(sendEmailWhenBoxIsChecked.name)
      .forSpreadsheet(wholeSheet)
      .onEdit()
      .create();
  }
}