如何通过Google Apps Script实现HTML邮箱输入联动谷歌表格填充字段

1. 整理谷歌表格数据结构 #

先确保你的谷歌表格有清晰的列布局，比如：

• A列：Agent Email ID（用于匹配的关键字段）
• B列：Agent EMP ID
• C列：Agent Name
• D列：主管姓名
  表格名称设为AgentData，表头放在第一行。

2. 编写Google Apps Script后端函数 #

打开脚本编辑器，创建一个用于根据邮箱查询员工数据的函数：

function getAgentDataByEmail(email) {
  // 替换为你的谷歌表格ID（从表格URL中获取）
  const sheet = SpreadsheetApp.openById("你的表格ID").getSheetByName("AgentData");
  const allData = sheet.getDataRange().getValues();

  // 跳过表头，遍历匹配邮箱
  for (let i = 1; i < allData.length; i++) {
    if (allData[i][0].toLowerCase() === email.toLowerCase()) {
      return {
        empId: allData[i][1],
        name: allData[i][2],
        supervisor: allData[i][3] // 根据实际列索引调整
      };
    }
  }
  return null; // 无匹配数据时返回null
}

3. 修改HTML表单与前端逻辑 #

给表单字段添加ID方便操作，同时添加输入监听脚本：

<form id="myForm" onsubmit="handleFormSubmit(this)">
  <table class="table table-bordered table-striped table-condensed" style="border-style: solid">
    <tr>
      <td colspan="6">Mandatory Fields </td>
    </tr>
    <tr>
      <th>Agent EMP ID</th>
      <td class="text-center">
        <input name="agent_ID" id="agentIdInput" type="hidden" class="">
        <span id="agentIdSpan">agent_ID</span>
      </td>
      <th>Agent Name: </th>
      <td class="text-center">
        <input name="agent_name" id="agentNameInput" type="hidden" class="">
        <span id="agentNameSpan">agent_name</span>
      </td>
      <th>Agent Email ID </th>
      <td class="text-center">
        <input name="agent_email_ID" id="a_email_id" type="text" value="" class="">
      </td>
    </tr>
    <!-- 新增主管姓名字段 -->
    <tr>
      <th>主管姓名</th>
      <td class="text-center" colspan="2">
        <span id="supervisorSpan">主管姓名</span>
        <input name="supervisor" id="supervisorInput" type="hidden" class="">
      </td>
    </tr>
  </table>
</form>

<script>
// 监听邮箱输入框的内容变化
document.getElementById('a_email_id').addEventListener('input', function() {
  const email = this.value.trim();
  
  // 清空字段（如果邮箱为空）
  if (!email) {
    resetFields();
    return;
  }

  // 调用后端函数获取数据
  google.script.run
    .withSuccessHandler(agentData => {
      if (agentData) {
        // 更新表单字段
        document.getElementById('agentIdSpan').textContent = agentData.empId;
        document.getElementById('agentIdInput').value = agentData.empId;
        document.getElementById('agentNameSpan').textContent = agentData.name;
        document.getElementById('agentNameInput').value = agentData.name;
        document.getElementById('supervisorSpan').textContent = agentData.supervisor;
        document.getElementById('supervisorInput').value = agentData.supervisor;
      } else {
        // 无匹配数据时提示
        document.getElementById('agentIdSpan').textContent = '未找到匹配员工';
        document.getElementById('agentNameSpan').textContent = '未找到匹配员工';
        document.getElementById('supervisorSpan').textContent = '未找到匹配员工';
        // 清空隐藏输入框
        document.getElementById('agentIdInput').value = '';
        document.getElementById('agentNameInput').value = '';
        document.getElementById('supervisorInput').value = '';
      }
    })
    .withFailureHandler(error => {
      console.error('获取数据失败:', error);
      alert('获取员工信息出错，请稍后重试');
    })
    .getAgentDataByEmail(email);
});

// 重置表单字段的辅助函数
function resetFields() {
  document.getElementById('agentIdSpan').textContent = 'agent_ID';
  document.getElementById('agentIdInput').value = '';
  document.getElementById('agentNameSpan').textContent = 'agent_name';
  document.getElementById('agentNameInput').value = '';
  document.getElementById('supervisorSpan').textContent = '主管姓名';
  document.getElementById('supervisorInput').value = '';
}
</script>

4. 测试与部署 #

1. 在脚本编辑器中点击「测试部署」，选择部署类型为「Web应用」，权限设置为「任何人，甚至匿名」（根据实际需求调整）。
2. 获取测试URL，打开后输入员工邮箱，验证是否自动填充对应字段。

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