Get last non-empty cell from range input

Question

Up until now, I was using an answer from SF to fetch the last non empty row from a range:

=INDEX( FILTER( A4:D ; NOT( ISBLANK( A4:A ) ) ) ; ROWS( FILTER( A4:A ; NOT( ISBLANK( A4:A ) ) ) ) )

Since I started using it quite often, I opted for a script instead:

/* E.g. GETLASTNECELL(A4:A) */
function GETLASTNECELL(input) {
 
  if (!Array.isArray(input)) {
    throw new Error("Must be range")
  }
  if (input.map(col => col.length).sort()[0] !== 1) {
    throw new Error("Range must be a single column")
  }
  
  const col = input
    .map(row => row[0])   // get value
    .filter(val => typeof val !== 'undefined' && val.toString().length > 0) // empty
    return col[col.length - 1]
}

The issue is that the function is really slow...Is it just a by-product of Google Scripts lifecycle ? The native formulas approach displays the result in an instant. (Tested on a tiny 40x40 spreadsheet)

Read:

For clarification purposes. You said you tested the formula on a `40x40` spreadsheet. I tested your formula and realized that it works only for a single column. If you see the error message you have `Error("Range must be a single column")`. How do you execute it? What do you pass as a parameter? — Marios, Feb 19 '21 at 17:22

Rubén · Accepted Answer · 2021-02-19T17:00:43.683

1

Yes, custom functions / scripts are slower than their equivalent built-in functions.

Most built-in functions runs on client-side while custom functions / scripts runs on server-side.

edited Feb 19 '21 at 17:00

answered Feb 19 '21 at 16:58

Rubén

34,714
9
70
166

Ah thanks, thought as much :( I wish there was a way to create a native alias or something similar. – nathan Feb 19 '21 at 17:00
You might use Google Apps Script to insert your formulas i.e. create a custom menu. Going further you might use a dialog to ask for the range references or use R1C1 notation :) – Rubén Feb 19 '21 at 17:03

Marios · Answer 2 · 2021-02-19T18:11:00.023

0

Try this approach of passing the range as a string instead and let me know if it works faster for you as it does for me:

function GETLASTNECELL(input) {
  const sh = SpreadsheetApp.getActive().getActiveSheet();
  const lrow = sh.getLastRow();
  const Avals = sh.getRange(input+lrow).getValues();
  const value  = Avals[Avals.reverse().findIndex(c=>c[0]!='')]
  return value;
}

Usage:

Regular function:

function myFunction() {
  const input = "A1:A";
  const sh = SpreadsheetApp.getActive().getActiveSheet();
  const lrow = sh.getLastRow();
  const Avals = sh.getRange(input+lrow).getValues();
  const value  = Avals[Avals.reverse().findIndex(c=>c[0]!='')]
  return value;
}

and execute it from the script editor (click on the run button) or create a macro menu to execute it from the spreadsheet itself.

edited Feb 19 '21 at 18:11

answered Feb 19 '21 at 17:34

Marios

26,333
8
32
52

My lack of understanding was the main issue. I thought there was a way to run gs script locally, just like native functions. – nathan Feb 19 '21 at 18:02
@nathan do you want to run it as a regular function ? Namely, execute it from the script editor or some kind of macro menu ? If yes, let me know and I will modify my answer. – Marios Feb 19 '21 at 18:05
The function is used in the spreadsheet, I don't need to run it manually so this might not the be case. – nathan Feb 19 '21 at 18:21
@nathan does my first code works faster then ? If not, it is fine but I am not sure if you tried it out. – Marios Feb 19 '21 at 18:22
I did, since my spreadsheets aren't really big the performance is pretty much the same (I didn't benchmark but the issue is server-side communication) – nathan Feb 20 '21 at 13:21
@nathan consider upvoting if you found the solution helpful :) – Marios Feb 20 '21 at 13:23

Get last non-empty cell from range input

2 Answers2