Google Sheets Script: email with a HTML Table that only has a set of

Question

In Google Spreadsheet I want to be able to send a email with a HTML Table within the Email. I have accomplished this below and would like to now extend the functionality more. Now I am trying to understand / find a way on how to filter the getRange based on a variable.

Example: If Column C = Todays Date I want to return all rows with Todays date into the HTML Table in the Email. I have been playing with the GetRange but when adjusting it something other then a Range it breaks. How should I go about doing this?

function sendEmail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  var recipient = 'email@gmail.com'
  var subject = 'Subject'
  var date = Utilities.formatDate(new Date(), "GMT+1", "dd/MM/yyyy")
  var schedRange = sheet.getRange("A1:L21"); // Trying to understand 
  //var schedRange = sheet.getRange(Col == 3 && compare == date);

  // Put Name & Date into email first.
  // 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>'+ 'Deployment Table Header ' +'</H1>';
  body += '<H2>'
  body += getHtmlTable(schedRange);
  body += '</div>';
  debugger;

  recipient = 'email@gmail.com';  // For debugging, send only to self
  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();

  // Read table contents
  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));
  }

  // Build HTML Table, with inline styling for each cell
  var tableFormat = 'style="font-size: 10px; border:1px solid black;border-collapse:collapse;text-align:center" border = 1 cellpadding = 1';
  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(),
                     'M/d');
      }
      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('');
}

Why don't you simply add your condition in the function, in the loop that adds the row, so that it skips the row if the condition isn't met — Liora Haydont, Apr 25 '18 at 20:25

score 1 · Answer 1 · answered Apr 26 '18 at 02:40

As stated in the comments, you can filter out any rows you don't want when you build your HTML table:

function getHtmlTable(range){

// ... your code...
  var today = new Date();
  for (row=0;row<data.length;row++) {
    var row_date = data[row][2]; // Assuming date is in 3rd column
    if(sameDay(new Date(row_date), today){ // See note
      html.push('<tr height="'+rowHeights[row]+'">');
      for (col=0;col<data[row].length;col++) {

        // ... your code ...
      }
    }
  }
}

function sameDay(d1, d2) {
  return d1.getFullYear() === d2.getFullYear() &&
    d1.getMonth() === d2.getMonth() &&
    d1.getDate() === d2.getDate();
}

A few notes:

Calling new Date(row_date) will only work if the date is formatted correctly for the Date constructor. If it is not, you need to parse the date before creating the Date object.
sameDay function borrowed from this answer.

Formatting looks correct - Attempted adding your content but I received a Error t"TypeError: Cannot read property "length" from undefined. (line 49, file "Code")"function getHtmlTable(range){ for (row=0;row — Evan Quinn, Apr 30 '18 at 14:57
`data` is a variable you defined in your `getHtmlTable` function. The error seems to indicate that `data` is undefined. Make sure you are passing in a valid range since you define `var data = range.getValues();` — ryan.d.williams, May 01 '18 at 00:12

Google Sheets Script: email with a HTML Table that only has a set of

1 Answers1

Linked