如何实现网站输入学号后从Google Sheets查询返回对应护照积分

前置准备 #

• 确认Google Sheets结构：第一列存储9位学号（格式需设置为文本，避免前导0丢失），第二列存储对应积分，表头可设置为student_id、points即可。
• 打开目标Google Sheets，点击顶部菜单「扩展程序」-「Apps Script」进入脚本编辑页。

第一步：编写Google Apps Script后端接口 #

该脚本作为轻量后端，接收前端传递的学号参数、查表返回积分结果：

function doGet(e) {
  // 配置跨域头，支持前端调用
  const responseHeaders = {
    "Content-Type": "application/json",
    "Access-Control-Allow-Origin": "*" // 正式上线可替换为学校平台域名，提升安全性
  };

  try {
    // 获取前端传入的学号参数
    const studentId = e.parameter.student_id;
    // 前端二次校验学号格式
    if (!/^\d{9}$/.test(studentId)) {
      return ContentService.createTextOutput(JSON.stringify({
        code: 400,
        msg: "请输入合法的9位数字学号",
        points: null
      })).setHeaders(responseHeaders);
    }

    // 打开目标表格，此处替换为你自己的表格ID（表格URL中d/和/edit之间的字符串）、工作表名称
    const sheet = SpreadsheetApp.openById("替换为你的Google Sheets ID").getSheetByName("替换为你的工作表名，如Sheet1");
    // 获取表格全量数据
    const rows = sheet.getDataRange().getValues();
    // 遍历匹配学号（i从1开始跳过表头）
    for (let i = 1; i < rows.length; i++) {
      if (rows[i][0] == studentId) {
        return ContentService.createTextOutput(JSON.stringify({
          code: 200,
          msg: "查询成功",
          points: rows[i][1]
        })).setHeaders(responseHeaders);
      }
    }

    // 未匹配到对应学号
    return ContentService.createTextOutput(JSON.stringify({
      code: 404,
      msg: "未查询到该学号对应的积分信息",
      points: null
    })).setHeaders(responseHeaders);
  } catch (error) {
    return ContentService.createTextOutput(JSON.stringify({
      code: 500,
      msg: "服务器错误，请稍后再试",
      points: null
    })).setHeaders(responseHeaders);
  }
}

脚本编写完成后按以下步骤部署：

1. 点击脚本编辑器右上角「部署」-「新建部署」
2. 点击设置图标选择「Web应用」
3. 填写版本说明，「执行身份」选择「我（你的谷歌账号）」，「谁可以访问」选择「所有人」
4. 点击部署完成授权，复制生成的Web应用URL，即为后端接口地址。

第二步：编写前端查询页面 #

<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
  <title>学生护照积分查询</title>
</head>
<body>
  <div style="max-width: 400px; margin: 100px auto;">
    <h2>学生护照积分查询</h2>
    <input type="text" id="studentIdInput" placeholder="请输入9位学号" maxlength="9" style="width: 100%; padding: 10px; margin: 10px 0;">
    <button id="queryBtn" style="width: 100%; padding: 10px; background: #2385bb; color: white; border: none; cursor: pointer;">查询积分</button>
    <div id="result" style="margin-top: 20px; font-size: 18px; text-align: center;"></div>
  </div>

  <script>
    const queryBtn = document.getElementById('queryBtn');
    const studentIdInput = document.getElementById('studentIdInput');
    const resultEl = document.getElementById('result');
    // 替换为上一步部署生成的Web应用URL
    const API_URL = '替换为你的Web应用URL';

    queryBtn.addEventListener('click', async () => {
      const studentId = studentIdInput.value.trim();
      if (!/^\d{9}$/.test(studentId)) {
        resultEl.textContent = '请输入合法的9位数字学号';
        resultEl.style.color = 'red';
        return;
      }
      resultEl.textContent = '查询中...';
      try {
        const res = await fetch(`${API_URL}?student_id=${studentId}`);
        const data = await res.json();
        if (data.code === 200) {
          resultEl.textContent = `您的积分是：${data.points}`;
          resultEl.style.color = 'green';
        } else {
          resultEl.textContent = data.msg;
          resultEl.style.color = 'red';
        }
      } catch (err) {
        resultEl.textContent = '查询失败，请稍后再试';
        resultEl.style.color = 'red';
      }
    });
  </script>
</body>
</html>

注意事项 #

• 学号列必须设置为文本格式，否则0开头的学号会丢失前导0导致查询失败。
• 首次部署Apps Script授权时如果出现未验证提示，点击「高级」-「继续访问（你的应用名）」即可完成授权，属于正常情况。
• 正式上线后建议把Apps Script里的Access-Control-Allow-Origin的值从*改成学校平台的实际域名，避免接口被恶意调用。
• 若查询量较大，可给脚本增加缓存逻辑优化查询速度，无需每次遍历全表。

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