How to move to the last selected row when you open Google Sheets.

Google Sheets is versatile. I use it for data storage, programming, and also for writing my work journals.

When you use it as a work journal, you will usually freeze the sheet’s top part since the header will be hidden if there are many records. Like this;

However, the more records you have, the more painful it becomes to move to the last row. This is because Google Sheets, unlike Excel, does not remember where you were looking on the sheet last time. So if you open the sheet next time, you will have to scroll the sheet until the last row appears. Don’t you think it’s very bothersome?

How to display the last visible position when the sheet is reopened.

This is where Google Apps Script comes in.

At first, you should save the row number you selected. Since your browser might close unexpectedly, you should save the position each time. You can use PropertiesService to store the data as follows:

function onSelectionChange(e) {
  var range = e.range;
  PropertiesService.getScriptProperties().setProperty("lastRowNo", range.getRow());
}

The onOpen method is triggered when you open your spreadsheet. So you can write the code to achieve our desire there.

function onOpen() {
  var lastRowNo = PropertiesService.getScriptProperties().getProperty("lastRowNo");
  if( lastRowNo != null ){
    jumpRow( parseInt(lastRowNo) );
  }
}
function jumpRow(rowNo){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange(rowNo, 1).activate(); 
}

It’s easy, isn’t it?

I created a sample spreadsheet that demonstrates the above behavior. Feel free to copy the spreadsheet and check the behavior.

Google Sheets takes a little longer to load the sheets and scripts, so it seemed to take a little longer to run the scripts. Still, it’s no problem to use.

Enjoy your Google Sheets life!