google-apps-script - Google 工作表 : On checkbox click, 表格中的数据应该发送到选定的电子邮件地址

Question

下面提到的是我的 ss 链接，它有“Sheet1”，里面有数据。每当从下拉列表 (E2) 中选择电子邮件地址并将复选框选中为真 (F2) 时，A、B 和 C 列 (A1:C) 中存在的数据需要发送到所选电子邮件地址。注意:可能有1个条目或多个条目可用，需要发送CHECK时可用的所有数据。

提前致谢。SS 链接:https://docs.google.com/spreadsheets/d/1tmnDOMyupjeO8d65qHQsxb5KrrKeq7pMYIE8h2VmEJ4/edit?usp=sharing

Answer 1

我相信你的目标如下。

• 您在单元格“E2”和“F2”中分别有电子邮件地址和复选框。
• 选中该复选框后，您想要检索电子邮件地址和“A”到“C”列中的值，并希望将这些值作为电子邮件发送。
• 您想将值转换为 HTML 表格。

在这种情况下，下面的示例脚本怎么样？

示例脚本:

此脚本由已安装的 OnEdit 触发器运行。所以，please install OnEdit trigger to the function installedOnEdit。并且，当你想测试这个时，请选中“F2”的复选框。

function installedOnEdit(e) {
  const range = e.range;
  const sheet = range.getSheet();
  const [email, checkbox] = sheet.getRange("E2:F2").getValues()[0];
  if (sheet.getSheetName() != "Sheet1" || !checkbox) return;
  const values = sheet.getRange("A1:C" + sheet.getLastRow()).getValues();
  const html = '<table border="1" style="border-collapse: collapse">' + values.reduce((s, r) => s += "<tr>" + r.map(c => `<td>${c}</td>`).join("") + "</tr>", "") + "</table>";
  MailApp.sendEmail({ to: email, subject: "sample subject", htmlBody: html});
  range.uncheck();
}

引用资料:

• reduce()
• sendEmail(message)

添加:

关于您接下来的第三个问题，

,the given SS link may not work now, but is there any work around that only the occupied cells get sent on mail. With the above code, entire column including the black cells are getting sent on the email.

With the above code, all the data present in the range "A1:C" are being sent on the mail with the borders. But I want the table to be sent on mail only till the last data row available. This script is sending the the entire available rows i.e. A1:C1000 with borders. For e.g. If the data is available till the 3rd row i.e. A1: C3, i want only that data to be sent, instead this code is sending the entire available rows i.e. A1:C1000

比如下面的修改怎么样？

修改后的脚本:

function installedOnEdit(e) {
  // Ref: https://stackoverflow.com/a/44563639
  Object.prototype.get1stEmptyRowFromTop = function (columnNumber, offsetRow = 1) {
    const range = this.getRange(offsetRow, columnNumber, 2);
    const values = range.getDisplayValues();
    if (values[0][0] && values[1][0]) {
      return range.getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow() + 1;
    } else if (values[0][0] && !values[1][0]) {
      return offsetRow + 1;
    }
    return offsetRow;
  };

  const range = e.range;
  const sheet = range.getSheet();
  const [email, checkbox] = sheet.getRange("E2:F2").getValues()[0];
  if (sheet.getSheetName() != "Sheet1" || !checkbox) return;
  const values = sheet.getRange("A1:C" + sheet.get1stEmptyRowFromTop(1)).getValues();
  const html = '<table border="1" style="border-collapse: collapse">' + values.reduce((s, r) => s += "<tr>" + r.map(c => `<td>${c}</td>`).join("") + "</tr>", "") + "</table>";
  MailApp.sendEmail({ to: email, subject: "sample subject", htmlBody: html});
  range.uncheck();

  // This is from your 2nd question.
  var dstSheet = e.source.getSheetByName("History");
  dstSheet.getRange(dstSheet.getLastRow() + 1, 1, values.length, values[0].length).setValues(values);
}