Is there a way that we can break the main table below into smaller tables and email (gmail) them to the corresponding email on the first column?

For example, send the below table as gmail to bank 1's email ?

and below to bank 2's email (same with bank 3)?

Thanks!

CodePudding user response：

You can try this. I leave it up to you to adjust the styling to suit your taste. I edited this to add the first part. Sorting the clients.

function test() {
  try {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sh = ss.getSheetByName("Sheet2");
    // dont get header
    var data = sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn()).getValues();
    var banks = [];
    data.forEach( row => banks.push(row[0]) );
    banks = [...new Set(banks)];
    var results = [];
    function addClient(row) {
      var i = banks.indexOf(row[0]);
      if( results[i] === undefined ) results[i] = [];
      row.shift();
      results[i].push(row);
    }
    data.forEach( addClient );
    results.forEach( (bank,index) => createTable(banks[index],bank) );
  }
  catch(err) {
    console.log(err);
  }
}

function createTable(email,data) {
  try {
    var table = "<table style='border-width:thin;border-style:solid;border-color:black'>";
    function addCell(value) {
      table = table.concat("<td style='border-width:thin;border-style:solid;border-color:black;width:20px'>");
      table = table.concat(value.toString());
      table = table.concat("</td>");
    }
    function addRow(row) {
      table = table.concat("<tr>");
      row.forEach( addCell );
      table = table.concat("</tr>");
    }
    data.forEach( addRow )
    table = table.concat("</table>")
    console.log(table);
    MailApp.sendEmail( {
      to: "[email protected]",
      subject: "Test",
      htmlBody: table });
  }
  catch(err) {
    console.log(err);
  }
}