How to change text color in a cell-CodePudding

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>