Skip to content

How to Highlight row based on particular value in the table?

I’m working on a YouTube tutorial that works on Google App Script and Google Sheets

I want to highlight the row if it contains the value “ABSENT”, I tried many ways to but ended in failures,

Need some assistance to modify this code to do the job

NOTE: Updated the code for better understanding.

CODE.JS

function doGet(e) {
  
  return HtmlService.createTemplateFromFile("Index").evaluate()
  .setTitle("WebApp: Search By Password")
  .addMetaTag('viewport', 'width=device-width, initial-scale=1')
  .setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}


/* PROCESS FORM */
function processForm(formObject){ 
  var concat = formObject.searchtext+formObject.searchtext2;
  var result = "";
  if(concat){//Execute if form passes search text
      result = search(concat);
  }
  return result;
}

//SEARCH FOR MATCHED CONTENTS ;
function search(searchtext){
  var spreadsheetId   = '1bahNEJIweyuvmocYbSR8Nc_IA_HP3qdO7tCKU6w'; //** CHANGE !!!!
  var sheetName = "Data"
  var range = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName).getDataRange();
  var data = range.getDisplayValues();
  var ar = [];
  
  data.forEach(function(f) {
    if (~[f[8]].indexOf(searchtext)) {
      ar.push([ f[2],f[3],f[4],f[5],f[6],f[7] ]);
    }
  });
                                           
  return ar;
};

INDEX.HMLT

<!DOCTYPE html>
<html>
    <head>
        <base target="_self">
        <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" crossorigin="anonymous">
       
   <style>
   
   /* h5 {background: red;} */
   
   </style>

        
    </head>
    <body>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <center><form id="search-form" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <h5 for="searchtext">Work Log Records</h5>
                    </div><p>
                    <div class="form-group mx-sm-3 mb-3">
                      <input type="email" class="form-control col-sm-6" id="searchtext" name="searchtext" placeholder="Email"  required><br>
                  
                      <input type="text" class="form-control col-sm-6" id="searchtext2" name="searchtext2" placeholder="Employee ID"  required>
                    </div><p>
                    <button type="submit" class="btn btn-primary mb-2" >Generate
                      <span id="resp-spinner5" class="spinner-border spinner-border-sm d-none" role="status" aria-hidden="true"></span> 
                    </button>
                    
                    
                 
                  </form></center>
                  <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
                <div id="search-results" class="table table-responsive ">
                  <!-- The Data Table is inserted here by JavaScript -->
                </div>
                <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
                  
              </div>
            </div>
        </div>
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" integrity="sha384-w1Q4orYjBQndcko6MimVbzY0tgp4pWB4lZ7lr30WKz0vr/aWKhXdBNmNb5D92v7s" crossorigin="anonymous"></script>



        <!--##JAVASCRIPT FUNCTIONS ---------------------------------------------------- -->
        <script>
          //PREVENT FORMS FROM SUBMITTING / PREVENT DEFAULT BEHAVIOUR
          function preventFormSubmit() {
            var forms = document.querySelectorAll('form');
            for (var i = 0; i < forms.length; i++) {
              forms[i].addEventListener('submit', function(event) {
              event.preventDefault();
              });
            }
          }
          window.addEventListener("load", preventFormSubmit, true);
             
          
          
          //HANDLE FORM SUBMISSION
          function handleFormSubmit(formObject) {
           if(document.getElementById('searchtext').value == "" || document.getElementById('searchtext2').value == ""){
              alert("Fill in Email and Employee ID");
           }else{
             document.getElementById('resp-spinner5').classList.remove("d-none");
        
            google.script.run.withSuccessHandler(createTable).processForm(formObject);
            document.getElementById("search-form").reset();
           };
          };
        
          //CREATE THE DATA TABLE
          
          function createTable(dataArray) {
             document.getElementById('resp-spinner5').classList.add("d-none");
   
            if(dataArray && dataArray !== undefined && dataArray.length != 0){
              var result = "<table class='table table-sm table-dark table-hover' id='dtable' style='font-size:0.8em'>"+
                           "<thead style='white-space: nowrap'>"+
                             "<tr >"+                               //Change table headings to match with the Google Sheet
                              
                              "<th scope='col'>EMPLOYEE</th>"+
                              "<th scope='col'>DATE</th>"+
                              "<th scope='col'>IN TIME</th>"+
                              "<th scope='col'>OUT TIME</th>"+
                              "<th scope='col'>HOURS</th>"+
                              "<th scope='col'>STATUS</th>"+
                            "</tr>"+
                          "</thead>";
              for(var i=0; i<dataArray.length; i++) {
                  result += "<tr>";
                  for(var j=0; j<dataArray[i].length; j++){
                      result += "<td>"+dataArray[i][j]+"</td>";
                  }
                  result += "</tr>";
              }
              result += "</table>";
              var div = document.getElementById('search-results');
              div.innerHTML = result;
            }else{
              var div = document.getElementById('search-results');
              //div.empty()
              div.innerHTML = "Data not found!";
            }
          }
        </script>
        <!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->    
    
    </body>
</html>

Answer

I believe your goal as follows.

  • You want to set the background color of the row when the row has the value of ABSENT.

In this case, how about checking whether the value of ABSENT is included in each row? When this is reflected to your script, it becomes as follows.

From:

for(var i=0; i<dataArray.length; i++) {
    result += "<tr>";
    for(var j=0; j<dataArray[i].length; j++){
        result += "<td>"+dataArray[i][j]+"</td>";
    }
    result += "</tr>";
}

To:

for(var i=0; i<dataArray.length; i++) {
  result += dataArray[i].some(c => c.toUpperCase() == "ABSENT") ? '<tr style="background-color:red;">' : "<tr>";
  for(var j=0; j<dataArray[i].length; j++){
    result += "<td>"+dataArray[i][j]+"</td>";
  }
  result += "</tr>";
}
  • In this case, the row which has the value of ABSENT is set as the red background color. If you want to change the color, please modify above script.

Note:

  • If you want to set the background color for only the cells instead of the row, you can also use the following modification.

      for(var i=0; i<dataArray.length; i++) {
        result += "<tr>";
        for(var j=0; j<dataArray[i].length; j++){
          result += (dataArray[i][j].toUpperCase() == "ABSENT" ? '<td style="background-color:red;">' : "<td>") +dataArray[i][j]+"</td>";
        }
        result += "</tr>";
      }
    

Added:

From your following replying,

@Tanaike I’m absolutely sorry, I made mistake by adding your code in wrong place, after placing the correct place, the login and table appears perfectly, but it doesn’t highlight the row. this is the code your provided, ‘for(var i=0; i<dataArray.length; i++) { result += dataArray[i].some(c => c.toUpperCase() == “Leave”) ? ” : “”; for(var j=0; j<dataArray[i].length; j++){ result += “”+dataArray[i][j]+””; }’

It seems that you are testing the script using the value of Leave. In your question, the value is ABSENT. If you want to change the values to Leave, please modify above script as follows. Because toUpperCase() converts the characters to the upper case.

From:

for(var i=0; i<dataArray.length; i++) {
    result += "<tr>";
    for(var j=0; j<dataArray[i].length; j++){
        result += "<td>"+dataArray[i][j]+"</td>";
    }
    result += "</tr>";
}

To:

for(var i=0; i<dataArray.length; i++) {
  result += dataArray[i].some(c => c == "Leave") ? '<tr style="background-color:red;">' : "<tr>";
  for(var j=0; j<dataArray[i].length; j++){
    result += "<td>"+dataArray[i][j]+"</td>";
  }
  result += "</tr>";
}