Google Sheets动态数据自动添加带空行边框的问题求助

问题背景 #

从下拉列表获取动态数据后，使用现有onEdit脚本无法自动根据数据更新边框，仅在编辑最后一行数据下方单元格时才生效，需要实现边框随动态数据自动调整，且底部边框前保留一行空行。

原尝试脚本 #

function onEdit(e) {
  const sheetName = "MONTHLY OVERVIEW";
  const expectedRange = "C10:G1000";

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() != sheetName) return;
  const r = sheet.getRange(expectedRange);
  const startRow = r.getRow();
  const endRow = startRow + r.getNumRows() - 1;
  const startCol = r.getColumn();
  const endCol = startCol + r.getNumColumns() - 1;
  if (range.rowStart < startRow || range.rowEnd > endRow || range.columnStart < startCol || range.columnEnd > endCol) return;
  r.setBorder(null, false, false, false, false, false);
  const last = r.getDisplayValues().findIndex(rr => !rr.join(""));
  sheet.getRange(startRow, startCol, last != -1 ? last : r.getNumRows(), endCol - startCol + 1).setBorder(true, true, true, true, true, false);
}

修改后的脚本 #

function onEdit(e) {
  const sheetName = "MONTHLY OVERVIEW";
  const expectedRange = "C10:G1000";

  const { range } = e;
  const sheet = range.getSheet();
  if (sheet.getSheetName() !== sheetName) return;
  
  // 获取目标区域及基础参数
  const targetRange = sheet.getRange(expectedRange);
  const startRow = targetRange.getRow();
  const startCol = targetRange.getColumn();
  const totalRows = targetRange.getNumRows();
  const totalCols = targetRange.getNumColumns();
  
  // 清除原有边框
  targetRange.setBorder(null, false, false, false, false, false);
  
  // 从下往上查找最后一行有数据的行
  const values = targetRange.getDisplayValues();
  let lastDataRowIndex = -1;
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i].join("").trim() !== "") {
      lastDataRowIndex = i;
      break;
    }
  }
  
  if (lastDataRowIndex === -1) return;
  
  // 计算边框范围：包含最后一行数据及下方空行
  const borderRowCount = lastDataRowIndex + 2; // +2是因为索引从0开始，加1到数据行，再加1到空行
  const borderRange = sheet.getRange(startRow, startCol, borderRowCount, totalCols);
  
  // 设置边框样式
  borderRange.setBorder(true, true, true, true, true, false);
}

核心改进说明 #

• 精准定位最后一行数据：原脚本从前往后找第一个空行，若数据中间有空行会导致边框提前截止；现在从下往上遍历，确保找到真正的最后一行数据。
• 自动保留底部空行：边框范围扩展到最后一行数据的下一行，满足“底部边框前留空行”的需求。
• 放宽触发条件：移除原脚本中仅允许编辑目标区域内单元格的限制，确保下拉列表数据更新时（手动选择选项）能立即触发边框调整。

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