Parcel下拉框联动获取Google Sheets对应最大Status值异常求助

云上老周

2026-6-27

问题：选择Parcel下拉框后，Status下拉框无法自动填充对应最大状态值

我有一个包含「Statuses」工作表的Google表格，同时用Apps Script开发了一个Web表单。需求是用户选择Parcel下拉框后，自动从Google表格中获取该Parcel对应的最大状态值，并填充到Status下拉框中。但实际测试时，选择表格中存在的Parcel选项后，Status下拉框并未填充对应的最大状态值（比如「Negotiating」）。

当前使用的Apps Script代码

function doGet(e) {
  var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
  var subs = getDistinctSubstations();
  htmlOutput.message = '';
  htmlOutput.subs = subs;
  return htmlOutput.evaluate();
}

function doPost(e) {
  var parcel = e.parameter.parcel.toString();
  var substation = e.parameter.substation.toString();
  var comment = e.parameter.comment.toString();
  var status = e.parameter.status.toString();
  var date = new Date();
  
  addRecord(comment, parcel, date);
  addToStatuses(parcel, status, date);
  
  var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
  var subs = getDistinctSubstations();
  htmlOutput.message = 'Record Added';
  htmlOutput.subs = subs;
  return htmlOutput.evaluate(); 
}

function getStatusOptions(parcel) {
  var maxStatus = getMaxStatusForParcel(parcel);
  var options = ["", "Outreach", "Negotiating", "Signed"];
  var dropdownHTML = options.map(option => {
    if (option === maxStatus) {
      return `<option value="${option}" selected>${option}</option>`;
    } else {
      return `<option value="${option}">${option}</option>`;
    }
  }).join("");
  
  return dropdownHTML;
}

function getDistinctSubstations() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rdSheet = ss.getSheetByName("Raw Data"); 
  var subs = rdSheet.getRange('O4:O' + rdSheet.getLastRow()).getValues().flat().filter((sub, index, self) => self.indexOf(sub) === index && sub !== "");
  return subs;
}

function getParcels(substation) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rdSheet = ss.getSheetByName("Raw Data"); 
  var lastRow = rdSheet.getLastRow();
  var substationValues = rdSheet.getRange('O4:O' + lastRow).getValues().flat();
  var parcelValues = rdSheet.getRange('A4:A' + lastRow).getValues().flat();
  var filteredParcels = [];
  
  for (var i = 0; i < substationValues.length; i++) {
    if (substationValues[i] === substation && parcelValues[i]) {
      filteredParcels.push(parcelValues[i]);
    }
  }
  
  return filteredParcels.filter(parcel => parcel !== "");
}

// 原逻辑存在问题的函数
function getMaxStatusForParcel(parcel) {
  var url = 'https://docs.google.com/spreadsheets...';
  var ss = SpreadsheetApp.openByUrl(url);
  var statusSheet = ss.getSheetByName("Statuses");
  var lastRow = statusSheet.getLastRow();
  var parcelValues = statusSheet.getRange('A4:A' + lastRow).getValues().flat();
  var statusValues = statusSheet.getRange('B4:B' + lastRow).getValues().flat();
  
  for (var i = 0; i < parcelValues.length; i++) {
    if (parcelValues[i] === parcel) {
      return statusValues[i];
    }
  }
  
  return ""; // 未找到匹配Parcel时返回空字符串
}

function addRecord(comment, parcel, date) {
  var url = 'https://docs.google.com/spreadsheets....';
  var ss = SpreadsheetApp.openByUrl(url);
  var dataSheet = ss.getSheetByName("Comments");
  var formattedDate = Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), "MM/dd/yy");
  dataSheet.appendRow([parcel, comment, formattedDate]);
}

function addToStatuses(parcel, status, date) {
  var url = 'https://docs.google.com/spreadsheets....';
  var ss = SpreadsheetApp.openByUrl(url);
  var statusSheet = ss.getSheetByName("Statuses");
  var formattedDate = Utilities.formatDate(new Date(date), Session.getScriptTimeZone(), "MM/dd/yy");
  statusSheet.appendRow([parcel, status, formattedDate]);
}

function getUrl() {
  var url = ScriptApp.getService().getUrl();
  return url;
}

