Synchronous Scrolling across sheets (tabs) in a Google Sheets Spreadsheet

Question

I am new to Google Sheets, Apps Scripts and JavaScript so kindly bare with me.

I am particularly interested in Google Sheets due to its ability to control user access to specific ranges for data entry purposes.

I have information that I would like split across different sheets in a spreadsheet. As there is a lot of information with details that can be categorized, I am planning to separate the information across different sheets/tabs per category for ease of use.

Due to the expected large number of rows, I wanted to be able to synchronously scroll across these category sheets also for ease of use. I managed to do the same using Macros on Excel, please see this excel file to have an idea of what I am intending to do.

Basically when a cell (or even row) is selected in the active category sheets, the same row is selected in all the other inactive category sheets. Alternatively, when the user selects a category sheet, it should check which row is selected in the previously selected category sheet and select the same row in the newly active sheet.

I was playing with Apps Script but wasn't able to set the selection of an inactive sheet. Similarly, when selecting another category sheet, I was unable to get the selected range from the previously selected category sheet - getSelection() and getActiveRange() always return selection of the current active sheet.

Is there any way I can achieve this? Are there alternative solutions anyone can suggest to reach a similar target? For example I was also looking at the sidebar but that reduces the number of columns viewable ... wanted to minimize horizontal scrolling as that is the main intention of splitting the data between sheets to begin with.

I will greatly appreciate any feedback.

Thanks in advance.

Answer 1

If I understand you correctly, you want to programmatically scroll all sheets to the same row as the active range.

If that's the case, you can just look through all sheets and set the corresponding row (using the rowIndex from the active range) as the active range, using Sheet.setActiveRange(range), as shown here:

function scrollAllSheets() {
  const rowIndex = SpreadsheetApp.getActiveRange().getRow();
  const sheets = SpreadsheetApp.getActive().getSheets();
  sheets.forEach(sheet => {
    const row = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn());
    sheet.setActiveRange(row);
    //SpreadsheetApp.flush();
  });
}

If you want the script to run when a new cell is clicked, use an onSelectionChange(e) trigger. For that, just change your function name to onSelectionChange and, optionally, use the event object (argument e):

function onSelectionChange(e) {
  const rowIndex = e.range.getRow();
  const sheets = e.source.getSheets();
  sheets.forEach(sheet => {
    const row = sheet.getRange(rowIndex, 1, 1, sheet.getLastColumn());
    sheet.setActiveRange(row);
  });
}

Note:

• I don't think it's necessary, but in order to make sure the spreadsheet selection is updated for all sheets, you might find SpreadsheetApp.flush() useful. Uncomment it from the code above if it's not working.
• Using RangeList is not an option here, since it refers to a list of ranges from the same sheet.

Answer 2

Just to expand on what Iamblichus' answer, please note this spreadsheet that has 5 sheets Sheets1-5, of which only Sheets1-3 are to be synced.

I worked on two solutions, one via custom menu Sync Sheets, and the other onSelectionChange. Please note code below:

    // all sheets
var syncSheet1 = "Sheet1"
var syncSheet2 = "Sheet2"
var syncSheet3 = "Sheet3"
var syncSheet4 = "Sheet4"
var syncSheet5 = "Sheet5"

// array of sync sheets
var syncedSheets = new Array (syncSheet1, syncSheet2, syncSheet3);

function onOpen(e) {
  // Add a custom menu to the spreadsheet.
  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu("Sync Sheets")
      .addItem("Sync Sheets","scrollAllSheets")
      .addToUi();

  SpreadsheetApp.getUi() // Or DocumentApp, SlidesApp, or FormApp.
      .createMenu("Goto 10,5")
      .addItem("Goto 10,5","goto10_5")
      .addToUi();

  const prop = PropertiesService.getScriptProperties();
  const sheetName = e.range.getSheet().getSheetName();
  const sheetRowIndex = e.range.getRow();
  prop.setProperty("previousSheet", sheetName);
  prop.setProperty("previousSheetRow", sheetRowIndex);
}

function goto10_5(){
  const row = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(10,5);
  SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().setActiveRange(row);
  // SpreadsheetApp.flush();
}

function scrollAllSheets() {
  const currentRange = SpreadsheetApp.getActiveRange();
  const rowIndex = SpreadsheetApp.getActiveRange().getRow();
  const currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName();
  if (syncedSheets.indexOf(currentSheet) !=  -1){
    const sheets = SpreadsheetApp.getActive().getSheets();
    sheets.forEach(sheet => {
      if (syncedSheets.indexOf(sheet.getSheetName()) != -1){
        const row = sheet.getRange(rowIndex, 1);
        row.setValue(sheet.getSheetName() + " ;row: " + rowIndex);
        sheet.setActiveRange(row);
        SpreadsheetApp.flush();
      }
    });
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentSheet).setActiveRange(currentRange);
  } else {
    currentRange.setValue(currentSheet + " is not a synced sheet");
  }
}

function onSelectionChange(e) {
  const prop = PropertiesService.getScriptProperties();
  const previousSheet = prop.getProperty("previousSheet");
  const previousSheetRow = prop.getProperty("previousSheetRow");
  const range = e.range;
  const a1Notation = range.getA1Notation();
  const rowIndex = range.getRow();
  const sheet = range.getSheet();
  const sheetName = sheet.getSheetName();
  if (sheetName != previousSheet) {
    if (syncedSheets.indexOf(previousSheet) != -1 && syncedSheets.indexOf(sheetName) != -1){
      if (rowIndex != parseInt(previousSheetRow)){
        range.setValue(previousSheet + " row: " + previousSheetRow + "; dif " + rowIndex + " " + sheetName);
        var row = sheet.getRange(parseInt(previousSheetRow),2);
        row.setValue(sheetName);
        sheet.setActiveRange(row);
        // row.activate();
        SpreadsheetApp.flush();
      } else {
        range.setValue("same " + rowIndex + " " + sheetName);
      }
    } else {
      range.setValue("from not synced sheet: " + previousSheet);
    }
      
  } else {
      range.setValue(a1Notation);
  }
  prop.setProperty("previousSheet", sheetName);
  prop.setProperty("previousSheetRow",rowIndex);
  SpreadsheetApp.flush();
}

Many thanks for your assistance Iamblichus. I will let the end users chose which solution to implement.