How do you change which sheet a user is viewing with google app script?

Question

I would like a user to be able to click a button and have their view change to a different sheet.

I have looked through all of the documentation but I can't find any methods that actually cause a UI change. For example getActiveSheet(), setActiveSheet() or setActiveRange() or activate() only change what the focus of the code not what the user can see.

There is a "duplicate" question here: In Google Sheets, how do you change the sheet that the user sees?

but using setActiveSheet() does not result in any changes.

function ChangeSheet(){
var ss = SpreadsheetApp.openById("exampleID");
var MainBoardSheet = ss.getSheetByName('Main board');
ss.setActiveSheet(MainBoardSheet);
}

I have tried a number of variations including setting the active sheet by name and selecting ranges from that sheet but nothing seems to change the view I have.

Cameron Roberts Cameron Roberts · Accepted Answer · 2019-10-29T16:25:29

The functions like setActiveSheet(), setActiveRange(), activateAsCurrentCell() will change the UI view, but only within the active user session in which the script is running, so you must use Spreadsheet.getActiveSpreadsheet(); rather than openById().

You can use these functions from a custom menu or onEdit trigger to change the active sheet in the UI from which they were triggered, but it won't impact other open copies of the sheet, for this or other users.

If you are attempting to trigger via another method, where the code isn't executing in the context of the current users session, you won't see any impact on the UI side.

/* this does not work */
function getByIDJumpByActive(){
  var spreadsheet=  SpreadsheetApp.openById('1u3d8Auoi3CJNqZgLiui2oKIB3FDYOwAHLq59zWY5fNk');
  var sheet = spreadsheet.getSheetByName('Sheet3');
  spreadsheet.setActiveSheet(sheet);
  spreadsheet.toast('opened by id and jumped by active sheet');
}

/* the below all work within the active user session. */
function jumpByRange() {
  var spreadsheet=  SpreadsheetApp.getActiveSpreadsheet();
  var range = spreadsheet.getRange('Sheet2!A1');
  range.activateAsCurrentCell();
  spreadsheet.toast('jumped by active cell');
}

function jumpByActive(){
  var spreadsheet=  SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadsheet.getSheetByName('Sheet3');
  spreadsheet.setActiveSheet(sheet);
  spreadsheet.toast('jumped by active sheet');
}

function onOpen(){
  var doc = SpreadsheetApp.getUi();
  var menu = doc.createMenu('Switch');
  menu.addItem('by range','jumpByRange');
  menu.addItem('by active','jumpByActive');
  menu.addItem('get by ID and jump by active','getByIDJumpByActive');
  menu.addToUi();
}

function onEdit(){
  jumpByActive();
}

How do you change which sheet a user is viewing with google app script?

3 Answers