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 | 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 |
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?
In your showing script, as a simple modification, how about the following modification?
const range = sheet.getRange(startRowIndex + 1, colIndex + 1, i - startRowIndex, 1);
const range = sheet.getRange(startRowIndex + 2, colIndex + 1, i - startRowIndex, 1);
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}`);
}
}
I reflected the following reply to the above sample script.
The script you provided is merging the cells in a expected manner for the first execution. It's still throwing the error whenever new data is added to the sheet and try to run again