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

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!

CodePudding user response：

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

function endEmail() {
  MailApp.sendEmail({to: '[email protected]',
  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
}

CodePudding user response：

For your first question, there is but that is very lengthy to imitate/mimic (Check Mike's detailed answer about this)

As for the second question, yes you can. Try this quick and easy approach for exporting a specific sheet to a PDF file.

Script:

function exportSheet(sheetName) {    
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // get list of sheets except the one to export
  var sheets = ss.getSheets().filter(sheet => sheet.getSheetName() != sheetName);
  
  // hide unwanted sheets 
  sheets.forEach(sheet => sheet.hideSheet())

  // will only export the single sheet that is not hidden
  var file = DriveApp.getFolderById(<FOLDER_ID>).createFile(ss.getBlob());
  file.setName('new file name.pdf');

  // show the sheets again after creating the file
  sheets.forEach(sheet => sheet.showSheet())
}

function main() {
  exportSheet('Sheet1');
}

Script:

Sample data:

Output: