Array Formulas

Array Formulas on Google Sheets are great time savers. Here are some tips and tricks for working with Array Formulas


CONTENTS


ArrayFormula with Header/s

Here is a normal array formula, which you would normally have to put in row 2, and write in a header above it:

=arrayformula(iferror(vlookup(A2:A,lists!$A$2:$E$201,5*sign(row(A2:A)),FALSE)))

However if you put everything in curly braces, and a text at the beginning, you can put the formula in row 1, and a header magically appears with the arrayformula below it.

={"Supervisor";arrayformula(iferror(vlookup(A2:A,lists!$A$2:$E$201,5*sign(row(A2:A)),FALSE)))}

You can have more than one header row as well:

={"Supervisor";"Name";arrayformula(iferror(vlookup(A3:A,lists!$A$3:$E$301,5*sign(row(A3:A)),FALSE)))}


getLastRow() when sheet has Array Formula

The benefits of using array formulas in your spreadsheets are manifold, but when you come to grabbing the data held on those sheets for scripting you are presented with an issue: using the getDataRange().getValues function provided in GAS will return the entire sheet, in terms of rows, and therefore a whole load of unnecessary rows shown in your google visualization table or arrays.

This little helper script will convert your initial array from getDataRange().getValues() to just the data, in the same way it normally works in a sheet without an array formula in it. It will only work on sheets that have array formulas, and these can be anywhere on the sheet.

Because array formulas can be constructed with either “{}” or with arrayformula() we must test for both. It is most likely that the array formula will be in the top rows, but the script provides the option to test as many rows as required, the range could also be amended if necessary.


Some alternatives:

use .getDisplayValues() - (Thank you Michael Walker)

or

var vals = ss.getRange("A1:A").getValues();

var lastrow = vals.filter(String).length;