GAS – 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.

What is going on? First we feed the dataValues into the getLastRow script. This concatenates each row to a string, removes any commas, and tests the length. If length is greater than 1 it moves on to the next row, if less than 1 the script stops and returns the number of the last row that contains data. This might be all you need, but I have added a further helper script, popItems(), that removes all the empty array elements, using the pop() function, leaving you with just the data you were after.

Credits:
Jean-Pierre Verhulst
https://productforums.google.com/forum/#!topic/docs/-Xr6dud_Nak

OK, we then ask ourselves, how do we know if the sheet contains an array formula? Well, there is a little script to do that too, that can then be incorporated to the workflow:

Because array formulas can be constructed with either “{}” or with arrayformula() we must test for both. It is most likely that the arrayformula 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.

3 thoughts on “GAS – getLastRow() when sheet has Array Formula

  1. Pingback: Google Visualization – Pick and Choose… | METRIC RAT

    • Yes, have used that too. Also another way:
      var vals = ss.getRange("A1:A").getValues();
      var lastrow = vals.filter(String).length;

Leave a Reply

Your email address will not be published. Required fields are marked *