Passing an array into a custom function wrapped in an ArrayFormula?

Question

I have a spreadsheet that contain columns that use merged cells for formatting reasons. I am trying to create columns that mirror this first set of columns but used the merged cell value on all of its affected rows. I can do this thanks to a custom function that I found online. What I can't do is then contain this within an arrayformula and I'm not sure why.

Here is a small version of the spreadsheet: https://docs.google.com/spreadsheets/d/1mp8PpgO4sI60bbx__1L4a17qL1VGIB9QVB910vTyhg0/edit?usp=sharing

The custom function is:

/**
* Takes into account merged cells and returns the value of the merged cell 
* for all the cells within the merged range, rother than just the top left 
* cell of the merged range.
*
* Copied from https://webapps.stackexchange.com/questions/110277/how-do-i-reference-the-values-of-merged-cells-in-formulas
*
* Used by Patrick Duncan. - 7 May 2018
*/

function cellVal(cellAddress) {
  var cell = SpreadsheetApp.getActiveSheet().getRange(cellAddress);  
  return (cell.isPartOfMerge() ? cell.getMergedRanges()[0].getCell(1, 1) : cell).getValue();
}

The formula without the Arrayformula is:

=cellVal2(index(address(row(),5,4)))

And what I was trying was:

=arrayformula(cellVal2(index(address(row(E3:E),5,4))))

Any idea what I'm doing wrong here?

Cheers,

Patrick

Answer 1

How about this modification? I think that there are several solutions for your situation. So please think of this as one of them.

Modification points :

• When index(address(row(E3:E30),5,4)) is given to cellAddress, cellAddress is [["E3"], ["E4"], ["E5"],,,]. This is a 2 dimensional array.
  • Flatten this 2 dimensional array for getRangeList().
• Convert the flattened array to the range array using getRangeList().
• Retrieve each value using the converted range and return them.

Modified script :

function cellVal3(cellAddress) { // Modified the function name to "cellVal3"
  cellAddress = Array.prototype.concat.apply([], cellAddress);
  var cells = SpreadsheetApp.getActiveSheet().getRangeList(cellAddress).getRanges();
  return cells.map(function(cell){return [(cell.isPartOfMerge() ? cell.getMergedRanges()[0].getCell(1, 1) : cell).getValue()]});
}

Usage :

When you use this custom function, please use as follows.

=ARRAYFORMULA(cellVal3(index(address(row(E3:E30),5,4))))

or

=cellVal3(index(address(row(E3:E30),5,4)))

References :

• Array.prototype.concat()
• getRangeList()

If I misunderstand your question, I'm sorry.