Google Sheets – Set Selection Across All Sheets

google sheetsgoogle-apps-script

I have a spreadsheet with multiple sheets. I want them all to scroll in sync. For example if I scroll to J18 in Sheet1, all others sheets will have the cursor at J18 and same row/column at the top left.

I know how to write (basic) scripts – but could not figure this one out. Any ideas?

^{Question on Google+ community: https://plus.google.com/113862354064807861446/posts/D7b919WFFjY}

Best Answer

The closest thing you can have is the following script.

Code

function onOpen() {
  SpreadsheetApp.getUi().createMenu("Harmonize")
    .addItem("Go !!", "mySheets").addToUi();
}

function mySheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  // retrieve number of sheets
  var nshs = ss.getNumSheets();

  // find first sheet
  for(var i=0; i<nshs; i++) {
    var sh = ss.getSheets()[i];
    if(sh.getIndex() == 1) {
      var aCell = ss.getActiveCell().getA1Notation();
      var index = i;
    }
  }

  // set active range throughout the sheets
  for(var j=0; j<nshs; j++) {
    if(ss.getSheets()[j].getIndex() != 1) {
      ss.getSheets()[j].setActiveSelection(aCell).activate();
      Utilities.sleep(200);
    }
  }

  // return to first sheet
  ss.getSheets()[index].setActiveSelection(aCell).activate();   
}

Explained

Upon opening, a menu item is created. Selecting this item, will execute the mySheet function. This will find the first sheet (doesn't need to be getSheets[0]) and its index plus the A1Notation(). After that, it will set the active selection of each sheet according to the first sheet.

Example

I've created an example sheet for you: Set Selection throughout all sheets

Related Solutions

Google-sheets – How to use Google Scripts w/ spreadsheet and jotform to auto sort so the newest entry is always listed on the top row

Simple triggers like onOpen and onEdit have restrictions.

In contrary of onOpen and onEdit that are reserved words onChange it's not, but its commonly used to name an installable trigger to be called by the spreadsheet change event. In the same way it's possible to set functions to be called ty the the spreadsheet open and change events which not have the restrictions of simple triggers.

We could create simple triggers manually or programmatically.

For further details please read Triggers and Events Guide.

Google-sheets – Insert column across multiple sheets

You could always create the column in one sheet, and copy-paste it to the other sheets, one sheet at the time. But I guess that's what you're trying to avoid.

It is possible to do this with a Google Apps Script. The following script adds a menu in a spreadsheet, which allows you to insert or remove a column on all sheets.

function addColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Insert column right of column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.insertColumnAfter(colIdx);
  }
}

function removeColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Remove column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.deleteColumn(colIdx);
  }
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Add column on all sheets", functionName: "addColumnOnAllSheets"}, 
    {name: 'Remove column on all sheets', functionName: 'removeColumnOnAllSheets'}
  ];
  sheet.addMenu("Columns", entries);
}

To install this script in your own spreadsheet, click Tools -> Script editor, and paste the code into the script editor. Save the script, and reload the spreadsheet in order to show the menu.

I have set up an example spreadsheet to demonstrate the script.