Google Sheets借款人追踪器：实现借据查询与归还信息更新功能

核心思路 #

将数据库设为只读（仅管理员可编辑），团队成员通过单独的「归还报表」标签完成操作：借据编号搜索→匹配数据展示→填写归还单号→一键同步至数据库，全程不直接接触核心数据库。

分步实现 #

1. 数据库标签权限锁定 #

• 右键数据库标签 → 选择「保护工作表和范围」
• 权限设置为「仅特定用户可编辑」，添加管理员账号；其余团队成员设为「查看权限」
• 锁定表头行（冻结窗格），防止结构被误修改

2. 归还报表标签布局（匹配图示样式） #

按功能划分区域，用单元格边框/背景色区分：

• 搜索区：A1单元格标注*借据编号*，A2为输入框（团队成员可编辑）
• 信息展示区：B4:E4展示匹配到的工具名称、借款人、借出日期等数据（设为不可编辑）
• 更新区：F1单元格标注*归还单据编号*，F2为输入框；旁边插入「同步至数据库」按钮
• 样式调整：给每个区域加边框，用浅灰色背景区分功能块，对齐图示布局

3. 搜索匹配功能（公式实现） #

假设数据库标签名为Database，借据编号在A列，需展示的字段在B-E列：
在归还报表的B4单元格输入公式，横向拖拽到E4：

=IFERROR(XLOOKUP($A$2, Database!$A:$A, Database!B:B, "未找到匹配借据"), "")

• $A$2是搜索输入框的固定引用，确保拖拽公式时不会偏移
• 给A2输入框加数据验证：限制为文本格式，避免无效输入

4. 同步更新脚本（Google Apps Script） #

点击「扩展程序」→「Apps Script」，创建以下脚本：

function syncReturnSlip() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const borrowRef = activeSheet.getRange("A2").getValue().trim();
  const returnRef = activeSheet.getRange("F2").getValue().trim();

  // 输入校验
  if (!borrowRef || !returnRef) {
    SpreadsheetApp.getUi().alert("请完整填写借据编号和归还单据编号");
    return;
  }

  // 连接数据库
  const dbSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Database");
  const data = dbSheet.getDataRange().getValues();

  // 查找并更新
  for (let i = 1; i < data.length; i++) {
    if (data[i][0].trim() === borrowRef) {
      // 更新归还单号（假设数据库F列是归还单号）
      dbSheet.getRange(i + 1, 6).setValue(returnRef);
      // 可选：更新状态为「已归还」（假设E列是状态）
      dbSheet.getRange(i + 1, 5).setValue("已归还");
      // 可选：更新归还日期（假设G列是归还日期）
      dbSheet.getRange(i + 1, 7).setValue(new Date());

      SpreadsheetApp.getUi().alert("更新成功！");
      // 清空输入和展示区
      activeSheet.getRange("A2,F2").clearContent();
      activeSheet.getRange("B4:E4").clearContent();
      return;
    }
  }

  SpreadsheetApp.getUi().alert("未找到对应借据编号，请检查输入");
}

绑定按钮到脚本：

• 插入→绘图，绘制矩形按钮，标注「同步至数据库」
• 右键按钮→「分配脚本」，输入syncReturnSlip

5. 归还报表权限控制 #

• 选中信息展示区（B4:E4）及其他非输入区域
• 右键→「保护工作表和范围」，设置为「仅特定用户可编辑」，仅允许管理员修改这些区域；团队成员仅能编辑A2、F2输入框

优化建议 #

• 给输入框添加提示文本（数据验证→输入消息），引导正确输入
• 数据库标签添加条件格式：给「已归还」状态的行设置浅灰色背景，快速识别
• 脚本中可添加日志记录，方便排查问题：Logger.log("更新借据：" + borrowRef + " 归还单号：" + returnRef);

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