Google表格脚本:带有HTML表格的电子邮件，该表格只有一组 [英] Google Sheets Script: email with a HTML Table that only has a set of

问题描述

在Google Spreadsheet中，我希望能够在电子邮件中发送带有HTML表格的电子邮件.我已经在下面完成了此操作，现在想进一步扩展功能.现在，我试图理解/找到一种基于变量来过滤getRange的方法.

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.

示例: 如果列C =今天的日期，我想将所有带有今天的日期的行返回到电子邮件的HTML表中.我一直在玩GetRange，但在进行其他调整时会破坏Range.我应该怎么做呢?

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 = '[e-mail]'
  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 = '[e-mail]';  // 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('');
}

推荐答案

如注释中所述，您可以在构建HTML表时过滤掉不需要的任何行:

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();
}

一些注意事项:

1. 仅当new Date(row_date) ="nofollow noreferrer"> Date构造函数.如果不是，则需要解析日期，然后再创建Date对象.
2. sameDay函数是从

1. 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.
2. sameDay function borrowed from this answer.

这篇关于Google表格脚本:带有HTML表格的电子邮件，该表格只有一组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！