谷歌表格新增行自动填充SHOW列下拉选项脚本失效求助

原脚本核心问题 #

• 列定位硬编码：固定使用D/E列，未根据表头动态查找，列位置变动后脚本直接失效。
• onEdit逻辑偏离需求：仅在编辑D/E列时给其他工作表添加下拉，跳过当前编辑表，完全未处理「新增行时给当前表SHOW列加下拉」的核心需求。
• 缺失百分位数计算：完全未实现YES(1)/NO(0)的权重百分位数计算逻辑，忽略核心功能需求。
• 未筛选目标工作表：initializeTracking给所有工作表加下拉，未判断是否包含指定表头。

修复后的完整脚本 #

// 辅助函数：获取指定工作表中SHOW和SHOW - RATE的列索引
function getTargetColumns(sheet) {
  const headerRow = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const showCol = headerRow.indexOf('SHOW') + 1;
  const rateCol = headerRow.indexOf('SHOW - RATE') + 1;
  
  // 仅当两个列都存在时返回结果
  if (showCol > 0 && rateCol > 0) {
    return { showCol, rateCol };
  }
  return null;
}

// 初始化：给符合条件的工作表SHOW列添加下拉，并计算初始百分位数
function initializeTracking() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = spreadsheet.getSheets();
  
  sheets.forEach(sheet => {
    const targetCols = getTargetColumns(sheet);
    if (!targetCols) return; // 跳过无指定表头的工作表
    
    const { showCol, rateCol } = targetCols;
    const lastRow = sheet.getLastRow();
    
    // 给SHOW列（除表头外）添加下拉选项
    if (lastRow > 1) {
      const showRange = sheet.getRange(2, showCol, lastRow - 1, 1);
      const validationRule = SpreadsheetApp.newDataValidation()
        .requireValueInList(['YES', 'NO'], true)
        .build();
      showRange.setDataValidation(validationRule);
    }
    
    // 计算并填充初始的SHOW-RATE百分位数
    updateShowRate(sheet, targetCols);
  });
}

// 更新SHOW-RATE列的百分位数（YES权重1，NO权重0）
function updateShowRate(sheet, targetCols) {
  const { showCol, rateCol } = targetCols;
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return; // 无数据行则跳过
  
  // 将SHOW列值转换为权重数组
  const showValues = sheet.getRange(2, showCol, lastRow - 1, 1).getValues()
    .map(row => row[0] === 'YES' ? 1 : 0);
  
  // 计算百分占比
  const totalWeight = showValues.reduce((sum, val) => sum + val, 0);
  const totalRows = showValues.length;
  const rate = totalRows > 0 ? (totalWeight / totalRows * 100).toFixed(2) + '%' : '0%';
  
  // 更新SHOW-RATE列的统计值（可根据需求调整为整列或特定单元格）
  sheet.getRange(2, rateCol).setValue(rate);
}

// 监听单元格编辑：修改SHOW列时同步更新百分位数
function onEdit(e) {
  if (!e || !e.source) return;
  
  const sheet = e.source.getActiveSheet();
  const targetCols = getTargetColumns(sheet);
  if (!targetCols) return;
  
  // 判断编辑的是SHOW列的非表头行
  if (e.range.getColumn() === targetCols.showCol && e.range.getRow() > 1) {
    updateShowRate(sheet, targetCols);
  }
}

// 监听表格变更：新增行时自动给SHOW列添加下拉
function onChange(e) {
  if (!e || e.changeType !== 'INSERT_ROW') return;
  
  const sheet = e.source.getActiveSheet();
  const targetCols = getTargetColumns(sheet);
  if (!targetCols) return;
  
  const newRow = e.range.getRow();
  // 给新增行的SHOW列添加下拉选项
  const validationRule = SpreadsheetApp.newDataValidation()
    .requireValueInList(['YES', 'NO'], true)
    .build();
  sheet.getRange(newRow, targetCols.showCol).setDataValidation(validationRule);
  
  // 同步更新百分位数
  updateShowRate(sheet, targetCols);
}

关键功能说明 #

1. 动态列定位：通过表头查找SHOW和SHOW-RATE列的位置，不再依赖固定列字母，适配列位置变动场景。
2. 工作表筛选：仅处理同时包含两个指定表头的工作表，自动忽略其他无关表。
3. 新增行自动处理：通过onChange监听行插入事件，给新增行的SHOW列自动添加下拉选项。
4. 实时百分位更新：修改SHOW列的YES/NO值时，onEdit触发百分位数计算并同步更新到SHOW-RATE列。
5. 初始化兼容：initializeTracking可手动执行或配置「打开文档」触发，确保新克隆的表格打开时自动完成初始化。

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