Google表格单元格部分匹配搜索实现咨询 现有精确搜索代码需改造

核心修改说明 #

• 将原本的单元格完全精确匹配改为内容包含匹配，支持输入部分关键词即可返回对应结果，同时默认不区分大小写
• 修复原HTML代码中ID重复导致的功能异常问题，两个搜索框可独立工作互不干扰

修改后的Google Apps Script代码 #

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

/* 处理表单提交 */
function processForm(formObject){  
  var result = "";
  if(formObject.searchtext){// 存在搜索关键词时执行搜索
      result = search(formObject.searchtext);
  }
  return result;
}

// 模糊搜索匹配内容
function search(searchtext){
  var spreadsheetId   = '1iG30kufq5MQpd0xmoPhPir5iA3hlz8s7vI_0EWQfg7Q'; //** 请替换为你自己的表格ID !!!
  var dataRange        = 'Data!A2:Y';                                    //** 请替换为你自己的数据范围 !!!
  var data = Sheets.Spreadsheets.Values.get(spreadsheetId, dataRange).values;
  var ar = [];
  // 统一转小写实现不区分大小写匹配，不需要的话可以去掉toLowerCase()
  const searchKeyword = searchtext.toLowerCase();
  
  data.forEach(function(row) {
    // 遍历行内所有单元格，只要有一个单元格包含搜索关键词就命中
    const isMatch = row.some(cell => {
      // 处理空单元格的情况
      if (!cell) return false;
      return cell.toString().toLowerCase().includes(searchKeyword);
    });
    if (isMatch) {
      ar.push(row);
    }
  });
  return ar;
}

修改后的HTML代码 #

<!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>
          //阻止表单默认提交行为
          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); 
              
          
          //处理表单提交，新增参数区分不同搜索框对应的结果容器
          function handleFormSubmit(formObject, resultContainerId, formId) {
            google.script.run.withSuccessHandler((data) => createTable(data, resultContainerId)).processForm(formObject);
            document.getElementById(formId).reset();
          }
        
          //生成数据表格，新增参数指定结果渲染容器
          function createTable(dataArray, resultContainerId) {
            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>"+                               //可自行修改表头匹配你的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(resultContainerId);
              div.innerHTML = result;
            }else{
              var div = document.getElementById(resultContainerId);
              div.innerHTML = "未找到匹配数据！";
            }
          }
        </script>
        <!--##JAVASCRIPT FUNCTIONS ~ END ---------------------------------------------------- -->
        
    </head>
    <body>
          <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## MS编号搜索表单 ------------------------------------------------ -->
                  <form id="search-form-ms" class="form-inline" onsubmit="handleFormSubmit(this, 'search-results-ms', 'search-form-ms')">
                    <div class="form-group mb-2">
                      <label for="searchtext-ms">搜索MS编号</label>
                    </div>
                    <div class="form-group mx-sm-3 mb-2">
                      <input type="text" class="form-control" id="searchtext-ms" name="searchtext" placeholder="输入MS编号">
                    </div>
                    <button type="submit" class="btn btn-primary mb-2">搜索</button>
                  </form>
                  <!-- ## MS编号搜索表单 ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## MS编号搜索结果区 ------------------------------------------------ -->
                <div id="search-results-ms" class="table-responsive">
                  <!-- 搜索结果会自动插入此处 -->
                </div>
                <!-- ## MS编号搜索结果区 ~ END ------------------------------------------------ -->
                  
              </div>
            </div>
        </div>
        <div class="container">
            <br>
            <div class="row">
              <div class="col">
            
                  <!-- ## 标题搜索表单 ------------------------------------------------ -->
                  <form id="search-form-title" class="form-inline" onsubmit="handleFormSubmit(this, 'search-results-title', 'search-form-title')">
                    <div class="form-group mb-2">
                      <label for="searchtext-title">搜索标题</label>
                    </div>
                    <div class="form-group mx-sm-3 mb-2">
                      <input type="text" class="form-control" id="searchtext-title" name="searchtext" placeholder="输入标题关键词">
                    </div>
                    <button type="submit" class="btn btn-primary mb-2">搜索</button>
                  </form>
                  <!-- ## 标题搜索表单 ~ END ------------------------------------------- -->
              
              </div>    
            </div>
            <div class="row">
              <div class="col">
            
                <!-- ## 标题搜索结果区 ------------------------------------------------ -->
                <div id="search-results-title" class="table-responsive">
                  <!-- 搜索结果会自动插入此处 -->
                </div>
                <!-- ## 标题搜索结果区 ~ END ------------------------------------------------ -->
                  
              </div>
            </div>
        </div>
    </body>
</html>

内容的提问来源于stack exchange，提问作者Muthu