Howto duplicate this array table for another list of value in cells $A34:$B40-CodePudding

I have this table on my sheet

I have the code below that capture this information in an array

    var values  = ws.getRange('$A$24:$B$30').getValues();
//remove empty arrays
  var filtered_values = values .filter(function(r){
    return r.join("").length>0;
  });

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

The code to preformat the html email

//variable pour les quantites
emailTemp.content = arr_obj;
Logger.log(emailTemp.arr_obj);

//variable pour la boucle des lignes
var i = ws.getRange("$B$31").getValue();
//Logger.log(i);

The code to generate the draft email

//evaluation du template d'email en creant un html output avec son texte
  var htmlMessage = emailTemp.evaluate().getContent();
//  Logger.log(from);
//  Logger.log(cc);
//  Logger.log(qui);
//  Logger.log(sujet);
//  Logger.log(nom);
//  une fois fait l'envoyé (en draft)
// avec comme valeur (recipient,subject, body) 
      GmailApp.createDraft(
      mail,
      sujet,
        
      "Votre messagerie ne support pas HTML",
//envoyé le message créé htmlbody est le htmlmessage créé.
         {name: nom, htmlBody: htmlMessage,cc: cc, from: from});

And then the email table in html

<table style="border-collapse: collapse; width: 100%; height: 60px;" border="1">
<thead>
<tr style="height: 22px;">
<td style="width: 29.2011%; background-color: #c2e0f4; height: 22px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;">Date</span></td>
<td style="width: 26.3085%; background-color: #c2e0f4; height: 22px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;">Quantit&eacute;</span></td>
</tr>
</thead>
<tbody>
    <?var ctr = 1  
      content.forEach(function (arrayItem) { ?>
<tr style="height: 19px;">
<td style="width: 29.2011%; height: 19px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;"><div > <?= arrayItem.date ?></div></span></td>
<td style="width: 26.3085%; height: 19px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;"><?= arrayItem.quantite ?></span></td>
</tr>
</tbody>
      <? ctr   ?>
    <? }); ?>
</table>

What I would like is another array similar but with the value from cell $A34:$B40

Also when I generated the draft I have the Date including time : Tue May 17 2022 00:00:00 GMT 0200 (Central European Summer Time)

Is it possible to have the format of the date like in the picture 17/05/2022 ?

Thank you

CodePudding user response：

Create a second array - with a second name

So, while the code for arr_obj is:

    var values  = ws.getRange('$A$24:$B$30').getValues();
//remove empty arrays
  var filtered_values = values .filter(function(r){
    return r.join("").length>0;
  });

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

simply add to this code the block for arr_obj2:

  values  = ws.getRange('$A34:$B40').getValues();
//remove empty arrays
  filtered_values = values .filter(function(r){
    return r.join("").length>0;
  });

  var arr_obj2 = [];
  //Create an Array of Object and populate using filtered_values 
  filtered_values.forEach(val => {
    var obj = {
      "date" : val[0],
    "quantite" : val[1],
    
    }
    arr_obj2.push(obj)
  })
 Logger.log(arr_obj2);
//  Logger.log(values);

Note that the additional code block will overwrite the vaariables values and filtered_values - this is not a concern, given that you do not need those variables anymore, once you build arr_obj. However, you can alternatively create new variables values2 and filtered_values2.

In the next step, add to your email template the new array in addition to the existing one.

For this, change

emailTemp.content = arr_obj;

emailTemp.content = arr_obj;
emailTemp.content2 = arr_obj2;

The last step would be to create an additoinal table in your html and populate it with the new variable content2:

<table style="border-collapse: collapse; width: 100%; height: 60px;" border="1">
<thead>
<tr style="height: 22px;">
<td style="width: 29.2011%; background-color: #c2e0f4; height: 22px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;">Date</span></td>
<td style="width: 26.3085%; background-color: #c2e0f4; height: 22px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;">Quantit&eacute;</span></td>
</tr>
</thead>
<tbody>
    <?var ctr = 1  
      content.forEach(function (arrayItem) { ?>
<tr style="height: 19px;">
<td style="width: 29.2011%; height: 19px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;"><div > <?= arrayItem.date ?></div></span></td>
<td style="width: 26.3085%; height: 19px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;"><?= arrayItem.quantite ?></span></td>
</tr>
</tbody>
      <? ctr   ?>
    <? }); ?>
</table>

<table style="border-collapse: collapse; width: 100%; height: 60px;" border="1">
<thead>
<tr style="height: 22px;">
<td style="width: 29.2011%; background-color: #c2e0f4; height: 22px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;">Date</span></td>
<td style="width: 26.3085%; background-color: #c2e0f4; height: 22px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;">Quantit&eacute;</span></td>
</tr>
</thead>
<tbody>
    <?var ctr = 1  
      content2.forEach(function (arrayItem) { ?>
<tr style="height: 19px;">
<td style="width: 29.2011%; height: 19px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;"><div > <?= arrayItem.date ?></div></span></td>
<td style="width: 26.3085%; height: 19px; border-color: #000000; border-style: solid;"><span style="font-family: arial, helvetica, sans-serif; font-size: 10pt;"><?= arrayItem.quantite ?></span></td>
</tr>
</tbody>
      <? ctr   ?>
    <? }); ?>
</table>

To your problem with the date formatting:

Try if replacing

var values = ws.getRange('$A$24:$B$30').getValues();

through

var values = ws.getRange('$A$24:$B$30').getDisplayValues();

will solve the issue.

Background:

If your dates are stored in the spreadsheet as date objects, the script will automatically process the values to the default date format Tue May 17 2022 00:00:00 GMT 0200 (Central European Summer Time).
If instead you use getDisplayValues(), this will retrieve the values as strings (which in your case is preferable), and thus the date formatting will not be converted.

Alternatively, if you want to work with date objects / getValues90, you can use the method Utilities.formatDate(date, timeZone, format) to modify the displayed date formatting.

For this, change

"date" : val[0]

"date" : Utilities.formatDate(val[0], "GMT","dd/MM/yyyy")

(Please modify GMT to your desired timezone.)