How can I ensure my Google Apps Script merges cells in the final rows as expected?

I'm trying to use Google Apps Script to merge cells in Google Sheets when multiple rows have the same values in a specific column. My goal is to merge the cells in the other columns based on matches in one column (specifically, the second column at index 1). If multiple rows share the same value in this column, I want to merge the values of the other columns for those rows.

The following script works as expected, except for one condition: if the rows with the same values in the second column (the target column) are the last rows in the sheet, it doesn’t merge them like it does for other rows that are not at the end.

Thanks to @Tanaike who provided the following 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();
    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}`);
  }
}

Is there a way to adjust the code so it includes the last rows if they meet the criteria? Any insights or suggestions would be greatly appreciated!

Solution

In your situation, how about the following modification? In this modification, I used the sample script from this post (Author: me).

Modified script:

function mergeCustomerOrderRows() {
  const spreadsheetId = "GOOGLE_SHEET_ID";
  const sheetName = "SHEET_NAME";
  const mergeColumns = { start: "A", end: "E" }; // Columns "A", "B", "C", "D", and "E".
  const checkCol = 2; // Column "B". This is from your script.

  try {
    const sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
    const srcRange = sheet.getDataRange();
    const values = srcRange.getValues();
    srcRange.getMergedRanges().forEach((r) => {
      const startRow = r.getRow() - 1;
      const endRow = startRow + r.getNumRows() - 1;
      const startCol = r.getColumn() - 1;
      const endCol = startCol + r.getNumColumns() - 1;
      const v = values[startRow][startCol];
      for (let r = startRow; r <= endRow; r++) {
        for (let c = startCol; c <= endCol; c++) {
          if (!values[r]) {
            values[r] = [];
          }
          values[r][c] = v;
        }
      }
      r.breakApart();
    });

    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();
      const range = sheet.getRange(`${mergeColumns.start}${start}:${mergeColumns.end}${end}`);
      if (!range.isPartOfMerge()) {
        range.mergeVertically();
      }
    });

    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}`);
  }
}

In this modification, first, all merged cells are embedded by the values and cells are merged. And, the cells are merged again. By this, the additional rows can be processed.
Also, I modified the method for merging cells from merge to mergeVertically. By this, the process cost might be able to be reduced a little.

References:

Added:

Although I'm not sure whether I could correctly understand your current issue and your actual expected result, 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 range = sheet.getDataRange();
    const values = range.getValues();
    const lastRow = sheet.getRange(range.getNumRows() - 1, 1, 1, range.getNumColumns());
    if (lastRow.isPartOfMerge()) {
      lastRow.getMergedRanges().forEach((r) => {
        const startRow = r.getRow() - 1;
        const endRow = startRow + r.getNumRows() - 1;
        const startCol = r.getColumn() - 1;
        const endCol = startCol + r.getNumColumns() - 1;
        const v = values[startRow][startCol];
        for (let r = startRow; r <= endRow; r++) {
          for (let c = startCol; c <= endCol; c++) {
            if (!values[r]) {
              values[r] = [];
            }
            values[r][c] = v;
          }
        }
        r.breakApart();
      });
    }
    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();
    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}`);
  }
}