Google Apps Script dynamically table row in an html file from Gsheet

Question

I would like to create a dynamically rows in a table in an html file from a google apps-script with variable. and for this I would like to have some helps as I'm really new on this. at this moments mail email as fixed rows with variable on each rows therefore if they are empty I have empty rows in my table.

I have the function called var_rows I have variables in cells to be the added in cells on the html table. I have also created a countable variable that will populate the rows if it's not 0 and do not add the rows if the count is 0.

var_row.gs

            function var_rows() {
      //
    //  https://www.youtube.com/watch?v=h2z13YE3kJg
    //  Source pour le code
    //
    //
    // variable pour la creation du fichier HTML
    var emailTemp = HtmlService.createTemplateFromFile("gsuite_demande_validation");
    //variable pour appeller le "sheet" du fichier actif  
    var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("VALIDATION.FORM");
    //  la variable d'appel pour l'objet dans l'onglet final
    var wsSettings = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("VALIDATION.FORM");
    var from  = wsSettings.getRange("$C$2").getValue();
    var mail  = wsSettings.getRange("$C$3").getValue();
    var cc = wsSettings.getRange("$C$4").getValue();
    var sujet = wsSettings.getRange("$C$5").getValue();
    
    var action1 = wsSettings.getRange("$C$16").getValue();
    var utilisateur1 = wsSettings.getRange("$C$17").getValue();

    
    var count2 = wsSettings.getRange("$D$23").getValue();
    var action2 = wsSettings.getRange("$C$22").getValue();
    var utilisateur2 = wsSettings.getRange("$C$23").getValue();

    
    emailTemp.from = from;
    emailTemp.mail = mail;
    emailTemp.cc = cc;
    emailTemp.sujet = sujet;
    emailTemp.action1 = action1;
    emailTemp.utilisateur1 = utilisateur1;
    emailTemp.count2 = count2;
    emailTemp.action2 = action2;
    emailTemp.utilisateur2 = utilisateur2;

    
GmailApp.createDraft(
          mail,
          sujet,
            
          "Votre messagerie ne support pas HTML",
             {name: nom, htmlBody: htmlMessage,cc: cc, from: from});
}

I have an email template called

gsuite_demande_validation.html

  <table style="border-collapse: collapse; width: 83.662%; height: 1129px;" border="1">
    <tbody>
    <tr style="height: 47px;">
    <td style="height: 47px; width: 37.7105%;">
    <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;">Action 1 :</span></p>
    </td>
    <td style="height: 47px; width: 62.1212%;">
    <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;"><?= action1 ?></span></p>
    </td>
    </tr>
    <tr style="height: 47px;">
    <td style="height: 47px; width: 37.7105%;">
    <p dir="ltr"><span style="font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;">Utilisateur d&eacute;fini</span></p>
    </td>
    <td style="height: 47px; width: 62.1212%;">
    <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;"><?= utilisateur1 ?></span></p>
    </td>
    </tr>
   
<!--another action here if count2 is not 0 -->
    <tr style="height: 15.75pt;">
    <td style="height: 19px; width: 37.7105%;">&nbsp;</td>
    <td style="height: 19px; width: 62.1212%;">&nbsp;</td>
    </tr>
    <tr style="height: 47px;">
    <td style="height: 47px; width: 37.7105%;">
    <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;">Action 2 :</span></p>
    </td>
    <td style="height: 47px; width: 62.1212%;">
    <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;"><?= action2 ?></span></p>
    </td>
    </tr>
    <tr style="height: 47px;">
    <td style="height: 47px; width: 37.7105%;">
    <p dir="ltr"><span style="font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;">Utilisateur d&eacute;fini</span></p>
    </td>
    <td style="height: 47px; width: 62.1212%;">
    <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;"><?= utilisateur2 ?></span></p>
    </td>
    <tr style="height: 15.75pt;">
    <td style="height: 19px; width: 37.7105%;">&nbsp;</td>
    <td style="height: 19px; width: 62.1212%;">&nbsp;</td>
    </tr>
    </tbody>
    </table>

It will be nice to have a code generating the rows according to the information contenned in the action variables then populating the email with the additioned rows if variable exist. Thank you for your help.

Nikko J. · Accepted Answer · 2020-12-22 15:20:02Z

Instead of declaring variables every time a new entry is added, you could store the value of Action and Utilisateur défini in an Array of Object in JavaScript and loop through each element in the HTML to dynamically populate the table rows.

Example Sheet:

Try this code below:

function myFunction() {
  var emailTemp = HtmlService
      .createTemplateFromFile('test');
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var values = ws.getRange("A:B").getValues();

  //remove empty arrays
  var filtered_values = values.filter(function(r){
    return r.join("").length>0;
  });

  //remove headers
  filtered_values.shift();

  var arr_obj = [];
  //Create an Array of Object and populate using filtered_values 
  filtered_values.forEach(val => {
    var obj = {
      "action_value" : val[0],
      "user_defined_value" : val[1]
    }
    arr_obj.push(obj)
  })

  emailTemp.content = arr_obj;
  var message = emailTemp.evaluate().getContent();
  


  MailApp.sendEmail({
    to: "xxxsome-email-addressxxx",
    subject: "Your application is approved!",
    htmlBody: message
  }); 
}

test.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <table style="border-collapse: collapse; width: 83.662%; height: 1129px;" border="1">
    <tbody>
    <tr style="height: 47px;">
    
    <?var ctr = 1  
      content.forEach(function (arrayItem) { ?>
      <tr style="height: 47px;">
        <td style="height: 47px; width: 37.7105%;">
        <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;">Action<?=ctr?>:</span></p>
        </td>
        <td style="height: 47px; width: 62.1212%;">
        <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;"><?= arrayItem.action_value ?></span></p>
        </td>
      </tr>

      <tr style="height: 47px;">
        <td style="height: 47px; width: 37.7105%;">
        <p dir="ltr"><span style="font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;">Utilisateur d&eacute;fini:</span></p>
        </td>
        <td style="height: 47px; width: 62.1212%;">
        <p dir="ltr"><span style="font-size: 10pt; font-family: Roboto, RobotoDraft, Helvetica, Arial, sans-serif;"><?= arrayItem.user_defined_value ?></span></p>
        </td>
      </tr>

      <tr style="height: 15.75pt;">
        <td style="height: 19px; width: 37.7105%;">&nbsp;</td>
        <td style="height: 19px; width: 62.1212%;">&nbsp;</td>
      </tr>
      <? ctr++ ?>
    <? }); ?>

  </body>
</html>

Sample Output:

Reference:

Templated HTML

Hello Nkko J. I tried your suggestion and got an error in the line where table is at this time you wrote : tabe) the script expeting something else: SyntaxError: Unexpected token ')' (ligne 17, fichier "Code.gs")
@terry - Kindly remove the table) part in your code, It was supposedly included in the comment part. Apologies for causing confusion.

Collectives™ on Stack Overflow

Google Apps Script dynamically table row in an html file from Gsheet

var_row.gs

gsuite_demande_validation.html

1 Answer 1

Reference:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

var_row.gs

gsuite_demande_validation.html

1 Answer 1

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related