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;

an example of frozen rows

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.

https://docs.google.com/spreadsheets/d/1eB6PYWmmMEzDlvjidkbLgLkUsk43PFpsK7Mnb8MfNeA/edit?usp=sharing

the behavior of sample spreadsheet (speeded up to reduce the size)

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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create your website with WordPress.com
Get started
%d bloggers like this: