Get the value of a cell in a merged range

Question

I'm iterating over a column in a sheet, which contains merged cells spread over a few rows. E.g.

        |-----
 row 1  |  5
        |-----
 row 2  |
 row 3  |  3
 row 4  |
        |-----
 row 5  |
        |-----
 row 6  |  1
        |-----

I use merging here to show a single value shared by multiple cells.

The problem is that when I'm trying to collect the data and I'm using Range.getValues() only the first cell in the merged range has a non-empty value (in the example ['5','3','','','','','1']), so I can't know which cells are actually empty and which ones belong to a merged range.

The only workaround I found here was color everything and look at the color of the cells (but this is super ugly).

Any nice way so solve this problem?

This was already answered here. http://stackoverflow.com/questions/11025543/how-do-i-know-if-spreadsheet-cells-are-merged-using-google-apps-script — Tesseract, Mar 25 '16 at 23:32

score 1 · Accepted Answer · answered Mar 29 '16 at 12:20

Also possible workaround is to set and then get horizontal allignment.

This code:

function GetMyRange() {
      var sheet = SpreadsheetApp.getActiveSheet();
      var range1 = sheet.getRange("A1:A6");
      range1.setHorizontalAlignment("right");
      var vals = range1.getValues();
      var alignment = range1.getHorizontalAlignments();


      Logger.log(vals);
      Logger.log(alignment);
    }

gives the result:

[[5.0], [3.0], [], [], [], [1.0]]

[[right], [right], [general], [general], [right], [right]]

from starting range:

        |-----
 row 1  |  5
        |-----
 row 2  |
 row 3  |  3
 row 4  |
        |-----
 row 5  |
        |-----
 row 6  |  1
        |-----

"general" aligned cells will give you the answer where merged range is.

I like this thank you. It doesn't rely on someone changing the color of the cells — Andrei Ivanov, Mar 29 '16 at 19:51

Get the value of a cell in a merged range

1 Answers1