E-mail google spreadsheet range as HTML table preserving values?

Question

I have a script I modified and what it does is.

It grabs a spreadsheet range, and then it takes all the values and re-formats them into an HTML table which is sent via e-mail. So far ALL of this works. EXCEPT: The numbers it displays are using a number format with no decimal end. The original spreadsheet, for example, has the formatted value of 10,249.2 which is rounded but the HTML table in the email shows: 10249.243990558198

This is the code I'm using:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Send EOD",
    functionName : "sendEmail"
  }];
  sheet.addMenu("Send Email", entries);
};

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
 // adding for range
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
 //  var schedRange = sheet.getRange("T10:AC"+lastColumn);
   var schedRange = sheet.getRange("T10:AC28");
 // end of range 
  var range = sheet.getDataRange();
  var formattedDate = Utilities.formatDate(new Date(), "GMT+1", "MM/dd/yyyy")
  var subject = "Daily Volume - " + formattedDate;

  // Dynamically get whole sheet based on lastrow and lastcolumn
  // var schedRange = sheet.getRange(1, 1, lastRow, lastColumn);

  // We only want the schedule within borders, so
  // these are handled separately.
  var body = '<div style="text-align:center;display: inline-block;font-family: arial,sans,sans-serif">'
  body += '<H1>'+ "Daily Volume   " + formattedDate  +'</H1>';
  body += '<H2>'
       + '</H2>';
  body += getHtmlTable(schedRange);
  body += '</div>';
  debugger;
  //Logger.log(body);
  recipient = 'my@email.com'+","+"mysecondemail@email.com";
  GmailApp.sendEmail(recipient, subject, "Requires HTML", {htmlBody:body})
}

/**
 * Return a string containing an HTML table representation
 * of the given range, preserving style settings.
 */
function getHtmlTable(range){
  var ss = range.getSheet().getParent();
  var sheet = range.getSheet();
  startRow = range.getRow();
  startCol = range.getColumn();
  lastRow = range.getLastRow();
  lastCol = range.getLastColumn();
  // testing
 // var lastRow = sheet.getLastRow();
  // var lastColumn = sheet.getLastColumn();
  // var schedRange = sheet.getRange("T10:AC"+lastColumn);
  //end of testing 

  // Read table contents
  var numberFormats = range.getNumberFormats();
  var data = range.getValues();


  // Get css style attributes from range
  var fontColors = range.getFontColors();
  var backgrounds = range.getBackgrounds();
  var fontFamilies = range.getFontFamilies();
  var fontSizes = range.getFontSizes();
  var fontLines = range.getFontLines();
  var fontWeights = range.getFontWeights();
  var horizontalAlignments = range.getHorizontalAlignments();
  var verticalAlignments = range.getVerticalAlignments();

  // Get column widths in pixels
  var colWidths = [];
  for (var col=startCol; col<=lastCol; col++) {
    colWidths.push(sheet.getColumnWidth(col));
  }
  // Get Row heights in pixels
  var rowHeights = [];
  for (var row=startRow; row<=lastRow; row++) {
    rowHeights.push(sheet.getRowHeight(row));
  }

  // Future consideration...
  // var numberFormats = schedRange.getNumberFormats();





  // Build HTML Table, with inline styling for each cell
  var tableFormat = 'style="border:1.5px solid black;border-collapse:collapse;text-align:center" border = 1.5 cellpadding = 5';
  var html = ['<table '+tableFormat+'>'];
  // Column widths appear outside of table rows
  for (col=0;col<colWidths.length;col++) {
    html.push('<col width="'+colWidths[col]+'">')
  }
  // Populate rows
  for (row=0;row<data.length;row++) {
    html.push('<tr height="'+rowHeights[row]+'">');
    for (col=0;col<data[row].length;col++) {
      // Get formatted data
      var cellText = data[row][col];
      if (cellText instanceof Date) {
        cellText = Utilities.formatDate(
                     cellText,
                     ss.getSpreadsheetTimeZone(),
                     'MMM/d EEE');
      }
      var style = 'style="'
                + 'color: ' + fontColors[row][col]+'; '
                + 'font-family: ' + fontFamilies[row][col]+'; '
                + 'font-size: ' + fontSizes[row][col]+'; '
                + 'font-weight: ' + fontWeights[row][col]+'; '
                + 'background-color: ' + backgrounds[row][col]+'; '
                + 'text-align: ' + horizontalAlignments[row][col]+'; '
                + 'vertical-align: ' + verticalAlignments[row][col]+'; '
                +'"';
      html.push('<td ' + style + '>'
                +cellText
                +'</td>');
    }
    html.push('</tr>');
  }
  html.push('</table>');

  return html.join('');
}

score 1 · Answer 1 · answered Jan 05 '18 at 01:18

1

If you want the values as they are show on screen then use getDisplayValues() instead of getValues().

Difference between getValue() and getDisplayValue() on google app script

answered Jan 05 '18 at 01:18

Rubén

34,714
9
70
166

What about this part, I basically have formulas running for 110 rows however, when there is data only say Colum A and B populate with a number. How can i make it so that my ROW range depends on whether or not the data = null. var schedRange = sheet.getRange("T10:AC28"); I'd like to make it so that the number 28 there depends on the last ROW in column T to have any real data. – Gerald Lopez Jan 05 '18 at 22:50
@GeraldLopez That looks like a different question. Please post a new one. – Rubén Jan 05 '18 at 22:53

E-mail google spreadsheet range as HTML table preserving values?

1 Answers1