CODEWITHSUNDEEP
emailgoogle-apps-scripthtml-tableemail-attachments
Vivian Roberts
Vivian Roberts

Reputation: 127

Attaching a table to an email on Google Apps script

I have a code that sends a summary of a sheet in an email. However, I want to add a summary table from another sheet to that email. The sheet that I want to add to the email looks like this: [![Sheet][1]][1]. This sheet is a filter from another sheet and it filters only the rows where the date is equal to the date in B1. I would like to have a copy of this table that contains only the rows that are not empty and add it to the email. I tried this code:

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responses = ss.getSheetByName("cambios mail");
  var mail = ss.getSheetByName("MAILS");
  var active_range = responses.getActiveRange();
  var cambio = responses.getRange(active_range.getRowIndex(), 5).getValue();
  var nuevo = responses.getRange(3, 11).getValue();
  var cancelados = responses.getRange(3, 12).getValue();
  var fecha =responses.getRange(3, 8).getValue();
  var date = Utilities.formatDate(fecha, "GMT+2", "dd/MM/YYYY")
 var sheet = ss.getSheetByName('cambios drop');
 var values = sheet.getRange("A2:I" + sheet.getLastRow()).getValues();
 var tabla= JSON.stringify(values);
var subject = "CAMBIOS REFERENCIAS: Resumen refes canceladas/añadidas";
    var body = "Los siguientes modelos fueron modificados en el Master Doc ayer fecha " +date +".\n\n" + "Refes añadidas:" + nuevo + "\n\nRefes canceladas:"+ cancelados+ "\n\nCualquier consulta podéis contestar a este mail."+"\n\nAdjunto una tabla con los cambios de drops de ayer. Si no hubo cambios, la tabla aparecerá vacía."+"\n\nTabla"+ tabla+  "\n\nArchivo: https://docs.google.com/spreadsheets/d/";
   var mailCorrecto = mail.getRange(1,2).getValues()
    GmailApp.sendEmail(mailCorrecto, subject, body);

And this is what the table looks like on the email: [![email][2]][2]

Does anybody know how I can format the range to be able to see it as a table on the email? Thank you in advance! [1]: https://i.sstatic.net/Tt3b9.png [2]: https://i.sstatic.net/pZMEd.png

Upvotes: 1

Views: 719

Answers (1)

Cooper
Cooper

Reputation: 64100

A templated html file for sending a table to an email

html:

<!DOCTYPE html>
 <html>
   <head>
     <title>My Title</title>
   </head>
   <body>
     <div id="myimages">
        <? for(var i = 0;i < filename.length; i++) { ?>
          <br /><img src="cid:img<?= i ?>"  /><br />File Name: <?= filename[i] ?><hr /> 
        <? } ?>
     </div>

  • This is the div for the table

       <div id="tabledata">
     <? var vs = getBullSheetData(); ?>
     <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>

  • This is the end

     </body>

gs:

function getBullSheetData() {
  Logger.log('entering getBullSheetData')
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet2');
  const vs = sh.getDataRange().getValues();
  return vs;
}

templated html

Upvotes: 2

Related Questions