Google sheet: On checkbox click, data present in the table should get sent on the selected email address

Question

Below mentioned is my ss link, it is having 'Sheet1' which is having data in it. Whenever the email address is selected from the dropdown (E2) and checkbox is checked to true(F2), the data present in the column A,B and C (A1:C) need to be sent on the selected email address. Note: There may be 1 entry or multiple entries available, need to send all the data that is available at the time of CHECK.

Thanks in advance. SS Link: https://docs.google.com/spreadsheets/d/1tmnDOMyupjeO8d65qHQsxb5KrrKeq7pMYIE8h2VmEJ4/edit?usp=sharing

Answer 1

I believe your goal is as follows.

• You have the email address and checkbox in the cells "E2" and "F2", respectively.
• When the checkbox is checked, you want to retrieve the email address and the values from the columns "A" to "C", and want to send the values as an email.
• You want to convert the values to the HTML table.

In this case, how about the following sample script?

Sample script:

This script is run by the installed OnEdit trigger. So, please install OnEdit trigger to the function installedOnEdit. And, when you want to test this, please check the checkbox of "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();
}

References:

• reduce()
• sendEmail(message)

Added:

About your following 3rd question,

,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

For example, how about the following modification?

Modified scirpt:

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

Answer 2

SUGGESTION:

I did took the long route with using an HTML Template created on the App Script Files Library, but here's mine:

Notes: Instead of using a checkbox, I'd suggest creating a little image button and assign the script to that button on your Google Sheets (if that works for you.) Like so:

SCRIPT:

function sendEmail() {

  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName('Sheet1'); 
  var emailadd = sheet.getRange('E2').getValue(); 
  var header = sheet.getRange('A1:C1').getValues().flat();
  var range = sheet.getRange(2,1, sheet.getLastRow() - 1, 3);

  const sr = header[0];
  const pName = header[1]; 
  const price = header[2];

  var values = range.getDisplayValues();
  const html = HtmlService.createTemplateFromFile('email');

  html.sr = sr; 
  html.pName = pName; 
  html.price = price;
  html.values = values;

  const test = html.evaluate().getContent();
  console.log(test);

  MailApp.sendEmail(
  emailadd, 
  "Set Email Subject Here", 
  "HTML", { htmlBody: test}
  );

}

(EDITED) HTML:

    <!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    
    <table style="border: 1px solid;border-collapse: collapse;">

      <thead>
        <tr style="border: 2px solid;border-collapse: collapse;">
          <th ><?= sr ?></th><th><?= pName ?></th><th><?= price ?></th>
        </tr>
      </thead>

      <tbody >
     
          <? values.forEach(x => { ?> 
          <tr style="border: 2px solid;border-collapse: collapse;">
            <td style="border: 1px solid;border-collapse: collapse;"><?= x[0] ?></td><td style="border: 1px solid;border-collapse: collapse;"><?= x[1] ?></td><td style="border: 1px solid;border-collapse: collapse;"><?= x[2] ?></td>
          </tr>
          <? }) ?>
          
        </tr>
      </tbody>

    </table> 
  </body>
</html>