Google Sheets script to merge identical values of a column when a row is added-CodePudding

I manage a table and I have a tab that is the equivalent of an account statement.

I use a script that allows me to merge all the cells of identical value in a column that I execute manually.

In a table B28:N, ordered by date(B), whose range B28:M is filled via several imports with QUERY, the column B contains dates of operation and the column N takes the month and the year of the date of the column B with a formula in ARRAYFORMULA in N27. Here it is:

={"Période / Mois";ARRAYFORMULA(SI($B$26:$B<>"";TEXTE($B26:$B;"mmm")&CAR(10)&TEXTE($B$26:$B;"yyy");""))}

My problem is that when the cells in column M are merged correctly and a row is added and I run the script, the merge is done in a weird way.

I would like that automatically when a new row is added, the script cancels the merge, merges again, and reformats the horizontals lines.

it is possible to center the text ( /- one row) but much more complicated

CodePudding user response：

By script

function mergeCells() {
  const columns = [14]; // These column number is the column "N"
  const sheetName = "BANQUE";  // Please set the sheetname.
  const firstRow = 28
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const rng = sheet.getRange(firstRow, columns, sheet.getLastRow() - firstRow, 1)
  rng.getMergedRanges().forEach(m => m.breakApart())
  const data = rng.getValues().flat()
  let toThere = data.length - 1
  let fromHere = 0
  for (var i = data.length - 1; i > 0; i--) {
    if (data[i] != data[i - 1]) {
      sheet.getRange(firstRow   i, columns, toThere - i   1, 1).activate().merge()
      toThere = i - 1
    }
  }
  i = 0
  sheet.getRange(firstRow   i, columns, toThere - i   1, 1).activate().merge()
}