I'm working on this

I'm using the following code but running into errors.

One of the error i'm getting is

"ReferenceError: rows is not defined"

I'm modifying the code from this tutorial

function processEdit(e) {
  var sheet = SpreadsheetApp.getActive();
  var rows = sheet.getRangeByName("EmailChanges").getValues();
  var headerRow = rows.shift();
  var editedRow = e.range.getRow();

  var template = HtmlService.createTemplateFromFile("Template");
  template.headerRow = headerRow;
  template.editedRow = editedRow;
  
  
  var html = template.evaluate().getContent();
  if(row>=3 && row<=1339 && col>6 && col==7 && editedSheet.getName()=="Change Requests"){
    //Send email
  var message = "The Column G Cell " editedCell.getA1Notation() " was modified from '" e.oldvalue "' to '" e.value "'";
  MailApp.sendEmail({
    to: "[email protected]",
    subject: "Change",message,
    name:'GSD BI',
    htmlBody: html
  });
}
}

The Html code is

Please see the highlighted row below for information about the Changes.

<hr>
<br>
<table cellpadding='5'>
  <tr>
    <th bgcolor='#eaeaea'><?= headerRow[0] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[1] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[2] ?></th>
  </tr>
  <? var rowIndex = 3; ?>
  <? for(var i = 0; i < rows.length; i  ) { ?>
  <?   var background = rowIndex === editedRow ? "#e06666" : "#ffffff";  ?>
  <tr>
    <td bgcolor='<?= background ?>'><?= rows[i][0] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][1] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][2] ?></td>
  </tr>
  <?    rowIndex = rowIndex   1; ?>
  <? } ?>
</table>

CodePudding user response：

I believe your goal is as follows.

• When the column "G" is manually edited, you want to run your script by the installable OnEdit trigger.
• When the script is run, you want to retrieve the edited row and create message and send them as an email.

Modification points:

• In your script, rows is not defined. This is the reason for your issue of "ReferenceError: rows is not defined". And also, col is not declared.
• When I saw your sample Spreadsheet, the name of the named range is EmailChange. But, you are using the name EmailChanges.
• e.oldvalue always returns undefined. The property name is oldValue.
• In your template, rows is not put.
• From your showing expected goal, message is not included in the template.
• message of MailApp.sendEmail(message) has no property of message.

When these points are reflected in your script, it becomes as follows.

Modified script:

Google Apps Script: Code.gs

function processEdit(e) {
  var { source, range, value, oldValue } = e;
  var sheet = range.getSheet();
  var editedRow = range.rowStart;
  if (editedRow >= 3 && editedRow <= 1339 && range.columnStart == 7 && sheet.getSheetName() == "Change Requests") {
    var message = "The Column G Cell "   range.getA1Notation()   " was modified from '"   oldValue   "' to '"   value   "'";
    var namedRange = source.getRangeByName("EmailChange");
    var rows = namedRange.getValues();
    var headerRow = rows[0];
    var template = HtmlService.createTemplateFromFile("Template");
    var idx = editedRow - namedRange.getRow();
    template.message = message;
    template.rows = [rows[idx]];
    template.headerRow = headerRow;
    template.editedRow = editedRow;
    var html = template.evaluate().getContent();
    MailApp.sendEmail({
      to: "[email protected]",
      subject: "Change",
      name: 'GSD BI',
      htmlBody: html
    });
  }
}

HTML: Template.html

<p><?!= message ?></p>
<p>Please see the highlighted row below for information about the Changes.</p>

<hr>
<br>
<table cellpadding='5'>
  <tr>
    <th bgcolor='#eaeaea'><?= headerRow[0] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[1] ?></th>
    <th bgcolor='#eaeaea'><?= headerRow[2] ?></th>
  </tr>
  <? var rowIndex = 3; ?>
  <? for(var i = 0; i < rows.length; i  ) { ?>
  <?   var background = rowIndex === editedRow ? "#e06666" : "#ffffff";  ?>
  <tr>
    <td bgcolor='<?= background ?>'><?= rows[i][0] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][1] ?></td>
    <td bgcolor='<?= background ?>'><?= rows[i][2] ?></td>
  </tr>
  <?    rowIndex = rowIndex   1; ?>
  <? } ?>
</table>

Note:

• In your script, oldValue is used. In this case, please edit the cell by directly and manually putting the value. When you copy and paste the value, oldValue is not returned. Please be careful about this.

• This modified script is for your sample Spreadsheet. If your actual Spreadsheet is different, please check the name of the named range again.

• In this script, from your showing script, it supposes that your script is run by the installed OnEdit trigger. If you directly run the script with the script editor, an error occurs because of no event object. Please be careful about this.

Reference:

• sendEmail(message) of Class MailApp
• Installable Triggers