如何用Google Sheets Apps Script实现客户名检索自动填充配送日志

核心思路 #

针对你的配送日志需求，我们用Google Apps Script实现两个核心功能：

1. 输入客户名时的自动候选提示（支持部分匹配下拉）
2. 选中客户全名后自动填充关联信息（地址、邮编、电话等）

步骤1：确认表格结构 #

先统一你的两个工作表命名和列顺序：

• 客户列表：存储固定客户信息，列顺序为「姓名、地址、邮编、电话、附加信息」（可根据实际调整）
• 配送日志：每周配送清单，A列为客户姓名输入列，B-E列为待填充的关联信息列

步骤2：编写Apps Script代码 #

打开Google Sheets，点击扩展程序 > Apps Script，清空默认代码后粘贴以下内容：

代码1：实现客户名自动候选提示 #

// 给配送日志A列添加匹配候选提示
function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  const editRange = e.range;

  // 仅对配送日志的A列生效
  if (activeSheet.getName() !== "配送日志" || editRange.getColumn() !== 1) return;

  const customerSheet = e.source.getSheetByName("客户列表");
  // 获取所有客户姓名（跳过表头）
  const allNames = customerSheet.getRange(2, 1, customerSheet.getLastRow()-1, 1).getValues().flat();

  // 创建数据验证规则，允许部分匹配提示
  const validationRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(allNames, true)
    .setAllowInvalid(false) // 禁止输入不在列表中的名字
    .build();

  editRange.setDataValidation(validationRule);
}

代码2：选中客户名后自动填充信息 #

// 选中客户名时自动填充关联数据
function autoFillCustomerData(e) {
  const activeSheet = e.source.getActiveSheet();
  const editRange = e.range;

  // 仅对配送日志A列且单元格非空时生效
  if (activeSheet.getName() !== "配送日志" || editRange.getColumn() !== 1 || editRange.getValue() === "") return;

  const targetName = editRange.getValue();
  const customerSheet = e.source.getSheetByName("客户列表");
  const allCustomerData = customerSheet.getDataRange().getValues();

  // 遍历匹配客户信息
  for (let i = 1; i < allCustomerData.length; i++) {
    if (allCustomerData[i][0] === targetName) {
      // 填充地址、邮编、电话、附加信息（对应B-E列）
      editRange.offset(0, 1).setValue(allCustomerData[i][1]);
      editRange.offset(0, 2).setValue(allCustomerData[i][2]);
      editRange.offset(0, 3).setValue(allCustomerData[i][3]);
      editRange.offset(0, 4).setValue(allCustomerData[i][4]);
      break;
    }
  }
}

// 创建编辑触发器，确保自动填充生效
function setupTrigger() {
  ScriptApp.newTrigger("autoFillCustomerData")
    .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
    .onEdit()
    .create();
}

步骤3：配置与测试 #

1. 运行setupTrigger函数：在Apps Script编辑器中选择该函数，点击运行按钮，按提示完成权限授权（首次运行需要授权脚本访问表格数据）
2. 返回配送日志工作表，在A列输入客户名的前几个字，会自动弹出匹配的候选列表，选中全名后，B-E列会自动填充对应信息
3. 如果你的客户列表列顺序不同，只需修改代码中allCustomerData[i][x]的索引值（比如地址在第3列就改成allCustomerData[i][2]，索引从0开始计数）

新手调试提示 #

• 候选提示不显示：检查客户列表的工作表名称是否和代码中一致，确保客户姓名都在第1列且无空行
• 自动填充失效：确认已运行setupTrigger，若要忽略大小写匹配，可将判断条件改为allCustomerData[i][0].toLowerCase() === targetName.toLowerCase()

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