How do I know if spreadsheet cells are merged using google apps script

Question

In a Google docs spreadsheet. If cells A1 & A2 are merged, is there a way to confirm they are merged, using google apps script?

There is a merge function in GAS https://developers.google.com/apps-script/class_range#merge

But there is no function or example that shows how to check if cells are merged.

getValues etc just returns an empty string for the cell. e.g. this does not work.

function testMerge() {

  var spreadsheet = SpreadsheetApp.openById('Z3ppTjxNUE........'); 
  var sheet = spreadsheet.getSheets()[0];
  var range = sheet.getRange("A3:A4");
  var values = range.getValues();
  var formulas = range.getFormulasR1C1();
  var formulasA1 = range.getFormulas();

  range = sheet.getRange("A4");
  range.setValue("a");

  range = sheet.getRange("A3:A4");
  var values2 = range.getValues();
  var formulas2 = range.getFormulasR1C1();
  var formulasA12 = range.getFormulas();


  var count = range.getHeight();


}

score 14 · Answer 1 · answered Jan 14 '19 at 07:50

14

Yes, now you can use isPartOfMerge to check.

And for anyone wants to get the value of merged cell:

var value = (cell.isPartOfMerge() ? cell.getMergedRanges()[0].getCell(1,1) : cell).getValue();

Hope it help.

answered Jan 14 '19 at 07:50

eric long

640
5
16

But what is cell? – aNewb Sep 04 '21 at 17:58
my variable `cell` here is the range of the merged cell that you want to get its value. – eric long Sep 09 '21 at 15:04

Adelin · Accepted Answer · 2022-11-11T17:44:54.987

8

range.isPartOfMerge()

It seems the .isPartOfMerge() method was implemented (11 sept 2016).

So you can check if a range is merged as such:

function testMerge() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Sheet1");
  var range = sheet.getRange(1,1,2); // example for cells A1 and A2
  Logger.log(range.isPartOfMerge());
}

Docs https://developers.google.com/apps-script/reference/spreadsheet/range#ispartofmerge

edited Nov 11 '22 at 17:44

answered Oct 08 '15 at 14:14

Adelin

7,809
5
37
65

Good find. ... Am guessing this works with new style sheets and not old style. As the code in the question already checks this idea and shows it fails with old style sheet, but I have not tested it with new style sheets. – eddyparkinson Oct 12 '15 at 00:39
Why do you like the "old approach" better? Totally failed for me; every cell I tested returned "false." isPartOfMerge() was all I needed; perfectly simple. – Atiq Zabinski Nov 05 '22 at 03:56

score 3 · Answer 3 · edited Oct 02 '12 at 12:37

3

For those like me who didn't know the .breakapart() method to unmerge cells: https://developers.google.com/apps-script/class_range#breakApart

Thanks to Henrique for the tip !

edited Oct 02 '12 at 12:37

Andrew Barber

39,603
20
94
123

answered Aug 22 '12 at 18:45

edelans

8,479
4
36
45

score 2 · Answer 4 · answered Jun 14 '12 at 04:14

It's not possible to know that. There's already an issue opened regarding this, but it is quite old and has no activity. You should star it (to keep track of updates and kind of vote for it) and leave a comment to see if you can get some attention.

But what I do when I need to assure merged cells are as I want them is to break everything apart and merge again, just to be sure. Of course, this is not a solution, just an idea that might suit you as well.

score 2 · Answer 5 · answered Mar 25 '15 at 08:49

There seems to be a workaround exploiting the fact that merged cells always return a white background. The code below appears to work for me; I will appreciate it if anyone can confirm.

function testMerged() {
  var WHITE = '#ffffff';
  var NON_WHITE = '#fffffe';
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  range.setBackground(NON_WHITE);
  range.getBackgrounds().forEach(function (row, rowNum) {
    row.forEach(function (col, colNum) {
      if (col === WHITE) { Logger.log('Cell merged at row ' + rowNum + ' col ' + colNum); }
    });
  });
  range.setBackground(WHITE);  
}