HTML代码

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
    body {
      font-size: 15px;
      background-color: #f0f8ff; /* 浅蓝色背景 */
    }
    .container {
      width: 50%;
      margin: 0 auto; /* 水平居中容器 */
      text-align: center; /* 容器内内容居中 */
      padding-top: 20px; /* 顶部留白 */
    }
    h1 {
      margin-top: 20px; /* 头部图片与标题间距 */
      font-size: 20px;
    }
    select {
      width: 100%; /* 下拉框占满容器宽度 */
      font-size: 15px; /* 下拉框字体大小 */
    }
    textarea {
      height: 80px;
      font-size: 15px; /* 文本域字体大小 */
    }
    input[type="submit"] {
      font-size: 15px; /* 提交按钮字体大小 */
    }
    .submit-message {
      color: red;
      font-size: 15px;
    }
  </style>
  <script>
    function getParcels(substation) {
      google.script.run.withSuccessHandler(function(parcels) {
        var parcelDropdown = document.getElementById("parcel");
        parcelDropdown.innerHTML = "";
        var option = document.createElement("option");
        option.value = "";
        option.text = "";
        parcelDropdown.appendChild(option);
        parcels.forEach(function(parcel) {
          var option = document.createElement("option");
          option.value = parcel;
          option.text = parcel;
          parcelDropdown.appendChild(option);
        });
      }).getParcels(substation);
    };
    
    function getMaxStatusForParcel(parcel) {
      var statusDropdown = document.getElementById("status");
      if (!parcel) {
        statusDropdown.innerHTML = '<option value=""></option>';
        return;
      }

      google.script.run.withSuccessHandler(function(maxStatus) {
        var options = ["", "Outreach", "Negotiating", "Signed"];
        var dropdownHTML = options.map(option => {
          if (option === maxStatus) {
            return `<option value="${option}" selected>${option}</option>`;
          } else {
            return `<option value="${option}">${option}</option>`;
          }
        }).join("");
        
        statusDropdown.innerHTML = dropdownHTML;
      }).getStatusOptions(parcel);
    }
    
    function validateForm() {
      var parcel = document.getElementById("parcel").value;
      var substation = document.getElementById("substation").value;
      var comment = document.getElementById("comment").value;
      var status = document.getElementById("status").value; // 获取状态值
      
      if (!parcel || !substation || !comment || !status) {
        document.getElementById("submit-message").innerText = "所有字段为必填项。";
        return false; // 阻止表单提交
      }
      
      return true; // 允许表单提交
    }
  </script>  
</head>
<body>
  <div class="container">
    <img src="https://i.imgur.com/16QsZja.png" alt="Header Image" style="width: 100%; max-width: 200px;">
    
    <h1>评论提交表单</h1>
    
    <? var url = getUrl(); ?>
    <form method="post" action="<?= url ?>" onsubmit="return validateForm()">
      <label>变电站</label><br>
      <select name="substation" id="substation" onchange="getParcels(this.value)">
        <option value=""></option>
        <? for (var i = 0; i < subs.length; i++) { ?>
          <option value="<?= subs[i] ?>"><?= subs[i] ?></option>
        <? } ?>
      </select><br><br>
      
      <label>Parcel</label><br>
      <select name="parcel" id="parcel" onchange="getMaxStatusForParcel(this.value)"></select><br><br>
      
      <label>状态</label><br>
      <select name="status" id="status"></select><br><br>
      
      <label>评论</label><br>
      <textarea name="comment" id="comment"></textarea><br><br>
      
      <input type="submit" name="submitButton" value="提交" /> 
      <span id="submit-message" class="submit-message"></span>      
    </form>
  </div>
</body>
</html>

问题分析与修复方案

核心问题

原getMaxStatusForParcel函数逻辑错误：找到第一个匹配的Parcel就直接返回对应的Status，而不是遍历所有匹配记录并筛选出优先级最高的状态值。

修复后的`getMaxStatusForParcel`函数

function getMaxStatusForParcel(parcel) {
  var url = 'https://docs.google.com/spreadsheets...'; // 替换为你的表格URL
  var ss = SpreadsheetApp.openByUrl(url);
  var statusSheet = ss.getSheetByName("Statuses");
  // 精准获取A4到最后一行的有效数据（排除前3行表头）
  var data = statusSheet.getRange(4, 1, statusSheet.getLastRow() - 3, 2).getValues();
  
  // 定义状态优先级，数值越高优先级越高
  var statusPriority = {
    "": 0,
    "Outreach": 1,
    "Negotiating": 2,
    "Signed": 3
  };
  var maxPriority = 0;
  var maxStatus = "";

  for (var i = 0; i < data.length; i++) {
    var currentParcel = data[i][0];
    var currentStatus = data[i][1];
    // 匹配Parcel且当前状态优先级高于已记录的最高优先级时更新结果
    if (currentParcel === parcel && statusPriority[currentStatus] > maxPriority) {
      maxPriority = statusPriority[currentStatus];
      maxStatus = currentStatus;
    }
  }
  
  return maxStatus;
}

额外检查点

表格权限：确保Google表格已设置正确的共享权限，允许Apps Script访问该表格
值匹配问题：检查Parcel值的大小写、空格是否完全一致（比如表格中是「Parcel_001」，表单中是「parcel_001」会导致匹配失败）
数据范围：使用getRange(4,1,statusSheet.getLastRow()-3,2)替代原范围获取方式，避免包含空行导致的无效数据

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

Parcel下拉框联动获取Google Sheets对应最大Status值异常求助

当前使用的Apps Script代码 #

HTML代码 #

问题分析与修复方案 #

核心问题 #

修复后的getMaxStatusForParcel函数 #