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.

Daily Schedules and Results
DateKindSchedules or ResultsRemark
7/1/2021WorkSomething
7/2/2021BlogSomething
7/3/2021Work
7/4/2021Blog
7/5/2021
7/6/2021WorkSomething
7/7/2021BlogSomething
7/8/2021Work
7/9/2021Blog
7/10/2021WorkSomething
7/11/2021BlogSomething
7/12/2021Work
7/13/2021Blog
7/14/2021WorkSomething
7/15/2021BlogSomething
7/16/2021
7/17/2021Work
7/18/2021BlogSomething
7/19/2021WorkSomething
7/20/2021Blog
7/21/2021WorkSomething
7/22/2021BlogSomething
7/23/2021WorkSomething
7/24/2021BlogSomething
7/25/2021BlogSomething
7/26/2021WorkSomething
7/27/2021BlogI selected this row yesterday !!

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!

No responses yet

Leave a Reply

Your email address will not be published.