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

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

CodePudding user response：

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.

CodePudding user response：

You can easily find the last row with the getLastRow_() utility function attached below. Use it like this:

  const lastRow = getLastRow_(sheet);

If you want to find the last non-empty row in a particular column, give the column number as the second parameter:

  const lastRow = getLastRow_(sheet, 1);

You will need to paste the function code in your script project:

/**
* Gets the position of the last row that has visible content in a column of the sheet.
* When column is undefined, returns the last row that has visible content in any column.
*
* @param {Sheet} sheet A sheet in a spreadsheet.
* @param {Number} columnNumber Optional. The 1-indexed position of a column in the sheet.
* @return {Number} The 1-indexed row number of the last row that has visible content.
*/
function getLastRow_(sheet, columnNumber) {
  // version 1.5, written by --Hyde, 4 April 2021
  const values = (
    columnNumber
      ? sheet.getRange(1, columnNumber, sheet.getLastRow() || 1, 1)
      : sheet.getDataRange()
  ).getDisplayValues();
  let row = values.length - 1;
  while (row && !values[row].join('')) row--;
  return row   1;
}