Find first empty row and scroll to it, Lastrow not working due to formulas

I want to auto scroll to the first row after a given row number that has column A empty unloading the sheet (This Google Sheets)

the following formula works but scrolls past the last empty row as I assume if a row has formulas that add a blank in a cell it is counting them as not empty

 function onOpen(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheetname = ss.getSheets()[0].getName();
 // Logger.log("DEBUG: sheetname = "+sheetname)
 var sheet = ss.getSheetByName(sheetname);
 var lastRow = sheet.getLastRow();
 var range = sheet.getRange(lastRow,1);
 sheet.setActiveRange(range);
}

So I have tried modifying thus, but it doesn't scroll down

function onOpen(){
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheetname = ss.getSheets()[0].getName();
 // Logger.log("DEBUG: sheetname = "+sheetname)
 var sheet = ss.getSheetByName(sheetname);
 var lastRow = sheet.getRange('A7:A').getLastRow() + 1;
 var range = sheet.getRange(lastRow,1);
 sheet.setActiveRange(range);
}

column A is empty at row 600 say but the sheet doesn't scroll on loading

Solution

Try this, instead of the build-in .lastRow() method,

you can instead use .findIndex() with .reverse() and .length methods to find the last non-empty row number of a column.

function onOpen(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetname = ss.getSheets()[0].getName();
  // Logger.log("DEBUG: sheetname = "+sheetname)
  const sheet = ss.getSheetByName(sheetname);
  const values = sheet.getRange('A7:A').getValues();
  const lastRow = values.length - (values.reverse().findIndex(row => !!row[0]));
  const range = sheet.getRange(lastRow,1);
  sheet.setActiveRange(range);
}

But, if you are looking for the first empty row, you may consider to change the lastRow variable:

function onOpen(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheetname = ss.getSheets()[0].getName();
  // Logger.log("DEBUG: sheetname = "+sheetname)
  const sheet = ss.getSheetByName(sheetname);
  const values = sheet.getRange('A7:A').getValues();
  // const lastRow = values.length - (values.reverse().findIndex(row => !!row[0]));
    const firstEmptyRow = values.findIndex(row => !row[0]) + 1;
  // const range = sheet.getRange(lastRow,1);
    const range = sheet.getRange(firstEmptyRow,1);
  sheet.setActiveRange(range);
}

By the way, declare variables with const whenever you could, and let when you couldn't, is a better practice than using var in JavaScript.