Sending an email whenever Google Sheets specific range is edited

Question

I have a simple app script that will send an email whenever my G-sheet is edited. This works fine but it will send an email for every edit in any cell. So, I need to define a specific range as B3:E7 in my Gsheet and the email should trigger only after B3:E7 range is edited. No emails should send for other edits. Please help me on this. I took this code from this tutorial https://spreadsheet.dev/send-email-when-google-sheet-is-edited#:~:text=Step%201%3A%20Create%20your%20spreadsheet,whenever%20your%20spreadsheet%20is%20edited.

    //@OnlyCurrentDoc
function processEdit(e) {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getRangeByName("signups").getValues();
  var headerRow = rows.shift();
  var editedRow = e.range.getRow();
  
  var template = HtmlService.createTemplateFromFile("Template");
  template.headerRow = headerRow;
  template.editedRow = editedRow;
  template.rows = rows;
  
  var html = template.evaluate().getContent();
  
  MailApp.sendEmail({
    to: "myemail@gmail.com ",
      subject: "This is test mail",
    htmlBody: html
  });
}

Does this answer your question? [Google Script onedit(e) event restricted to a specific sheet and specific range](https://stackoverflow.com/questions/46499503/google-script-onedite-event-restricted-to-a-specific-sheet-and-specific-range) — Kos, Sep 10 '22 at 13:17

Cooper · Accepted Answer · 2022-09-11T04:58:41.843

Try this:

function processEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() == "Your sheet name" && e.range.columnStart > 1 && e.range.columnStart < 6 && e.range.rowStart > 2 && e.range.rowStart < 8) {
    var rows = sh.getRangeByName("signups").getValues();
    var headerRow = rows.shift();
    var template = HtmlService.createTemplateFromFile("Template");
    template.headerRow = headerRow;
    template.editedRow = e.range.rowStart;
    template.rows = rows;
    var html = template.evaluate().getContent();
    MailApp.sendEmail({
      to: "myemail@gmail.com ",
      subject: "This is test mail",
      htmlBody: html
    });
  }
}

I got this to work with some minor mods because I didn't want to have to do any extra work and I had the scriptlets for a table already done

GS:

function onMyEdit(e) {
  //Logger.log(JSON.stringify(e));
  //e.source.toast('Entry');
  var sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart > 1 && e.range.columnStart < 6 && e.range.rowStart > 2 && e.range.rowStart < 8) {
    //e.source.toast("Flag1");
    var rows = e.source.getRangeByName("signups").getValues();
    //var headerRow = rows.shift();
    var template = HtmlService.createTemplateFromFile("ah2");
    //template.headerRow = headerRow;
    //template.editedRow = e.range.rowStart;
    template.rows = rows;
    var html = template.evaluate().getContent();
    //Logger.log(html);
    GmailApp.createDraft("myemail@gmail.com","Subject",null,{htmlBody: html});
  }
}

I used Gmail because I just wanted to create a draft

HTML:

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
</head>
<body>
  <div id="tabledata">
       <? var vs = rows; ?>
       <table>
         <? vs.forEach((r,i)=>{ ?>
           <tr>
           <? r.forEach((c,j)=>{ ?>
             <? if(i == 0) { ?>
            <th style="padding:2px 5px;font-weight:bold;border:1px solid black;"><?= c ?> </th>           
           <? } else { ?>
             <td style="padding:2px 5px;border:1px solid black;"><?= vs[i][j] ?> </td>
           <? } ?>
         <?  }); ?>
           </tr>
         <? }); ?>
       </table>
     </div>
</body>
</html>

Image of email with table

Hi Cooper. Thank you for helping me out. I tried your code. but it did not work. No mail was sent. Could you kindly have a look — isanka rangana, Sep 11 '22 at 01:43
https://docs.google.com/spreadsheets/d/17ngxuIZ37Bveuyli92Gj1w2NzSIi7y-aypbQrE4Dfw0/edit#gid=1486321515 — isanka rangana, Sep 11 '22 at 04:00

Sending an email whenever Google Sheets specific range is edited

1 Answers1