Google Sheets – Link to Specific Sheet

google sheetsgoogle-apps-script

I have a complicated Google spreadsheet with many sheets and a table of contents. Is there some way to create a link to the sheet names so that with a click one can go directly to the sheet? That is: clicking on the cell "sheet5" switches to sheet5?

Best Answer

When you switch to a different sheet in Google Spreadsheets, pay attention to the URL in your browser's address bar. At the end of the URL you should see something like:

#gid=0

This number changes when you switch sheets, and specifies which sheet to display. Copy the entire URL and create a hyperlink to it with this formula:

=hyperlink("https://docs.google.com/spreadsheet/ccc?key=0AsaQpHJE_LShcDJ0dWNudHFZWVJqS1dvb3FLWkVrS0E#gid=0", "LINK TEXT")

With a script

I've thought about this question a lot since I first wrote this answer, and I came up with a solution that involves a script.

With the spreadsheet open, click the Tools menu, then Script editor.... Paste all this code into the editor:

function onOpen(event) {
  var ss = event.source;
  var menuEntries = [];
  menuEntries.push({name: "Go to sheet...", functionName: "showGoToSheet"});
  ss.addMenu("Tasks", menuEntries);
}

function showGoToSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var allsheets = ss.getSheets();
  var app = UiApp.createApplication();
  app.setTitle("Go to sheet...").setWidth(800).setHeight(600);
  var table = app.createFlexTable();
  table.setCellPadding(5).setCellSpacing(0);
  var goToSheetClick = app.createServerHandler('handleGoToSheetClick');
  var widgets = [];
  for (var i = 0; i < allsheets.length; i++) {
    var sheet_name = allsheets[i].getName();
    widgets[i] = app.createHTML(sheet_name).setId(sheet_name).addClickHandler(goToSheetClick);
    table.setWidget(i, 1, widgets[i])
  }
  var panel = app.createSimplePanel();
  panel.add(table);
  app.add(panel);
  ss.show(app);
}

function handleGoToSheetClick(e) {
  var sheet_name = e.parameter.source;
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheet_name);
  sheet.activate();
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

Save the script, then refresh the spreadsheet. After a second or two a new menu, Tasks, will appear after Help. There is one item in this menu: Go to sheet...

Tasks > Go to sheet...

This menu item will open a panel with a list of names of all the sheets in the current spreadsheet. It doesn't look like it, but if you click on one of the sheet names, that sheet will come to the front.

As an answer to another question, this script was improved to include a scrollable view and buttons.

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

The following piece of script will create a menu item in the spreadsheet named Maintenance. There are three menu option present named:

Add Student one by one, Add Student's at once (FIFO), Add Student's at once (LIFO), Delete Sheets

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [{name: "Add Student one by one", functionName: "addSheet"},
    {name: "Add Student's at once (FIFO)", functionName: "addSheets"},
    {name: "Add Student's at once (LIFO)", functionName: "addSheetsLIFO"},
    {name: "Delete Sheets", functionName: "delSheets"}
  ];
  ss.addMenu("Maintenance", menuEntries);
}

The next piece of code will create a new sheet, only if all information is entered properly. Once the information is entered, select the student that needs to be added and press Add Student:

function addSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var row = sh.getActiveRange().getRowIndex();
  var rData = sh.getRange(row, 1, 1, 3).getValues();

  if (row == 1) {
    ss.toast("This is the header");    
    return
  }

  if(rData[0][0] != null || rData[0][1] != null || rData[0][2] != null) {
    try {      
      ss.insertSheet(rData[0][2]);
      ss.setActiveSheet(ss.getSheets()[0]);
      sh.getRange("D"+(row)).setValue(new Date());
    } catch(e) {
      throw 'This student allready has a sheet. Try another sheet name.';      
    }
  }
}

The third piece of code will create new sheets, based on the sheet names, press Add Student's at once (FIFO):

function addSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [];    
  for(var i=1, len=rData.length; i<len; i++) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The fourth piece of code will create new sheets, based on the sheet names, but by itterating backwards, press Add Student's at once (LIFO):

function addSheetsLIFO() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getActiveSheet();
  var rData = sh.getDataRange().getValues();

  var message = [], i=rData.length; 
  while(i--) {
    if(rData[i][3] == null || rData[i][3] == "") {   
      if(rData[i][0] != null || rData[i][1] != null || rData[i][2] != null) {
        try {      
          ss.insertSheet(rData[i][2]);
          ss.setActiveSheet(ss.getSheets()[0]);
          sh.getRange("D"+(i+1)).setValue(new Date());
        } catch(e) {
          message.push("row " + (i+1));
        }
      }    
    }
  }
  ss.toast("These sheets allready exist: " + message);
  ss.setActiveSheet(ss.getSheets()[0]);
}

The last script is a bonus to delete all the created sheets, execpt the first sheet, press Delete Sheets:

function delSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var shs = ss.getNumSheets();

  for(var i=shs-1;i>0;i--){
    ss.setActiveSheet(ss.getSheets()[i]);
    ss.deleteActiveSheet();
  }
  ss.setActiveSheet(ss.getSheets()[0]);
  ss.getRange("D2:D").clear();
}

I've prepared an example file, to see things happening: Add Student to New Sheet.

Google Sheets – Creating a Table of Contents

I guess further progress given the time elapsed because what I think you require is very simple. Select A1 in Sheet1, click on the link icon in the menu bar, enter:

Sheet2!B2

for Link and click Apply.

Best Answer

With a script

Related Solutions

Google-sheets – Auto create and name new sheet in Google Spreadsheet

Google Sheets – Creating a Table of Contents

Related Topic