Merge Consecutive Cells in Google Sheets Using Apps Script
I came across a post on Reddit [1] where a user asked how to merge cells horizontally if they contain identical values. While it is possible to do this manually, it can be cumbersome when working with large datasets. In this tutorial, I'll show you how to automate this using Apps Script.
This simple script will scan each row of your Google Sheets spreadsheet and merge consecutive cells that contain identical values.
The screenshots below provide a before and after comparison of a spreadsheet where consecutive cells with the same values are merged.
Before:
After:
Prerequisites
This tutorial assumes you already know the following concepts:
Basic familiarity with Google Sheets
Basic understanding of Google Apps Script. If you're new to Apps Script, check out my Creating your first Apps Script tutorial.
Understanding of ranges in Google Sheets
Apps Script code to merge consecutive cells containing the same value//@OnlyCurrentDoc
function mergeConsecutiveCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
for (var row = 0; row < values.length; row++) {
var startCol = 0;
for (var col = 0; col < values[row].length - 1; col++) {
if (values[row][col] === values[row][col + 1]) {
// Do nothing since we're within a sequence of identical values
} else {
// Reached the end of the sequence, so merge if necessary
if (startCol < col) {
sheet.getRange(row + 1, startCol + 1, 1, col - startCol + 1).mergeAcross();
}
startCol = col + 1;
}
}
// Check for merging at the end of the row
if (startCol < values[row].length - 1) {
sheet.getRange(row + 1, startCol + 1, 1, values[row].length - startCol).mergeAcross();
}
}
}
How the code works
//@OnlyCurrentDoc
function mergeConsecutiveCells() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var dataRange = sheet.getDataRange();
var values = dataRange.getValues();
for (var row = 0; row < values.length; row++) {
var startCol = 0;
for (var col = 0; col < values[row].length - 1; col++) {
if (values[row][col] === values[row][col + 1]) {
// Do nothing since we're within a sequence of identical values
} else {
// Reached the end of the sequence, so merge if necessary
if (startCol < col) {
sheet.getRange(row + 1, startCol + 1, 1, col - startCol + 1).mergeAcross();
}
startCol = col + 1;
}
}
// Check for merging at the end of the row
if (startCol < values[row].length - 1) {
sheet.getRange(row + 1, startCol + 1, 1, values[row].length - startCol).mergeAcross();
}
}
}The script works by processing each row in the Google Sheets spreadsheet to identify and merge consecutive cells containing identical values. Here's the business logic:
The script first retrieves all data from the active sheet using
getDataRange().getValues()It then tracks potential merge sequences within each row using two key elements:
A loop that compares adjacent cell values
A
startColvariable that marks the beginning of each sequence of identical valuesWhen the script encounters different values in adjacent cells, it:
Checks if a mergeable sequence exists (
startCol < col)Merges the sequence using
mergeAcross()if a sequence is foundUpdates
startColto begin tracking a new potential sequenceAt the end of each row, the script performs a final check to merge any remaining sequence of identical values.
Important
The script only considers values in each cell and does not handle formulas. If your spreadsheet contains formulas, they may be lost during the merge process. To preserve formulas, the script would need to be modified to compare both values and formulas before merging cells.
Note
The script only performs horizontal merges within rows. Vertical merging across rows is not implemented. The Range class provides three methods to merge cells:
merge(): Merges all cells in the range.mergeAcross(): Merges cells horizontally, across the columns in the range.mergeVertically(): Merges cells vertically, across rows in the range.
We only used the mergeAcross() method since we had to merge consecutive cells with identical values within each row.
Important
Before running the script, make sure you have a backup of your data or test it on a copy of your spreadsheet first.
Conclusion
In this tutorial I showed you how to automatically merge consecutive cells with identical values in Google Sheets using Apps Script.
[1] https://www.reddit.com/r/GoogleAppsScript/comments/1gsu3o3/merging_cells_horizontally_in_rows_if_the_data_in/