Apps Script合并变量值发送格式化邮件问题求助

问题描述 #

我希望使用Apps Script发送一封包含格式化数据列表（datalist1、datalist2、datalist3）的邮件，要求数据列表呈现指定格式。但将三个数据列表通过push合并到一个数组后发送邮件时，仅datalist1格式正常，其余数据格式失效。

原代码 #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetName');

  var [_, ...data] = sheet.getDataRange().getValues();
  var arr1 = [];
  var arr2 = [];
  var arr3 = [];
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] == 'First') {
      arr1.push([data[i][1], data[i][2], data[i][0], data[i][3]]);
    }
    if (data[i][0] == 'Second') {
      arr2.push([data[i][1], data[i][2], data[i][0], data[i][3]]);
    }
    if (data[i][0] == 'Third') {
      arr3.push([data[i][1], data[i][2], data[i][0], data[i][3]]);
    }
  }

  var merge = [];
  var datalist1;
  var datalist2;
  var datalist3;

  for (var y = 0; y < arr1.length; y++) {
    if (arr1[y][2] == 'First') {
      if (y == 0) {
        datalist1 = "First" + "\n" + "- " + arr1[y];
      } else {
        datalist1 = datalist1 + "\n" + "- " + arr1[y]
      }

    }
  }

  for (var y = 0; y < arr2.length; y++) {
    if (arr2[y][2] == 'Second') {
      if (y == 0) {
        datalist2 = "Second" + "\n" + "- " + arr2[y];
      } else {
        datalist2 = datalist2 + "\n" + "- " + arr2[y]
      }

    }
  }

  for (var y = 0; y < arr3.length; y++) {
    if (arr3[y][2] == 'Third') {
      if (y == 0) {
        datalist3 = "Third" + "\n" + "- " + arr3[y];
      } else {
        datalist3 = datalist3 + "\n" + "- " + arr3[y]
      }

    }
  }
  console.log(datalist1);
  console.log(datalist2);
  console.log(datalist3);


  merge.push(datalist1, datalist2, datalist3);
  //merge.sort();
  console.log(merge);
  var email = '@gmail.com'
  var Subject = "data";
  var Message = "Hello, \n" + "\n" +
    "This is a data message to let you know\n" + "\n" +
    "Here is the list of data: \n" + datalist1 + 
    "\n" + datalist2 + "\n" + datalist3 + "\n";

  MailApp.sendEmail(email, Subject, Message);
}

问题原因 #

1. 变量未初始化：datalist1、datalist2、datalist3未初始化为空字符串，若对应的arr1/arr2/arr3为空数组，变量会是undefined，直接拼接会破坏邮件格式。
2. 冗余判断逻辑：循环中if (arr1[y][2] == 'First')完全多余——arr1本身就是筛选出data[i][0] == 'First'的数据，这一步判断不仅无效，还可能导致当数组元素不符合预期时（虽然逻辑上不可能），datalist变量无法赋值，出现undefined。
3. 数组隐式转换问题：直接拼接数组arr1[y]时，JS会自动调用toString()将数组元素用逗号分隔，但如果后续拼接时出现空值或未定义变量，会导致格式错乱。

解决方案 #

方案1：修复现有代码 #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetName');

  var [_, ...data] = sheet.getDataRange().getValues();
  var arr1 = [];
  var arr2 = [];
  var arr3 = [];
  
  // 筛选数据（保留原逻辑）
  for (var i = 0; i < data.length; i++) {
    const row = data[i];
    if (row[0] === 'First') {
      arr1.push([row[1], row[2], row[0], row[3]]);
    } else if (row[0] === 'Second') {
      arr2.push([row[1], row[2], row[0], row[3]]);
    } else if (row[0] === 'Third') {
      arr3.push([row[1], row[2], row[0], row[3]]);
    }
  }

  // 初始化datalist为空字符串，避免undefined
  let datalist1 = '';
  let datalist2 = '';
  let datalist3 = '';

  // 生成datalist1，去掉冗余判断
  if (arr1.length > 0) {
    datalist1 = "First\n";
    arr1.forEach(item => {
      datalist1 += "- " + item.join(' ') + "\n"; // 用join指定元素分隔符，避免默认逗号
    });
  }

  // 生成datalist2
  if (arr2.length > 0) {
    datalist2 = "Second\n";
    arr2.forEach(item => {
      datalist2 += "- " + item.join(' ') + "\n";
    });
  }

  // 生成datalist3
  if (arr3.length > 0) {
    datalist3 = "Third\n";
    arr3.forEach(item => {
      datalist3 += "- " + item.join(' ') + "\n";
    });
  }

  // 合并有效列表，过滤空字符串
  const validLists = [datalist1, datalist2, datalist3].filter(list => list);
  const Message = "Hello, \n\n" +
    "This is a data message to let you know\n\n" +
    "Here is the list of data: \n" + validLists.join("\n");

  var email = '@gmail.com';
  var Subject = "data";
  MailApp.sendEmail(email, Subject, Message);
}

方案2：优化分组与格式化逻辑（更简洁） #

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SheetName');
  var [_, ...data] = sheet.getDataRange().getValues();

  // 用reduce分组数据，更高效
  const groupedData = data.reduce((acc, row) => {
    const key = row[0];
    if (['First', 'Second', 'Third'].includes(key)) {
      if (!acc[key]) acc[key] = [];
      acc[key].push([row[1], row[2], row[0], row[3]]);
    }
    return acc;
  }, {});

  // 生成格式化的邮件内容
  let content = "Hello, \n\nThis is a data message to let you know\n\nHere is the list of data: \n";
  Object.keys(groupedData).forEach(key => {
    content += `\n${key}\n`;
    groupedData[key].forEach(item => {
      content += `- ${item.join(' ')}\n`;
    });
  });

  var email = '@gmail.com';
  var Subject = "data";
  MailApp.sendEmail(email, Subject, content);
}

关键改进点 #

• 初始化变量为空字符串，避免undefined干扰格式
• 用join()手动指定数组元素的分隔符（比如空格），替代默认的逗号分隔
• 过滤空列表，避免邮件中出现多余的空行
• 优化分组逻辑，减少冗余代码，提升可维护性

内容的提问来源于stack exchange，提问作者Asking Bob