0

I have a google sheet where i need to merge the cells if the values are repeating

The expected is to merge the cell from A1 to A4 and have a single value A in it.

I have tried with, 

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var range = sheet.getActiveRange(); //var values = range.getValues(); var numCols = values[0].length; values = [ ["A", "B", "C", "D", "E"], ["A", "B", "S", "D", "E"], ["A", "D", "C", "D", "E"], ["A", "B", "C", "D", "K"], ["c", "B", "W", "D", "K"], ["A", "B", "C", "D", "E"], ] for (var j = 0; j < numCols; j++) { var placer = ''; for (var i = 0; i < values.length - 1; i++) { if (values[i][j] == values[i + 1][j]) range.getCell(i + 2, j + 1).setValue(''); } }
Improve this question
3
  • 1
    Show expected output for the values array. And what do you mean by merge? Commented Jun 28, 2019 at 9:25
  • to merge the cell from A1 to A4 and have a single value A in it. That part is obvious. What is not obvious is what happens to the the values in Columns B, C, D and E. Commented Jun 28, 2019 at 9:38
  • B1 and B2 will be merged to hold B and B4 to B6 will be merged to have value B in the second column, c3 and c4 will be merged to hold C and so on. The code will Commented Jun 28, 2019 at 10:04

1 Answer 1

Reset to default
2
  • You want to merge cells vertically when the cells have the same values to the vertical direction.

  • You want to achieve the following situation.

  • Input enter image description here

  • Output enter image description here

If my understanding is correct, how about this modification? Please think of this as just one of several answers.

The flow of my sample script is as follows.

  1. Transpose the values.
  2. Start and end addresses for merging cells are retrieved from the transposed values.
  3. Merge cells using the retrieved addresses.

Sample script:

// Retrieve values from the active sheet. var sheet = SpreadsheetApp.getActiveSheet(); var values = sheet.getDataRange().getValues(); // Transpose values. var res1 = values[0].map(function(_, i) {return values.map(function(e) {return e[i]})}); // Merge cells. res1.forEach(function(col, i) { var temp = {}; col.forEach(function(row, j) { if (row === col[j + 1] && !(row in temp)) { temp[row] = j; } else if (row != col[j + 1] && row in temp) { sheet.getRange(temp[row] + 1, i + 1, (j - temp[row]) + 1, 1).merge(); temp = {}; } }); });

Note:

  • This is a simple sample script. So please modify this for your situation.

References:

If I misunderstood your question and this was not the result you want, I apologize.

Edit:

This is the answer for the additional question.

Usage:

  1. Manually select cells "A2:C6" on "Sheet1".
  2. Run the script.

Script:

// Retrieve values from the active sheet. var range = SpreadsheetApp.getSelection().getActiveRange(); var sheet = range.getSheet(); var values = range.getValues(); // Transpose values. var res1 = values[0].map(function(_, i) {return values.map(function(e) {return e[i]})}); // Offset var r = range.getRow() - 1; var c = range.getColumn() - 1; // Merge cells. res1.forEach(function(col, i) { var temp = {}; col.forEach(function(row, j) { if (row === col[j + 1] && !(row in temp) && row != "") { temp[row] = j; } else if (row != col[j + 1] && row in temp) { sheet.getRange(r + temp[row] + 1, c + i + 1, (j - temp[row]) + 1, 1).merge(); temp = {}; } }); });

Result:

Before:

enter image description here

After:

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you Tanaike. This is perfect. But how do I modify the answer to work on the active selection only?
@Code Guy Thank you for replying. About your new question, I'm not sure whether I could correctly understand about your issue. For example, if you select the cells "A1:E6" and you want to run the script for these cells, how about modifying from var values = sheet.getDataRange().getValues() to var values = SpreadsheetApp.getSelection().getActiveRange().getValues(). If I misunderstood your new question, I apologize. If my understanding is not correct, can you post it as new question by including the detail information? By this, I would like to confirm about it.
You can't vertically merge cells that intersect an existing filter. Is the error I get when I run the script
@Code Guy Thank you for replying. At first, can I ask you whether I could correctly understand about your new question?
@Code Guy About the filter, I couldn't see it from your question. So my answer doesn't consider about the filter. I deeply apologize that my answer cannot be used for various situations. About your current situation, unfortunately, I cannot understand. So can you post it as new question by including the detail information and sample Spreadsheet for replicating your new issue? I would like to correctly understand it. I apologize I cannot resolve your new issue soon. By posting it as new question, users including me can think of the solution. If you can cooperate to resolve your issue, I'm glad.
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.