Google Web App搜索框筛选Google Spreadsheet超2000行数据报错解决方案咨询

• 后端读取数据范围硬编码为ABC!A2:DV1500，数据超过1500行后新增内容无法被读取
• 前端院校名称列表硬编码在HTML中，数据量增大后HTML文件体积超标，加载超时触发无法打开文件错误
• 搜索逻辑为整行任意内容完全匹配，不符合仅匹配院校名称的需求
• 硬编码的院校名称列表需要手动更新，维护成本高

1. 后端动态获取表格所有有效数据，不再写死行范围
2. 新增后端接口自动返回所有院校名称，前端加载时异步拉取用于自动补全，取消前端硬编码
3. 搜索逻辑优化为仅匹配院校名称列，支持大小写不敏感的模糊匹配
4. 移除前端冗余的重复Bootstrap JS引入，减少加载体积

后端代码（Code.gs） #

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

// 获取所有院校名称用于前端自动补全
function getInstitutionNames() {
  const spreadsheetId = 'xxxxxxx'; // 替换为你的表格ID
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('ABC');
  // 获取A列（院校名称列）所有非空数据，从第2行开始
  const names = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues().flat().filter(item => item);
  return [...new Set(names)]; // 去重后返回
}

/* PROCESS FORM */
function processForm(formObject){  
  let result = [];
  if(formObject.searchtext?.trim()){
      result = search(formObject.searchtext.trim());
  }
  return result;
}

//SEARCH FOR MATCHED CONTENTS 
function search(searchtext){
  const spreadsheetId = 'xxxxxxx'; // 替换为你的表格ID
  const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName('ABC');
  // 动态获取所有有效数据
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  const ar = [];
  const searchKey = searchtext.toLowerCase();
  
  data.forEach(row => {
    // 仅匹配第一列（院校名称），大小写不敏感的包含匹配
    if (row[0]?.toLowerCase().includes(searchKey)) {
      ar.push(row);
    }
  });
  return ar;
}

前端代码（Index.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://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.3.1/js/bootstrap.bundle.min.js" integrity="sha384-xrRywqdh3PHs8keKZN+8zzc5TX0GRTLCcmivcbNJWm2rs5C8PRhcEn3czEjhAO9o" crossorigin="anonymous"></script>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>院校搜索系统</title>
    <link rel="stylesheet" href="//code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script type="text/javascript">
    // 页面加载完成后拉取院校名称用于自动补全
    $( function() {
      google.script.run.withSuccessHandler(names => {
        $( "#searchtext" ).autocomplete({
          source: names
        });
      }).getInstitutionNames();
    });

    //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);          

    function myFunction() {
      document.getElementById("searchtext").size = "70";
    }

    //HANDLE FORM SUBMISSION
    function handleFormSubmit(formObject) {
      google.script.run.withSuccessHandler(createTable).processForm(formObject);
    }

    //CREATE THE DATA TABLE
    function createTable(dataArray) {
      const div = document.getElementById('search-results');
      if(dataArray?.length){
        let result = "<table class='table table-sm table-striped' id='dtable' style='font-size:0.8em'>"+
                     "<thead style='white-space: nowrap'>"+
                       "<tr>"+
                        "<th scope='col'>Institution</th>"+
                        "<th scope='col'>Agreement with</th>"+
                        "<th scope='col'>Type of institution</th>"+                              
                        "<th scope='col'>Country</th>"+
                        // 其余表头按需补充
                    "</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>";
        div.innerHTML = result;
      }else{
        div.innerHTML = "Data not found!";
      }
    }        
    </script>
</head>
<body style="background-color:#cdffcd;">
  <h1 align="center"><img src="https://drive.google.com/uc?export=download&id=16SqA92JMCD3AVWH07BeyPPLlEBaO_4Ae" alt="logo" width="120" height="100"/>&nbsp;&nbsp;The SoT Search Form</h1>
    <div class="container">
        <br>
        <div class="row">
          <div class="col">
              <form id="search-form" class="form-inline" onsubmit="handleFormSubmit(this)">                  
                <div class="form-group mb-2">
                  <label for="searchtext" align="center"><strong>Search for the provider</strong></label>
                </div>
                <div class="form-group mx-sm-3 mb-2">  
                  <input id="searchtext" name="searchtext" class="form-control" onclick="myFunction()" placeholder="Enter the name.." align="center">
                 </div>
                <button type="submit" class="btn btn-primary mb-2">Search</button>
                <input type="reset" class="btn btn-primary mb-2" id="resetbutton" style='margin-left:16px' value="Reset">
              </form>
          </div>    
        </div>
        <div class="row">
          <div class="col">
            <div id="search-results" class="table-responsive">
            </div>
          </div>
        </div>
    </div>
</body>
</html>

• 代码中两处xxxxxxx需替换为你自己的Google表格ID
• 院校名称默认读取表格第一列，若你的院校名称不在A列可自行调整getRange的列参数
• 表格列头可根据你的实际字段需求补充完整

内容的提问来源于stack exchange，提问作者Sāùŗăbh Šħârâmâ