Home > Back-end >  Google Spread Sheet Search any part of the cell and display
Google Spread Sheet Search any part of the cell and display

Time:10-01

I have a code to search and display the exact field in any column.

I would like to get the code of partial search, such that the input value if we entre is partial, should display all the possible fields.

Eg. Search "Evaluation" in the search field should display all the possible results. enter image description here

function doGet() {
  return HtmlService.createTemplateFromFile('Index').evaluate();
}


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

//SEARCH FOR MATCHED CONTENTS 
function search(searchtext){
  var spreadsheetId   = '1iG30kufq5MQpd0xmoPhPir5iA3hlz8s7vI_0EWQfg7Q'; //** CHANGE !!!
  var dataRage        = 'Data!A2:Y';                                    //** CHANGE !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
  var ar = [];
  
  data.forEach(function(f) {
    if (~f.indexOf(searchtext)) {
      ar.push(f);
    }
  });
  return ar;
}

HTML CODE

<!DOCTYPE html>
<html>
    <head>
        <base target="_top">
        <link href="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-ggOyR0iXCbMQv3Xipma34MD dH/1fQ784/j6cY/iJTQUOhcWr7x9JvoRxT2MZw1T" crossorigin="anonymous">
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.min.js" integrity="sha384-JjSmVgyd0p3pXB1rRibZUAYoIIy6OrQ6VrjIEaFf/nJGzIxFDsf4x0xIM B07jRM" crossorigin="anonymous"></script>
        <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN 8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" 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) {
            google.script.run.withSuccessHandler(createTable).processForm(formObject);
            document.getElementById("search-form").reset();
          }
        
          //CREATE THE DATA TABLE
          function createTable(dataArray) {
            if(dataArray && dataArray !== undefined && dataArray.length != 0){
              var result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>" 
                           "<thead style='white-space: nowrap'>" 
                             "<tr>"                                //Change table headings to match witht he Google Sheet
                              "<th scope='col'>MS No</th>" 
                              "<th scope='col'>Title</th>" 
                              "<th scope='col'>Status</th>" 
                              "<th scope='col'>Date</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 ---------------------------------------------------- -->
        
    </head>
    <body>
          <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <label for="searchtext">Search MS No</label>
                    </div>
                    <div class="form-group mx-sm-3 mb-2">
                      <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Enter MS No">
                    </div>
                    <button type="submit" class="btn btn-primary mb-2">Search</button>
                  </form>
                  <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
                <div id="search-results" class="table-responsive">
                  <!-- The Data Table is inserted here by JavaScript -->
                </div>
                <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
                  
              </div>
            </div>
        </div>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## SEARCH FORM ------------------------------------------------ -->
                  <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">
                    <div class="form-group mb-2">
                      <label for="searchtext">Search Title</label>
                    </div>
                    <div class="form-group mx-sm-3 mb-2">
                      <input type="text" class="form-control" id="searchtext" name="searchtext" placeholder="Enter Title">
                    </div>
                    <button type="submit" class="btn btn-primary mb-2">Search</button>
                  </form>
                  <!-- ## SEARCH FORM ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## TABLE OF SEARCH RESULTS ------------------------------------------------ -->
                <div id="search-results" class="table-responsive">
                  <!-- The Data Table is inserted here by JavaScript -->
                </div>
                <!-- ## TABLE OF SEARCH RESULTS ~ END ------------------------------------------------ -->
                  
              </div>
            </div>
        </div>
    </body>
</html>

CodePudding user response:

This will highlight partial substring matches in red:

function searchhilight() {
  let hilite = SpreadsheetApp.newTextStyle().setBold(true).setForegroundColor('red').build();
  let normal = SpreadsheetApp.newTextStyle().setBold(false).setForegroundColor('black').build();
  const ui = SpreadsheetApp.getUi();
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  const sr = 2;//data start row
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr   1, sh.getLastColumn());
  rg.setFontColor('black').setFontWeight('normal');//set all data to black text with no bold
  const dvs = rg.getDisplayValues();
  const resp = ui.prompt('Search Text', 'Enter Search String', ui.ButtonSet.OK_CANCEL);
  if (resp.getSelectedButton() == ui.Button.OK) {
    const st = resp.getResponseText();
    dvs.forEach((r, i) => {
      r.forEach((c, j) => {
        let idx = '';
        fidx = '';//from index
        iA = [];//index array of matches
        do {
          idx = c.indexOf(st, fidx);
          if (~idx) {
            fidx = idx   st.length;
            iA.push({ idxs: idx, idxe: idx   st.length })
          }
        } while (~idx);
        let rtv = SpreadsheetApp.newRichTextValue().setText(c);
        iA.forEach(obj => rtv.setTextStyle(obj.idxs, obj.idxe, hilite));
        sh.getRange(i   sr, j   1).setNumberFormat('@STRING@');
        sh.getRange(i   sr, j   1).setRichTextValue(rtv.build());
      });
    });
  }
}

This function will also reformat every cell in the data range to plain text.

RichTextValueBuilder

RichTextValue

CodePudding user response:

function search(searchtext){
  var spreadsheetId   = '1do6G8pScBAz0Z7GOCjbBcXPKJkhbOwlSx5BZyt6S7H8'; //** CHANGE !!!
  var dataRage        = 'Data!A2:Y';                                    //** CHANGE !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRage).values;
  var ar = [];
  
  data.forEach(function(f) {
    if (~f.toString().toLowerCase().indexOf(searchtext.toString().toLowerCase())) {
      ar.push(f);
    }
  });
  return ar;
}
  • Related