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>