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

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:

CodePudding user response：

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,

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

}

HTML:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    <table>

      <thead >
        <tr >
          <th ><?= sr ?></th><th><?= pName ?></th><th><?= price ?></th>
        </tr>
      </thead>

      <tbody>
     
          <? values.forEach(x => { ?> 
          <tr >
            <td ><?= x[0] ?></td><td><?= x[1] ?></td><td><?= x[2] ?></td>
          </tr>
          <? }) ?>
          
        </tr>
      </tbody>

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