Go to last line of data in Google Sheets

Question

I have poked around and found the following code that will advance to the last line on data in our Google Spreadsheet- at least until we add more lines beyond row 297.

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var range = ss.getSheets()[0].getRange("B297");
   SpreadsheetApp.setActiveRange(range);
}

I am trying to figure out how to write this script so that it will go to the last line of data, regardless of the line number.

Is there a change that I can make to this code to accomplish this?

Answer 1

The method getLastRow() tells you the last row of a sheet that has data. This can be used to move the selection to that row. Another thing I changed in the sheet selection: your script always operates on the first sheet; it makes more sense to operate on whichever sheet is active currently.

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(sheet.getLastRow(), 1);
  SpreadsheetApp.setActiveRange(range);
}

This can be placed into the spreadsheet menu using onOpen.

By the way, pressing Ctrl + ArrowDown does the same thing, if you do it in a column that has some data in every row (like the date or Id column).

Answer 2

The script below allows you to go to the last cell with the content of column A. It works even if some cells in the column A contain formulas.

Modifying the number in parentheses in lastRowOfCol(1) allows you to reach the last cell with content from another column.

Additionally, you can also change the focus to the first empty cell after the last one with content.

function onOpen(){
  lastRowOfCol(1); //Enter the column number you want to use as the base of the search
}

function lastRowOfCol(column){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var total = sheet.getMaxRows();
  var values = SpreadsheetApp.getActiveSheet().getRange(1,column,total).getValues();
  for(var i=total-1;values[i]=="" && i>0; i--){}
  var last = sheet.getRange(i+1,column);
  //var last = sheet.getRange(i+1,1); //Option to fetch the last row of a given column, but to position in column 1
  //var last = sheet.getRange(i+2,column); //Option to go to the cell below the last one with content 
  sheet.setActiveSelection(last);
}

Script from Marcelo Camargo in this forum