google apps script - Merge cells with same words

Question

I created an array containing multiple rows and columns. In column B, I have the type of vegetables, and in column c, the varieties. In the array, there is an automatic sorting which is carried out in column B then column c.

Is it possible to merge automatically the cells of column B containing the same type?

I just know how to merge a range with :

var range1 = sheet.getRange("b4:b9");
range1.merge();

A test array here

Cordially.

Answer 1

How about this sample script? I think that there are several answers for this situation, so please think of this as one of them. The flow of this script is as follows.

Flow :

1. Retrieve values of column B.
2. Retrieve the number of duplication values.
3. Merge cells.

Sample script :

function myFunction() {
  var start = 4; // Start row number for values.
  var c = {};
  var k = "";
  var offset = 0;
  var ss = SpreadsheetApp.getActiveSheet();

  // Retrieve values of column B.
  var data = ss.getRange(start, 2, ss.getLastRow(), 1).getValues().filter(String);

  // Retrieve the number of duplication values.
  data.forEach(function(e){c[e[0]] = c[e[0]] ? c[e[0]] + 1 : 1;});

  // Merge cells.
  data.forEach(function(e){
    if (k != e[0]) {
      ss.getRange(start + offset, 2, c[e[0]], 1).merge();
      offset += c[e[0]];
    }
    k = e[0];
  });
}

Result :

Note :

• This sample script supposes that the values of column B is sorted.
• From your shared spreadsheet, it supposes that there are the values for merging cells at column B.
• In your shared spreadsheet, the order of sorted values is Composées, Cucurbitacées, Légumineuses, Liliacées, Solanacées. On the other hand, the order of "Wish" is Composées, Cucurbitacées, Légumineuses, Solanacées, Liliacées.
  • I couldn't understand the difference logic between Liliacées, Solanacées and Solanacées, Liliacées.
  • In this sample script, it uses the order of sorted values.

Reference :

• merge()

If I misunderstand your question, I'm sorry.

Edit 1 :

For your next question, I think that the following flow can achieve what you want. But I think that there may be other solution.

1. Add values by an user.
2. Break the merged cells using breakApart().
   • For example, it merges cells of "A1:A3" which have the values of "sample", "sample", "sample". When this merged cell is broken using breakApart(), each value of "A1:A3" retrieved by getValues() becomes [["sample"],[""],[""]].
3. Fill the empty cells created by breakApart().
4. Sort the cells.
5. Run the sample script on my answer.

Reference :

• breakApart()

Edit 2 :

Usage of breakApart():

If you want to break "B1:B", please use as follows.

var ss = SpreadsheetApp.getActiveSheet();
ss.getRange("B1:B").breakApart()