Google Spreadsheet cell reference to merged cell

Question

I would like to reference a merged cell and get the value of the merge cell from any of the cells that fall within the merged cell's range.

Using =CONCATENATE(A2, " ",B2) in C1 and dragging it down works for: un-merged cells, and the first cell of a merged cell. It does not work for subsequent cells in a merged group. This works as expected in MS Excel, but not Google spreadsheets, is there a way to achieve this?

Answer 1

I wrote the following script, which works only for merged columns (which is what I wanted):

/**
 * Returns the value at the top of a merged cell. If the merged cell is blank, not at the top of the sheet, and below another merged cell, the the function overflows into the above merged cell and returns incorrect input.
 *
 * @param {int} column Column number of the cell.
 * @param {int} row Row number of the cell.
 * @return The value shown in the merged cell.
 * @customfunction
 */
function FIRST_IN_MERGED_COLUMN(column, row) {
  var sheet = SpreadsheetApp.getActiveSheet();

  var cell = sheet.getRange(row, column);

  if (!cell.isPartOfMerge())
    return cell.getValue();
  else if (cell.getValue() != "")
    return cell.getValue();
  else {
    var r = row;
    var newCell = cell, oldCell = cell;
    while (r > 1) {
      if (!(newCell = sheet.getRange(--r, column)).isPartOfMerge())
        return oldCell.getValue();
      if (newCell.getValue() != "")
        return newCell.getValue();
      oldCell = newCell;
    }
    return ""; // Blank merged cell at top of sheet
  }
}

Unfortunately, if one merged column is directly above another merged column, and the bottom merged column is blank, then the script will use the value of the top merged column.

It is used below, column D shows the formula in column B, and success or error case are shown in column C.

Answer 2

I found a better way:

// example inputs for cell position
var row = 5;
var column = 7;

// Get the range that the cell belongs and then get the displayed value.
var cell = sheet.getRange(row, column);

var result;

if (!cell.isPartOfMerge()) {
  result = cell.getValue();
} else {
  // get the range the cell belongs
  var range = cell.getMergedRanges()[0];

  // getting the column and the row of the top left cell in the range
  var colRef = range.getColumn();
  var rowRef = range.getRow();

  // getting the value
  result = range.getDisplayValue();
}

Answer 3

This is my attempt to collate all of the best attributes of all of the other answers. I've torture-tested it in every way I can think of, and it seems to hold up pretty well. That said, error reports are welcomed.

Usage: call it with the cell reference in quotes: =MERGED_CELL_VALUE("H19"), not =MERGED_CELL_VALUE(H19). I couldn't make the function always raise an informative error if you call it without quotes, since the contents of H19 could easily be A2 or something. But it'll usually complain in a useful way.

If you want to be able to use click-and-pull autofill with this, there's a fairly easy workaround. Use CELL() as so: =MERGED_CELL_VALUE(CELL("address", H19)). Now, H19 is bare, so it'll be updated if you move it around. This trick also works with H$19 and the like.

/**
 * Returns the value at the top of a merged cell.
 *
 * Inspired by the code at:
 * https://stackguides.com/questions/41967357/google-spreadsheet-cell-reference-to-merged-cell
 *
 * @param {str} target The targeted cell, as a string in A1 format.
 * @return The value shown in the merged cell.
 * @customfunction
 */
function MERGED_CELL_VALUE(target) {
  var sheet = SpreadsheetApp.getActiveSheet();
  try {
    var targetRange = sheet.getRange(target);
  } catch(e) {
    if (target == "") {
      throw "Empty target specified.  Did you forget to enclose the targeted cell in quotes?"
    }
    else {
      throw "Invalid target '" + target + "'.  Did you forget to enclose the cell ID in quotes?";
    }
  }

  if (!(targetRange.getNumColumns() == targetRange.getNumRows() == 1)) {
    throw "This function does not work on ranges (e.g. 'A1:B3'), only individual cells.";
  }

  var cell = targetRange.getCell(1, 1); // n.b. Range.getCell() is 1-indexed because FML.

  if (!cell.isPartOfMerge()) {
    // If the cell is not part of a merge, return the cell's value.
    return cell.getValue();
  }
  else if (cell.getValue() != "") { // n.b. this is a special case for getValue(); empty returns "".
    // If the cell has a value, even if it's merged (!!??), return the value.
    return cell.getValue();
  }
  else {
    /*
     * This might turn out to be a bug; we pick getMergedRanges()[0].  Hopefully that matches how GSheets's logic works.
     * There is a getDisplayValue(), but that always returns a String, which is unlikely to fit our goal in
     * all cases.
     */
    return cell.getMergedRanges()[0].getCell(1, 1).getValue();
  }
}

Answer 4

I combined the other answers to this function:

function MERGED_CELL_VALUE(column, row) {
  var sheet = SpreadsheetApp.getActiveSheet();

  var cell = sheet.getRange(row, column);

  if (!cell.isPartOfMerge())
    return cell.getValue();
  else if (cell.getValue() != "")
    return cell.getValue();
  else {
    var range = cell.getMergedRanges()[0];
    return range.getDisplayValue();
  }
}