Google Sheets多字符串编号自动递增优化及持久化技术问询

原代码核心问题分析 #

• 编号无法持续递增：全局变量numberDRH这类计数变量，每次脚本触发时都会重置为0——因为Google Apps Script每次执行都是全新的环境，变量不会持久存储。
• 效率低下：多次单独调用getRange()和setValue()（这类IO操作本身就耗时）；冗余的独立变量定义；冗长的if-else分支；还有那个毫无意义的单次循环。

解决方案与优化代码 #

优化后的完整代码 #

// 用映射表统一管理医院名称和对应前缀，替代原有的一堆独立变量
const HOSPITAL_MAPPING = {
  "Davao Regional Hospital": "DVODRH",
  "Southern Philippines Medical Center": "DVOSPMC",
  "Kidapawan Doctors Hospital": "DVOKDH",
  "Mlang Specialist Medical Hospital": "DVOMSMH",
  "Mindanao Doctors Hospital and Cancer Center": "DVOMDHCC",
  "Kabacan Polymedic Hospital": "DVOKPH",
  "Dr. Aturo Pinggoy Hospital": "DVODAPH",
  "Socsargen County Hospital": "DVOSCH",
  "Davao del Sur Provincial Hospital": "DVODDSPH",
  "Davao Oriental Provincial Medical Center": "DVODOPMC"
};

function hospitalTag(e) {
  // 直接用表单提交事件对象e获取数据，减少重复读表操作
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hospitals");
  const hospitalName = e.values[2]; // 对应原代码的C列（数组索引从0开始）
  const prefix = HOSPITAL_MAPPING[hospitalName];
  
  if (!prefix) return; // 医院名称不在映射表中，直接退出
  
  // 读取第19列（序列号列）的所有数据，批量处理提升效率
  const serialColumn = sheet.getRange(2, 19, sheet.getLastRow() - 1, 1).getValues();
  let latestNumber = 0;
  
  // 遍历已有的序列号，找出当前医院的最大编号
  serialColumn.forEach(row => {
    const serial = row[0];
    if (serial && serial.startsWith(prefix)) {
      const num = parseInt(serial.split("-")[1]);
      if (num > latestNumber) latestNumber = num;
    }
  });
  
  // 生成新序列号，补零保持两位格式（如01、02）
  const newSerial = `${prefix}-${String(latestNumber + 1).padStart(2, "0")}`;
  
  // 写入新序列号到当前提交行的第19列
  sheet.getRange(e.range.rowStart, 19).setValue(newSerial);
}

关键优化点 #

• 用映射表简化管理：把医院名称和前缀统一存在对象里，后续加医院只需要改这个映射表，不用写一堆新变量和分支。
• 利用表单事件对象：直接从e里拿提交的数据和行位置，避免多次调用getLastRow()和getRange()，减少耗时的IO操作。
• 解决编号递增问题：通过读取已有的序列号列，提取对应医院的最大编号，确保每次触发都能拿到最新计数，不会重置。
• 批量读取数据：一次性把整个序列号列的数据读进来处理，比多次单独读单元格快很多。
• 统一编号格式：用padStart(2, "0")保证编号是两位数字，符合原代码的格式需求。

备选方案：用PropertiesService存计数 #

如果表格数据量特别大，读整列效率低，可以用脚本属性存储各医院的当前计数：

const HOSPITAL_MAPPING = { /* 同上面的映射表 */ };

function hospitalTag(e) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Hospitals");
  const hospitalName = e.values[2];
  const prefix = HOSPITAL_MAPPING[hospitalName];
  
  if (!prefix) return;
  
  const props = PropertiesService.getScriptProperties();
  const currentCount = parseInt(props.getProperty(prefix)) || 0;
  const newCount = currentCount + 1;
  const newSerial = `${prefix}-${String(newCount).padStart(2, "0")}`;
  
  sheet.getRange(e.range.rowStart, 19).setValue(newSerial);
  props.setProperty(prefix, newCount); // 更新存储的计数
}

注意：用这个方法时，如果手动修改了表格里的序列号，要同步更新Properties里的计数，不然会出现编号不一致的情况。

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