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

I should add that the reason I want to wrap this formula in an ArrayFormula is so that if subsequent users insert additional rows to the spreadsheet, the formula will still apply without them having do do anything. This will mean that I can hide my mirroring columns. — Patrick
– Patrick, Commented May 7, 2018 at 6:11

Tanaike · Accepted Answer · 2018-05-07 07:02:24Z

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 :

If I misunderstand your question, I'm sorry.

Collectives™ on Stack Overflow

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

1 Answer 1

Modification points :

Modified script :

Usage :

References :

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modification points :

Modified script :

Usage :

References :

Comments

Your Answer

Sign up or log in

Post as a guest

Related