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

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 startCol variable that marks the beginning of each sequence of identical values

• When 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 found

• Updates startCol to begin tracking a new potential sequence

• At the end of each row, the script performs a final check to merge any remaining sequence of identical values.

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/