时间戳

首页 > 解决方案 > 谷歌电子表格搜索单元格的任何部分并显示

google-apps-script - 谷歌电子表格搜索单元格的任何部分并显示

问题描述

我有一个代码来搜索和显示任何列中的确切字段。

我想获取部分搜索的代码，这样如果我们输入的输入值是部分的，应该显示所有可能的字段。

例如。在搜索字段中搜索“评估”应显示所有可能的结果。 在此处输入图像描述

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

代码

<!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>

标签： google-apps-scriptgoogle-sheets

解决方案

这将以红色突出显示部分子字符串匹配：

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());
      });
    });
  }
}

此函数还将数据范围中的每个单元格重新格式化为纯文本。

富文本值生成器

富文本值

推荐阅读