Email Google Sheets Content like Copy/Paste with Google Apps Script

Question

When you have a google sheet with a series of cells that have data in them, they can be easily copy/pasted into an email, where formatting and spacing is preserved, and tables are even created automatically when appropriate. Is there a way to mimic that behavior using Apps Script? My gut says no, but I wanted to check.

Alternatively, can I export a tab of a Google Sheet to a PDF or a JPEG using Apps Script?

Thanks!

Answer 1

You can combine the above option with sending the table as a PDF attachment in order to emulate style of the tables you have in your sheet.

   function onOpen(e) {
   var menu = SpreadsheetApp.getUi().createMenu("⚙️ Enviar Email");
   menu.addItem("Email PDF", "sendPDFHTMLEmail");
   menu.addToUi();}

function sendPDFHTMLEmail() {
  DocumentApp.getActiveDocument();
  DriveApp.getFiles();
  const ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1FBPBJ_VIBBYc-HLA-6A3TJwABO9ndl3T6mPRYXlFWHU/edit");
  const value = ss.getSheetByName("COVID / ALERTA").getRange("A1").getValue();
//  const email = value.toString();
//  const subject = ss.getSheetByName("COVID / ALERTA").getRange("A1:G60").getValue();
const subjectDate = ss.getSheetByName("COVID / ALERTA").getSheetName() +" " + ss.getSheetByName("COVID / ALERTA").getRange("H1").getValue().toISOString().slice(0, 10);
  const body = "Estimados, enviamos informe de trazabilidad de hoy.<p>"+"Atte.<br>"+"Enfermería de Junior School";
  const url = 'YOUR SHEET/export?';

  const exportOptions =
    'exportFormat=pdf&format=pdf' + 
    '&size=letter' + 
    '&landscape=true' + 
    '&fitw=true' + 
    '&sheetnames=true&printtitle=false' + 
    '&pagenumbers=false&gridlines=true' + 
    '&fzr=true' + 
    '&if=false' +
    '&ic=true' +
    '&r1=51' +
    '&c1=0' +
    '&r2=102' +
    '&c2=20'+
    '&gid=1218787468&range=1:60';
  
  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();

  MailApp.sendEmail({to: 'AN EMAIL',
  subject: subjectDate,
  htmlBody: testTableHTML(), body, attachments:
    [{
      fileName: subjectDate + ".pdf",
      content: response.getBytes(),
      mimeType: "application/pdf"
      }] })
};

function testTableHTML(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('COVID / ALERTA')
  var plage = sh.getRange('A1:G60'); // adapt as necessary
  return (tableHTML(plage))
}
function tableHTML(plage){
  var data=plage.getDisplayValues()
  var taille=plage.getFontSizes()
  var fond=plage.getBackgrounds()
  var couleur=plage.getFontColors()
  var police=plage.getFontFamilies()
  var body = "Estimados, enviamos informe de trazabilidad de hoy.<p>"+"Atte.<br>"+"Enfermería de Junior School<p><p>";
  var htmltable = body + '<table style="border:1px solid black;">';
  for (row = 0; row<data.length; row++){
    htmltable += '<tr>';
    for (col = 0 ;col<data[row].length; col++){
      if (data[row][col] === "" || 0) {htmltable += '<td>' + '&nbsp;' + '</td>';} 
      else
        htmltable += '<td style="font-family:' + police[row][col] + '; background-color:' + fond[row][col] + '; color:' + couleur[row][col] + '; font-size:' + taille[row][col] + 'px;">' + data[row][col] + '</td>';
      }
      htmltable += '</tr>';
    }
  htmltable += '</table>';
  return htmltable}

Answer 2

You can send emails this way you will have something like your spreadsheet

function endEmail() {
  MailApp.sendEmail({to: 'who@gmail.com',
  subject: 'my subject', 
  htmlBody: testTableHTML()})
};
function testTableHTML(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('mySheet')
  var plage = sh.getRange('A1:H33'); // adapt as necessary
  return (tableHTML(plage))
}
function tableHTML(plage){
  var data=plage.getDisplayValues()
  var taille=plage.getFontSizes()
  var fond=plage.getBackgrounds()
  var couleur=plage.getFontColors()
  var police=plage.getFontFamilies()
  var htmltable = '<table style="border:1px solid black;">';
  for (row = 0; row<data.length; row++){
    htmltable += '<tr>';
    for (col = 0 ;col<data[row].length; col++){
      if (data[row][col] === "" || 0) {htmltable += '<td>' + '&nbsp;' + '</td>';} 
      else
        htmltable += '<td style="font-family:' + police[row][col] + '; background-color:' + fond[row][col] + '; color:' + couleur[row][col] + '; font-size:' + taille[row][col] + 'px;">' + data[row][col] + '</td>';
      }
      htmltable += '</tr>';
    }
  htmltable += '</table>';
  return htmltable
}