google-sheetsgoogle-apps-script

How can I resolve this `Error: You must select all cells in a merged range to merge or unmerge them.` while merging cells through App Script?


I was trying to merge cells in Google Sheets using App Script when multiple rows have the same values in corresponding columns, I aimed to merge the cells based on one column.

Like, I was merging the rest of the corresponding column values by checking the second column's value(index 1). If multiple rows have same values for that column, I want to merge rest of the column values into one for those rows.

Although I'm not able to implement the functionality properly, the merging feature is working partially for the first run. But whenever I add some new rows and try to execute the script again, it's throwing this Error: You must select all cells in a merged range to merge or unmerge them.

Here's the raw data:

Date Customer Name Contact Number Email Address Product ID
11/1/2024 Wilma Gibbs 019999999999 myzitune@mailinator.com Sint magni, Anim, Upsa Combo-Z
11/1/2024 Dorothy Douglas 012222222222 dorothy@mailinator.com Incidunt, Minim Combo-A
11/1/2024 Paloma Wilcox 010000000000 paloma@mailinator.com Block B/50, Earth Combo-B
11/1/2024 Paloma Wilcox 010000000000 paloma@mailinator.com Block B/50, Earth Combo-B
11/1/2024 Paloma Wilcox 010000000000 paloma@mailinator.com Block B/50, Earth Combo-B
11/1/2024 Mari Patrick 016666666666 bymyjego@mailinator.com A/B Beatae, Consequatu Combo-A

->> Raw data On the sheet

The result I want:
->> Expected outcome

After running the following function in the Apps Script, I’m getting a corrupted outcome on the first execution. This results in the titled error on every execution afterward!
->> Corrupted outcome

function mergeCustomerOrderRows() {
  const spreadsheetId = "GOOGLE_SHEET_ID";
  const sheetName = "SHEET_NAME";
  const mergeColumns = [0, 1, 2, 3, 4]; // I don't want 'Product ID'(index 5) to be merged

  try {
    const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
    const sheet = spreadsheet.getSheetByName(sheetName);

    if (!sheet) throw new Error(`Sheet "${sheetName}" not found.`);

    const dataRange = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());
    const data = dataRange.getValues();
    
    let startRowIndex = 1;
    for (let i = 1; i < data.length; i++) {
      if (data[i][1] === data[i - 1][1]) continue;

      if (startRowIndex < i) {
        mergeColumns.forEach(colIndex => {
          const range = sheet.getRange(startRowIndex + 1, colIndex + 1, i - startRowIndex, 1);
          range.merge();
        });
      }
      startRowIndex = i;
    }

    if (startRowIndex < data.length) {
      mergeColumns.forEach(colIndex => {
        const range = sheet.getRange(startRowIndex+1, colIndex+1, data.length - startRowIndex, 1);
        range.merge();
      });
    }

    SpreadsheetApp.flush();
    Logger.log("Merging completed based on the second column match.");
    return ContentService.createTextOutput("Merged cells successfully based on second column condition");
  } catch (error) {
    Logger.log(`Error: ${error.message}`);
    return ContentService.createTextOutput(`Error: ${error.message}`);
  }
}

What changes could make it work the way I want?


Solution

  • In your showing script, as a simple modification, how about the following modification?

    From:

    const range = sheet.getRange(startRowIndex + 1, colIndex + 1, i - startRowIndex, 1);
    

    To:

    const range = sheet.getRange(startRowIndex + 2, colIndex + 1, i - startRowIndex, 1);
    

    Note:

    As another approach, how about the following sample script?

    function mergeCustomerOrderRows() {
      const spreadsheetId = "GOOGLE_SHEET_ID";
      const sheetName = "SHEET_NAME";
      const mergeColumns = ["A", "B", "C", "D", "E"];
      const checkCol = 2; // Column "B". This is from your script.
    
      try {
        const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
        const values = sheet.getDataRange().getValues();
        let temp = "";
        let tempAr = [];
        const ar = [];
        for (let i = 0; i < values.length; i++) {
          const v = values[i][checkCol - 1];
          const row = i + 1;
          if (v == temp) {
            tempAr.push(row);
          } else {
            if (tempAr.length > 0) {
              ar.push(tempAr);
            }
            tempAr = [row];
            temp = v;
          }
          if (i == values.length - 1 && tempAr.length > 0) {
            ar.push(tempAr);
          }
        }
        ar.filter(r => r.length > 1).forEach(r => {
          const start = r.shift();
          const end = r.pop();
          mergeColumns.forEach(m => {
            const range = sheet.getRange(`${m}${start}:${m}${end}`);
            if (!range.isPartOfMerge()) {
              range.merge();
            }
          });
        });
    
        SpreadsheetApp.flush(); // I thought that this line might not be required to be used.
        Logger.log("Merging completed based on the first column match.");
        return ContentService.createTextOutput("Merged cells successfully based on first column condition");
      } catch (error) {
        Logger.log(`Error: ${error.message}`);
        return ContentService.createTextOutput(`Error: ${error.message}`);
      }
    }
    

    Updated: