So my boss sent me this spreadsheet with an Apps Script extension (I looked it up and it says JavaScript) I am no coder. It's supposed to auto send spreadsheet in email and show table (this part is working just fine).
My boss is trying to ask me if I could look at the html part and maybe I could change the "DONE" part of the text into green instead of black.
This is how it looks now
code.gs
var config = {
// Google Sheet configuration
googleSheet: "https://docs.google.com/spreadsheets/d/asdfghjkl/edit", // ID or Sheet URL
sheetName: "email-schedule",
tableRange: "B4:G9", // Named Range OR Range i.e A6:B9
// change backgroud color of table header row
bgColor: "lightblue",
// Email Configuration
toEmail: "[email protected]",
cc: "", // comma separated email ids.
subject: "Table from GoogleSheet",
// Body text table
body: `Hello,
I am xxx.
Link to file: xxx`,
// Footer Signature after table
signature: `Thank you,
xxx`,
};
var isDate = function(date) {
return (date instanceof Date);
}
function sendEmail() {
// Function to send Email
// get table data
var tableData = getData();
// get email template
var htmlTemplate = getEmailTemplate(tableData);
// Send Email
MailApp.sendEmail({
to: config.toEmail,
subject: config.subject,
cc: config.cc,
htmlBody: htmlTemplate
});
Logger.log(`Email send successfully to '${config.toEmail}'.`);
}
function getEmailTemplate(tableData) {
// Function to Get Template for Email
// Create GMail Template
const htmlTemplate = HtmlService.createTemplateFromFile('emailTemplate');
htmlTemplate.body = config.body;
htmlTemplate.headers = tableData.headers;
htmlTemplate.rows = tableData.rows;
htmlTemplate.footer = config.signature;
htmlTemplate.bgColor = config.bgColor;
const htmlForEmail = htmlTemplate.evaluate().getContent();
// Logger.log(htmlForEmail);
return htmlForEmail;
}
function getData() {
// Function to Get Data
// Open Sheet
var gsheet;
if (config.googleSheet.includes("docs.google.com")) {
gsheet = SpreadsheetApp.openByUrl(config.googleSheet);
}
else {
gsheet = SpreadsheetApp.openById(config.googleSheet)
}
Logger.log("Your Google Sheet Name: " gsheet.getName());
Logger.log("Your Google Sheet URL: " gsheet.getUrl());
// var sheet = gsheet.getSheets()[0]; // Select by index
var sheet = gsheet.getSheetByName(config.sheetName); // select by name
Logger.log("Selected Sheet Name: " sheet.getName())
// Get Table
var values = sheet.getRange(config.tableRange).getValues();
var rows = values.slice(1);
// Parse Data
for (var i=0; i < rows.length; i ) {
for (var j=0; j < rows[i].length; j ) {
const cell = rows[i][j];
if (isDate(cell)) {
Logger.log(cell);
const options = {month: 'short' };
rows[i][j] = `${cell.getDate()}-${cell.toLocaleString(undefined, options)}-${cell.getFullYear()}`;
}
}
}
// Store Data
var tableData = {
headers: values[0],
rows: rows
};
// Logger.log(JSON.stringify(values));
Logger.log(JSON.stringify(tableData));
return tableData;
}
this is the html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<style>
* {
font-family: Verdana sans-serif;
font-size: 12pt;
}
table {
border-collapse: collapse;
}
table, th, td {
border: 1px solid black;
font-size:15px;
padding: 8px;
color: black;
}
tr:hover {background-color: #ddd;}
th, thead {
background-color: <?= bgColor ?>;
}
.table-container, .footer {
margin-top: 10px;
}
</style>
</head>
<body>
<!-- Body -->
<div>
<pre><?= body ?></pre>
</div>
<!-- Table -->
<div >
<table>
<thead>
<? headers.forEach(cell => {?>
<th><?= cell ?></th>
<?})?>
</thead>
<tbody >
<? rows.forEach(row => {?>
<tr>
<? row.forEach(cell => {?>
<td style="text-align:center"><?= cell ?></td>
<?})?>
</tr>
<?})?>
</tbody>
</table>
</div>
<!-- Footer -->
<div >
<pre><?= footer ?></pre>
</div>
</body>
</html>
I tried changing the color:black
into color:green
but it changes the whole texts' color.
table, th, td {
border: 1px solid black;
font-size:15px;
padding: 8px;
color: green;
Wanted it to maybe look like if it's "DONE" then color should be "green". Wanted it to maybe look like this
CodePudding user response:
Try replacing this line in Code.gs
:
var rows = values.slice(1);
...with:
var rows = values.slice(1).map(row => row.map(value => value === 'Done' ? '<span style="color:green;">Done</span>' : value));;
And this line in emailTemplate.html
:
<td style="text-align:center"><?= cell ?></td>
...with:
<td style="text-align:center"><?!= cell ?></td>