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?
| Bank's Email | Client name | Age | Bank with |
|---|---|---|---|
| bank1@hotmail.com | Client 1 | 19 | Bank 1 |
| bank1@hotmail.com | Client 2 | 32 | Bank 1 |
| bank1@hotmail.com | Client 3 | 22 | Bank 1 |
| bank2@hotmail.com | Client 4 | 34 | Bank 2 |
| bank2@hotmail.com | Client 5 | 22 | Bank 2 |
| bank1@hotmail.com | Client 5 | 28 | Bank 3 |
For example, send the below table as gmail to bank 1's email ?
| Client name | Age | Bank with |
|---|---|---|
| Client 1 | 19 | Bank 1 |
| Client 2 | 32 | Bank 1 |
| Client 3 | 22 | Bank 1 |
and below to bank 2's email (same with bank 3)?
| Client name | Age | Bank with |
|---|---|---|
| Client 4 | 34 | Bank 2 |
| Client 5 | 22 | Bank 2 |
Thanks!
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: "thewized@gmail.com",
subject: "Test",
htmlBody: table });
}
catch(err) {
console.log(err);
}
}