-1

Sorry, this might be a bit basic but it has been a while since I've used google scripts.

I'm trying to create a sheet that, when a checkbox in column I (Complete) is marked as true, it will send an email to the address populated in the same row in column E (Email).

I would prefer if it also added a timestamp in column J (Completed Date) to show when the checkbox was marked. Then, if possible, include the text from column B (Order Number) in the subject header, and the text from column G (Query) in the body of the email. And of course, avoid it looping so it only triggers the email when the relevant cell in the row is checked.

Example Image

General Grievance
  • 4,555
  • 31
  • 31
  • 45
Paul
  • 3
  • 2
  • It would seem that your goal will require an installable onedit trigger. – Cooper Jun 16 '21 at 18:24
  • The onEdit trigger would work. I took a different approach for a similar project. I had a button the user would click to send an email and it would use the data from the row the user was on. The script confirmed they were on the correct row prior to sending. – JohnA Jun 16 '21 at 19:05

1 Answers1

0

You can refer to this sample code:

Code.gs

function onEdit(e){
  var sheet = e.source.getActiveSheet();
  var cell = e.range;

  Logger.log(cell.getColumn());
  Logger.log(cell.isChecked());
  //Check if the checkbox in column I(index 9) was checked
  if(sheet.getName() == "Sheet2" && cell.getColumn() == 9 && cell.isChecked()){

    //get current row values from column A to column G
    var values = sheet.getRange(cell.getRow(),1,1,7).getDisplayValues().flat();
    Logger.log(values);
    var orderNum = values[1];
    var agent = values[3];
    var email = values[4];
    var query = values[6];

    //create and update the email's hmtl body
    var templ = HtmlService.createTemplateFromFile('html_template');
    templ.receiver = agent;
    templ.query = query;
    var message = templ.evaluate().getContent();

    //Send email
    MailApp.sendEmail({
      to: email,
      subject: "Test Email Order Num: "+orderNum,
      htmlBody: message
    });

    //Add timestamp at column J(index 10)
    var timeZone = Session.getScriptTimeZone();
    var date = Utilities.formatDate(new Date(),timeZone, "MM-dd-yyyy");
    sheet.getRange(cell.getRow(),10).setValue(date);
  }
}

html_remplate.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <p>Hello <?= receiver ?>,</p>
    <p> <?= query ?></p>
    <p>Best Regards,</p>
    <p>Support,<br>Company.com</p>
    
  </body>
</html>

Pre-requisite:

As mentioned by @Cooper via comment, your code will require an installable onEdit trigger since it can call services(such as MailApp.sendEmail()) that requires authorization

enter image description here

enter image description here

What it does?

  1. Get the active sheet of the current spreadsheet and the cell range using Google Sheets onEdit event object
  2. Check if the modified cell is in the specific sheet.
  3. Check if the modified cell is in column I (index 9) and if the checkbox is checked using Range.isChecked()
  4. Get the current row values from column A to column G using Sheet.getRange(row, column, numRows, numColumns) and Range.getValues(). This will return a 2-d array, I used array.flat() to change it to 1-d array.
  5. Get the specific information in the row values using its specific index. Example, order number should be in index 1 of the array values
  6. Create an HtmlTemplate using HtmlService.createTemplateFromFile(filename). Update the necessary html content variable. Then create an HtmlOutput using HtmlTemplate.evaluate()
  7. Get the content of the htmloutput using HtmlOutput.getContent()
  8. Send the email using MailApp.sendEmail(message)
  9. Lastly, add date timestamp in column J(index 10). I created a date string using Utilities.formatDate(date, timeZone, format)

Note:

  • You can change the date format based on your preference, currently I used "MM-dd-yyyy". Refer here for more details on how to configure date time format
  • If you are planning to run the onEdit() using the Run button in the script editor, please expect the execution will fail because the event object e is not defined. If you wish to debug an onEdit() trigger, you can add logs in your code then modify a cell in your sheet then check the execution logs in the execution tab

Output:

enter image description here

enter image description here

Ron M
  • 5,791
  • 1
  • 4
  • 16