I'm using g-script to put entries into an HTML file and send that file as email body. But array-CodePudding

Here is the g-script code I've written to push entires from a sheet where approval hasn't been made and send it to the person who has to make those approvals as a mail reminder.

I keep getting an error that says "Cannot read property 'vin' of undefined ", where vin is a key in the dictionary that I've pushed to the variable called 'responses_sheet'.

From there the variable is read into the HTML file and a for loop run through it, to make separate entries in the table with the headers already defined. But whenever the code reaches the HTML file it returns the error. Please help as I can't figure out the error.


  function time_trig_pending() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet Name");
  var n = sheet.getLastRow();

  var range_ceo_app = sheet.getRange("P1:P").getValues();
  var range_TAT_ceo = sheet.getRange("AA1:AA").getValues();
  var range_vin = sheet.getRange("C1:C").getValues();
  var range_amount_discount = sheet.getRange("K1:K").getValues();
  var range_rm_name = sheet.getRange("N1:N").getValues();
  var range_gm_name = sheet.getRange("M1:M").getValues();
 

  var last_filled = 0
  for (i=0;i<=n;i  ){
    last_filled  = 1;
    if (range_vin[i] == ""){
      break
    }
  }

  var responses_sheet = [];

  for (i=1;i<=last_filled;i  ){
    if (range_ceo_app[i] == "" && range_amount_discount[i] >= 5000 ){
    responses_sheet.push({
      ceo_app : String(range_ceo_app[i]),
      tat: parseInt(range_TAT_ceo[i]),
      vin: String(range_vin[i]),
      discount: parseInt(range_amount_discount[i]),
      rm_name: String(range_rm_name[i]),
      gm_name: String(range_gm_name[i])
    });
  }
  }
  

  var htmlBody = HtmlService.createTemplateFromFile('Pending Approval(html)');
  htmlBody.responses = responses_sheet;

  Logger.log(htmlBody.responses);


  var email_html = htmlBody.evaluate().getContent();

  MailApp.sendEmail(     
      "[email protected]",
      "Approval Reminder Mail",
      '',
      { name : "Reminder System",
        htmlBody : email_html
      });
}

and the HTML file I've linked to it is this :

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>

    <h3>This is to notify you of the pending approvals from your side.</h3>

    <table border="2">
    <caption></caption>
    <tr align="center" style="background-color:rgb(77, 159, 214)">
        <th scope="col">VIN</th>
        <th scope="col">RM Name</th>
        <th scope="col">GM Name</th>
        <th scope="col">Discount</th>
        <th scope="col">TAT</th>
    </tr>
    <tr align="center">
    <? for(i=0;i<=responses.length;i  ) { ?>
        <td> <?= responses[i].vin ?> </td>
        <td> <?= responses[i].rm_name ?> </td>
        <td> <?= responses[i].gm_name ?> </td>
        <td> <?= responses[i].discount ?> </td>
        <td> <?= responses[i].tat ?> </td>
    <? } ?>
    </tr>
  </body>
</html>

CodePudding user response：

I had the same experience as you. In that case, I used forEach instead of the for loop. Although I'm not sure whether this same method can be used for your situation, I would like to propose the following modification.

From:

<? for(i=0;i<=responses.length;i  ) { ?>
    <td> <?= responses[i].vin ?> </td>
    <td> <?= responses[i].rm_name ?> </td>
    <td> <?= responses[i].gm_name ?> </td>
    <td> <?= responses[i].discount ?> </td>
    <td> <?= responses[i].tat ?> </td>
<? } ?>

To:

<? responses.forEach(e => {?>
  <td> <?= e.vin ?> </td>
  <td> <?= e.rm_name ?> </td>
  <td> <?= e.gm_name ?> </td>
  <td> <?= e.discount ?> </td>
  <td> <?= e.tat ?> </td>
<? }); ?>