Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.5k views
in Technique[技术] by (71.8m points)

google apps script - Merge cells with same words

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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 :

enter image description here

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 :

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 :

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()

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...