如何在Google Apps Script邮件HTML表格中格式化日期与货币

核心原因 #

修改工作表单元格格式仅改变界面显示，不会影响通过getValues()等方法提取到dataGrid中的原始数据——日期存储为Date对象或时间戳，数字存储为纯数值，因此需要在生成HTML表格的环节对目标列的数据单独格式化。

解决方案 #

1. 编写格式化工具函数 #

先定义两个专用函数，分别处理日期和货币格式：

// 格式化日期为mm-dd-yyyy格式
function formatDate(date) {
  // 非日期类型直接返回原值
  if (!(date instanceof Date) || isNaN(date.getTime())) return date;
  const month = String(date.getMonth() + 1).padStart(2, '0');
  const day = String(date.getDate()).padStart(2, '0');
  const year = date.getFullYear();
  return `${month}-${day}-${year}`;
}

// 格式化数值为货币格式（以USD为例，可修改currency参数）
function formatCurrency(amount) {
  // 非数值类型直接返回原值
  if (isNaN(parseFloat(amount))) return amount;
  return new Intl.NumberFormat('en-US', {
    style: 'currency',
    currency: 'USD' // 如需人民币改为'CNY'，欧元改为'EUR'
  }).format(amount);
}

2. 在生成HTML表格时应用格式 #

在循环构建HTML表格的过程中，针对目标列（索引6、9、11、12）调用格式化函数，替换原始值：

function generateEmailHtml(dataGrid) {
  let htmlTable = '<table border="1" cellpadding="4" cellspacing="0">';
  
  // 先添加表头（假设dataGrid第一行是表头）
  htmlTable += '<tr>';
  dataGrid[0].forEach(header => {
    htmlTable += `<th>${header}</th>`;
  });
  htmlTable += '</tr>';
  
  // 处理数据行
  for (let i = 1; i < dataGrid.length; i++) { // 从第二行开始是数据
    htmlTable += '<tr>';
    for (let j = 0; j < dataGrid[i].length; j++) {
      let cellContent = dataGrid[i][j];
      // 处理第7列（索引6）为货币
      if (j === 6) {
        cellContent = formatCurrency(cellContent);
      }
      // 处理第10、12、13列（索引9、11、12）为日期
      else if ([9, 11, 12].includes(j)) {
        cellContent = formatDate(cellContent);
      }
      htmlTable += `<td>${cellContent}</td>`;
    }
    htmlTable += '</tr>';
  }
  
  htmlTable += '</table>';
  return htmlTable;
}

3. 调用示例 #

假设你的主函数是读取工作表数据并发送邮件，修改后大概是这样：

function sendFormattedEmail() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('你的工作表名称');
  const dataGrid = sheet.getDataRange().getValues(); // 获取所有数据
  const htmlBody = generateEmailHtml(dataGrid);
  
  // 发送邮件
  MailApp.sendEmail({
    to: '收件人邮箱',
    subject: '格式化表格邮件',
    htmlBody: htmlBody
  });
}

注意事项 #

• 如果dataGrid中的日期是字符串而非Date对象，需要先转换：new Date(dateString)，确保日期能被正确解析。
• 货币格式化的区域和代码可根据需求调整，比如中文环境用zh-CN和CNY。
• 函数中加入了非目标类型的判断，避免空值或非预期数据导致报错。

内容的提问来源于stack exchange，提问作者j. prufrock