How to make all sheets of a Google spreadsheet show A1 in upper left?

Question

I have a 72 sheet Google spreadsheet and am trying to reset each sheet so that it shows A1 in the upper left when you click on its tab. That is, if a sheet is scrolled downward so that you can't see A1, I want it to scroll back so that you can.

I've tried the following google scripts, but nothing does the job. I got the 4th one (reset4) from here but that didn't work either.

function reset1() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setActiveSelection("A1");
  }
}

function reset2() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setActiveRange(allSheets[i].getRange("A1"));
  }
}

function reset3() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allSheets = ss.getSheets();
  for (var i = 1; i < allSheets.length; i++) {
    allSheets[i].setCurrentCell(allSheets[i].getRange("A1").getCell(1, 1));
  }
}

// ***** CORRECTION: 
// ***** reset4() later found to work. See accepted answer comments for details

function reset4() {
  SpreadsheetApp.getActive().getSheets().forEach(function (s) {
    s.setActiveSelection("A1");
  });
  SpreadsheetApp.flush(); // may still need to refresh page afterward
}

function reset5(){
  var ss = SpreadsheetApp.getActive();
  var sheets = ss.getSheets();
  sheets.forEach(function(sh){
    sh.activate();
    ss.getActiveSheet().getRange("A1").activate();
  });
}

I added the 5th one (reset5) based on the code supplied in the answer by Rafa Guillermo, but that didn't work either. Though the answer by Rafa Guillermo works, I didn't mean for sheets to be reset EVERY time its tab is clicked, but only after the desired function is run.

The animated .gif below shows a test spreadsheet having the last function (reset5) and 3 sheets with may rows and columns. At the beginning of the video, cell A1 (with a red background color) is at the top left corner of each sheet. Then I scroll down and over and select other cells in each sheet so that A1 is no longer seen. After running the function, Sheet3 does have A1 positioned at the top left, as desired. But, the problem is that Sheet1 and Sheet2 do not.

Answer 1

Answer:

You need to use SpreadsheetApp.flush() after you set the active range of each sheet.

_{NB: This answer was edited after more information was provided, the original answer is below the break.}

More Information:

If I understand your situation correctly, you wish to have a function which:

• Does not run on a trigger.
• When run, resets the active cell of all sheets in the Spreadsheet it is attached to to cell A1.

With these parameters, I believe that the function reset4() you provided in your question does in fact work. Now, Google Sheets does have some strange behaviour with bound script files and sometimes the script tab will 'lose' it's bound status to the Spreadsheet if both are open and left unattended for long periods of time; this can be fixed by reloading the Spreadsheet (which, from a UI perspective, will also reset the active cell in all your sheets to A1, but we shall put that aside for now).

Code:

As a modification to the modification of the script in my previous answer, you can run your reset5() function but add a SpreadsheetApp.flush() after the activate() call:

function reset() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var range;
  sheets.forEach(function(sh) {
    range = sh.getRange("A1");
    range.activate();
    SpreadsheetApp.flush();
  });
}

References:

• Class Range - Method activate() | Apps Script

---

Previous Answer:

You can use the onSelectionChange(e) trigger in Apps Script to change the active cell of the current Sheet to A1 when the tab is changed.

More Information:

As per the Simple Triggers documentation:

The onSelectionChange(e) trigger runs automatically when a user changes the selection in a spreadsheet. Most onSelectionChange(e) triggers use the information in the event object to respond appropriately.

Code Example:

You can store the current active sheet in a PropertiesService property and compare it to the new active Sheet when the active selection is changed. From here you can then change the active cell to A1 of that sheet:

function onSelectionChange(e) {
  var lastSheet = PropertiesService.getUserProperties().getProperty("LastActiveSheet");
  if (e.source.getActiveSheet().getName() == lastSheet) {
    return;
  }
  else {
    PropertiesService.getUserProperties().setProperty("LastActiveSheet", e.source.getActiveSheet().getName())
    SpreadsheetApp.getActiveSheet().getRange("A1").activate();
  }
}

References:

• Simple Triggers | Apps Script | Google Developers