score 1 · Answer 6 · answered Aug 22 '13 at 09:21

If anyone here needs a work around for this problem, I wrote a little tool that takes most of the donkey work out of making a key value file of all merged cells from exported HTML.

You can find it here: https://github.com/martinhbramwell/CellMergeWorkAround

I wrote it because I have a small project that depends on being able to fully clone ranges from one spreadsheet to another. Obviously, I have added my vote to wanting the issue fixed.

(Note: I would have added this as a comment to the correct answer, but lack the necessary points.)

eddyparkinson · Answer 7 · 2013-08-01T06:15:59.133

0

Download as HTML: You can download a sheet as HTML, this will include the merged cell info. Not a nice solution, but it works.

Example download url:

docs.google.com/feeds/download/spreadsheets/Export?key=* spreadsheetkey *&exportFor‌mat=html&gid=3

where gid is the sheet gid number. - signin is required via google drive api (see google drive DrEdit Tutorial for details)

edited Aug 01 '13 at 06:15

answered Mar 05 '13 at 02:59

eddyparkinson

3,680
4
26
52

And from the HTML you can verify that which cells are merged? Any insight on that (e.g. they set rowspan)? I'm asking since that was your original question. – Henrique G. Abreu Apr 03 '13 at 13:14
Yes, the HTML contains rowspan and colspan for merged cells. Easy to test, use the URL above, change the key and it will download if you are logged in. (gid is optional, it selects the sheet). – eddyparkinson Apr 03 '13 at 22:42

score 0 · Answer 8 · edited Mar 14 '15 at 08:32

Found this solution, posted here:
https://code.google.com/p/google-apps-script-issues/issues/detail?id=618

But only works for active range

// This is my function to test the active range is merged or not and return
// true or false by passing active range to it
function ismerge() {
  var spread = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spread.getActiveSheet();
  var last_row = sheet.getActiveRange().getLastRow();
  var last_col = sheet.getActiveRange().getLastColumn();

  Logger.log("Last row: %s", last_row);
  var active_row = sheet.getActiveRange().getRow();
  var active_col = sheet.getActiveRange().getColumn();
  Logger.log("Row: %s", active_row);
  if ( (last_row == active_row) && (last_col == active_col) ) {
    Logger.log("Cell not merged");
    return false;
  }
  else {
    Logger.log("Cell merged");
    return true;
  }
}

score 0 · Answer 9 · answered Apr 07 '15 at 08:44

Maybe it's a bit late answer since I didn't see this problem for new version of google spreadsheets. But, I still use old spreadsheets version for my project. So I was needed somehow figure it out.

I need to copy format of previous column for new columns. This is part of code below:

      // trying to find first column with week day (white background)
      var background = '';
      for(var columnIndex = 3; columnIndex<=columnLetters.length; columnIndex++){
        background = sheet.getRange(1, columnIndex).getBackground();
        if(background == 'white' || background == ''){
          tmpRange = columnLetters[columnIndex]+':'+columnLetters[columnIndex];
          Logger.log('tmpRange: '+tmpRange);
          // workaround to be able to copy format if there is some merged cells
          try{
            Logger.log('try');
            // copy format of previous column
            sheet.getRange(tmpRange).copyTo(
              sheet.getRange('B1'), {formatOnly:true}
            );
          }catch(e) {
            Logger.log('continue');
            continue;
          }
          Logger.log('break');
          break;
        }
      }

So, the most important part here is:

try{
  Logger.log('try');
  // copy format of previous column
  sheet.getRange(tmpRange).copyTo(
    sheet.getRange('B1'), {formatOnly:true}
  );
}catch(e) {
  Logger.log('continue');
  continue;
}

If it's impossible to copy those cells it just continue to search others.

How do I know if spreadsheet cells are merged using google apps script

9 Answers9

Linked

Related