Get last non-empty cell from range input

0
votes

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:

2
google-apps-scriptgoogle-sheets
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? – soMario
"Real" range e.g. GETLASTNECELL(A4:A) – nathan

2 Answers

1
votes

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.

0
votes

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:

enter image description here

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.