Tips for Emphasizing a Row in a Table Using a Specific Value

Question

Tips for Emphasizing a Row in a Table Using a Specific Value

Currently, I am engaged in creating an educational YouTube tutorial that delves into Google App Script and Google Sheets.

I have been attempting various methods to highlight a row containing the word "ABSENT", but all my endeavors have proven to be unsuccessful.

If anyone could offer guidance on how to modify the code successfully, it would be greatly appreciated.

Important: A newer version of the code has been released for improved comprehension.

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/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/[email protected]/dist/umd/popper.min.js" integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/[email protected]/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>

javascript google-apps-script google-sheets

Answer 1

Answer №1

It seems like the value inside dataArray[i][j] might be where the word "Absent" could be located.

If this is the case, you are essentially checking if one string ("Absent") exists within another string (dataArray[i][j])

This is where the use of the search() method comes in handy. https://www.w3schools.com/jsref/jsref_search.asp

The following code snippet can help achieve this:

if (dataArray[i][j].search("ABSENT") > -1){
  /*change color to red or any other desired action*/
}

Answer 2

It seems like the value inside dataArray[i][j] might be where the word "Absent" could be located.

If this is the case, you are essentially checking if one string ("Absent") exists within another string (dataArray[i][j])

This is where the use of the search() method comes in handy. https://www.w3schools.com/jsref/jsref_search.asp

The following code snippet can help achieve this:

if (dataArray[i][j].search("ABSENT") > -1){
  /*change color to red or any other desired action*/
}

Answer 3

Answer №2

It appears that the provided code is unrelated to Google Spreadsheet and instead seems to be JavaScript code designed to create an HTML table for a web browser.

However, how exactly will this HTML table be transferred to Google Spreadsheet? Will it be done manually through copy and paste?

These tasks may prove to be quite different:

-- Changing the cell colors in the original HTML table (though these colors may not carry over when pasted into Google Spreadsheet via clipboard).

-- Changing the cell colors in a Google Spreadsheet table (but first, the table needs to be transferred there somehow, right?).

Answer 4

It appears that the provided code is unrelated to Google Spreadsheet and instead seems to be JavaScript code designed to create an HTML table for a web browser.

However, how exactly will this HTML table be transferred to Google Spreadsheet? Will it be done manually through copy and paste?

These tasks may prove to be quite different:

-- Changing the cell colors in the original HTML table (though these colors may not carry over when pasted into Google Spreadsheet via clipboard).

-- Changing the cell colors in a Google Spreadsheet table (but first, the table needs to be transferred there somehow, right?).

Answer 5

Answer №3

It is my understanding of your objective.

You aim to change the background color of a row when it contains the value ABSENT.

In this scenario, have you considered checking if each row includes the value ABSENT? By incorporating this into your script, it would look like this:

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, rows with the value ABSENT will have a red background color. Feel free to adjust the script if you wish to change the color.

Note:

If you prefer to set the background color only for specific cells rather than the entire row, you can make the following adjustments:

  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:

Based on your last comment,

@Tanaike I apologize for the confusion earlier. After placing your code correctly, the login and table are displaying properly, but the row highlighting isn't working. Here's the code you 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 appears that you are testing the script using the value Leave. However, in your initial query, the value specified was ABSENT. If you intend to switch to using Leave, you can modify the script as follows, considering that toUpperCase() converts characters to uppercase:

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>";
}

Answer 6

It is my understanding of your objective.

You aim to change the background color of a row when it contains the value ABSENT.

In this scenario, have you considered checking if each row includes the value ABSENT? By incorporating this into your script, it would look like this:

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, rows with the value ABSENT will have a red background color. Feel free to adjust the script if you wish to change the color.

Note:

If you prefer to set the background color only for specific cells rather than the entire row, you can make the following adjustments:

  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:

Based on your last comment,

@Tanaike I apologize for the confusion earlier. After placing your code correctly, the login and table are displaying properly, but the row highlighting isn't working. Here's the code you 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 appears that you are testing the script using the value Leave. However, in your initial query, the value specified was ABSENT. If you intend to switch to using Leave, you can modify the script as follows, considering that toUpperCase() converts characters to uppercase:

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>";
}

Tips for Emphasizing a Row in a Table Using a Specific Value

Answer №1

Answer №2

Answer №3

From:

To:

Note:

Added:

From:

To:

Similar questions

The error message "Cannot construct apickli.Apickli" is indicating a Type Error

Persist user input even after reloading the page

Is jQuery still recommended for adding animations in VueJS?

Implementing HTML page authentication with Identity ADFS URL through JavaScript

Customize your popover content with Bootstrap settings

Displaying random divs and subsequently animating them downwards using JavaScript

What is the best way to send user input text to a Vue method using v-on:change?

Display the Material UI Switch in an active state even when the "checked" value is set to false

d3.json is unable to parse a value of 'Infinity

Load charts.js synchronously into a div using XMLHttpRequest

Stop the background from scrolling and prevent auto-jumping to the top on mobile devices

Auto-complete feature not populating the input field in Google Chrome

The UI Bootstrap Datepicker requires a second click in order to update the month

Javascript 'break' statement is always executed

"Adding an Image to Another Image in HTML or JavaScript: A Step-by-Step

Encountered an error while web crawling in JavaScript: Error - Connection timeout

Leveraging an external TypeScript library in a TypeScript internal module

The utilization of useState can potentially trigger an endless loop

What is the best way to retrieve localstorage information within the getStaticProps function in next.js?

Cloud Foundry deployment faces startup issues