Google Sheets App Script to change sheet and keep same active cell

Question

I'm writing a script for Google Sheets in App Script. The spreadsheet contains two sheets and each has a button on it. Clicking the button on the first sheet takes you to the second sheet. Clicking the button on the second takes you back to the first.

It sounds simple, but I'm unable to restore the cursor position when I go back to each sheet. It always changes to A1 instead. Here is my code.

function changesheet2() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet2'), true);
};
function changesheet1() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Sheet1'), true);
};

According to the documentation, calling setActiveSheet with parameter true should restore the previous active selection and cursor position. This seems to work OK if you do everything within a single function. It doesn't work when there are two separate functions as here.

It's presumably something about context and who can see what data. I am running both scripts from the same user context by getting the user to click a button in each sheet. How can I make it work?

I see only one script here and two functions. What do you mean you run two scripts ? Did you run both functions separately to see if they are working ? — soMario
The button on sheet1 is connected to function changesheet2(). The button on sheet2 is connected to function changesheet1(). — tenbob

NaziA NaziA · Accepted Answer · 2021-02-24T15:38:32

Note that it will only work if it is in the same function. Since the session still has the data where it was previously at. When the function stops, you don't have a way of knowing where was the previous selection was.

The sample code shows it merely returns to the previous activated cell of the sheet.

// Set the first sheet as the active sheet and select the range D4:F4.
spreadsheet.setActiveSheet(firstSheet).getRange('D4:F4').activate();
// Switch to the second sheet to do some work.
spreadsheet.setActiveSheet(secondSheet);
// Switch back to first sheet, and restore its selection.
spreadsheet.setActiveSheet(firstSheet, true);

What I can recommend you is create another sheet storing their previous active cells before the session ends.

Code:

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = spreadsheet.getSheetByName('Sheet1');
var sheet2 = spreadsheet.getSheetByName('Sheet2');
var record = spreadsheet.getSheetByName("record");

function changesheet() {
  var sheet = spreadsheet.getActiveSheet();
  var sheetName = sheet.getSheetName();
  var selection = sheet.getActiveCell().getA1Notation();
  if(sheetName == sheet1.getSheetName()){ // if in sheet1, go to sheet2 
    spreadsheet.setActiveSheet(sheet2);
    var sheet2LastActiveCell = record.getRange("A2").getValue();
    // if sheet2 was previously recorded, get it's last active cell
    // else, go to A1 (default cell)
    if(sheet2LastActiveCell)
      sheet2.getRange(sheet2LastActiveCell).activateAsCurrentCell();
  }
  else if(sheetName == sheet2.getSheetName()){ // if in sheet2, go to sheet1 
    spreadsheet.setActiveSheet(sheet1);
    var sheet1LastActiveCell = record.getRange("A1").getValue();
    // if sheet1 was previously recorded, get it's last active cell
    // else, go to A1 (default cell)
    if(sheet1LastActiveCell)
      sheet1.getRange(sheet1LastActiveCell).activateAsCurrentCell();
  }
  else // don't do anything if not in sheet1 or sheet2
    return;
  // record current cell of previous sheet
  recordSheet(sheetName, selection);
}

function recordSheet(sheet, range){
  if (sheet == sheet1.getSheetName())
    // record sheet1 active cell in A1
    record.getRange("A1").setValue(range)
  else 
    // record sheet2 active cell in A2
    record.getRange("A2").setValue(range)
}

Additional Notes:

You can hide the record sheet after creating it.
If not yet recorded, it will go to A1 by default.
getActiveCell will always get the active cell of the active sheet. It can't get the active cell of non-active sheets.

Google Sheets App Script to change sheet and keep same active cell

1 Answers

Code:

Additional Notes: