Google Spreadsheet opening the last edited cell using script

Question

Using the following code allows the last edited cell to open when reopening a google spreadsheet.

function setTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger("myFunction").forSpreadsheet(ss).onEdit().create();
}

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sName = sheet.getName();
  var currentCell = sheet.getActiveCell().getA1Notation();
  UserProperties.setProperty("mySheetName", sName);
  UserProperties.setProperty("myCell", currentCell);
}

function onOpen() {
  var lastModifiedSheet = UserProperties.getProperty("mySheetName");
  var lastModifiedCell  = UserProperties.getProperty("myCell");
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName(lastModifiedSheet).getRange(lastModifiedCell).activate();
}

I have a cell which has

=today()

Which updates so the code doesn't open the last row of my spreadsheet, but opens in the cell that updates with today().

How would I update the script to open the spreadsheet at my last edited cell in the last row of the spreadsheet?

Chris Chris · Accepted Answer · 2013-12-13T15:41:04

Can you try this:

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var sName = sheet.getName();
  var currentCell = sheet.getActiveCell().getA1Notation();
  var temp = "";
  if (currentCell != "A1") {temp = currentCell;} 
 UserProperties.setProperty("mySheetName", sName);
  UserProperties.setProperty("myCell", temp);
}

I added a temp variable and am doing a check (assuming only A1 has the today() function) before updating temp. Seems to work.

Google Spreadsheet opening the last edited cell using script

1 Answers