含垂直合并单元格的表格行排序报错，求可行解决方案

Hey there! I’ve run into this exact headache before—vertical merged cells breaking perfectly good sort scripts. The error You can't sort a range containing vertical merges is Sheets way of saying it can’t handle merged cells during sorting directly. But don’t worry, we can work around this with a three-step approach: unmerge & fill, sort, then remerge.

Step 1: Unmerge Cells and Fill Values #

First, we need to break apart all vertical merges and fill every cell in the merged range with the original top cell’s value. This gives us a clean, unmerged range that Sheets can sort without issues.

Here’s a script to do that:

function unmergeAndFill() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const mergedRanges = range.getMergedRanges();
  
  mergedRanges.forEach(mergedRange => {
    // Grab the value from the top cell of the merged range
    const topCellValue = mergedRange.getCell(1, 1).getValue();
    mergedRange.unmerge();
    // Fill the value to all cells that were merged
    mergedRange.setValue(topCellValue);
  });
}

Step 2: Run Your Original Sort Script #

Now that there are no merged cells, your existing sort code should work exactly as it did before. If you need a quick example, here’s a basic sort function (replace this with your own sorting logic):

function runSort() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sortRange = sheet.getDataRange();
  // Sort by column 2 (B column) in ascending order—adjust to your needs
  sortRange.sort({column: 2, ascending: true});
}

Step 3: Remerge Vertical Cells #

Once sorting is done, we can restore the vertical merges for cells with identical values. This script will scan each column and merge consecutive rows with the same value:

function remergeVerticalCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const numRows = data.length;
  const numCols = data[0].length;
  
  // Loop through each column to handle merges
  for (let col = 0; col < numCols; col++) {
    let startRow = 0; // Array index starts at 0, sheet rows start at 1
    let currentValue = data[startRow][col];
    
    for (let row = 1; row < numRows; row++) {
      if (data[row][col] !== currentValue) {
        // Merge from startRow to row-1 if there's more than one row
        if (row - startRow > 1) {
          sheet.getRange(startRow + 1, col + 1, row - startRow, 1).mergeVertically();
        }
        startRow = row;
        currentValue = data[row][col];
      }
    }
    // Handle the last group of identical values
    if (numRows - startRow > 1) {
      sheet.getRange(startRow + 1, col + 1, numRows - startRow, 1).mergeVertically();
    }
  }
}

Combine It All into One Function #

To make this seamless, you can wrap all three steps into a single function that you can run with one click:

function sortWithVerticalMerges() {
  // Step 1: Unmerge and fill
  unmergeAndFill();
  
  // Step 2: Run your custom sort logic
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sortRange = sheet.getDataRange();
  // Replace this line with your original sort code!
  sortRange.sort({column: 3, ascending: false});
  
  // Step 3: Remerge cells
  remergeVerticalCells();
}

Important Notes #

• Test first: Always run this on a copy of your spreadsheet to avoid accidental data changes.
• Formula handling: If your merged cells had formulas, filling them will duplicate the formula across all cells—this is normal, and remerging will hide the duplicates visually.
• Simple merges only: This workaround works best for straightforward vertical merges (no nested or cross-column merges).

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