Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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?

share|improve this question

migrated from superuser.com Mar 21 '12 at 16:38

This question came from our site for computer enthusiasts and power users.

2 Answers 2

up vote 19 down vote accepted

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.

share|improve this answer
    
Yeah I think this is the only viable solution at the moment. Unfortunately I have not found a way to translate between the name "Sheet5" and the gid=7. –  Simon May 23 '12 at 3:50
    
I'm not sure exactly what you mean. The sheets are numbered in order from the left starting at 0. So if a sheet named "Sheet5" is at gid=7, it should be the eighth sheet from the left. –  William Jackson May 23 '12 at 15:23
1  
What I mean is, you cannot find out programatically what gid Sheet5 is, only manually. If it's position changes, it's gid changes, and you can no longer link to it reliably. –  Simon May 24 '12 at 3:23
    
If you figure out a way to link by name, please post it! –  Joe Casadonte Jul 7 '12 at 16:36
    
There is a programmatic way to get the Id of a sheet, but, bizarrely, this is not the same as the gid in the URL. There is also a programmatic way to activate a sheet, but I'm having a hard time coming up with a suitable trigger for the function. –  William Jackson Jul 25 '12 at 18:15

The best way to do this from my experience is to tie a function to a button/image. The only drawback is that you cannot pass parameters along with a script assigned to a button. Due to this you will need to make a function specific to each navigation but they can call be a in a single script file.

Steps:

Create an image (Insert -> image) and style it to your preference

Create a custom function with the following:

function showSheetByName(Name) {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getSheetByName(Name);
   SpreadsheetApp.setActiveSheet(sheet);
}

and then a function specific to your button

function showSheet5() {
   showSheetByName("Sheet5");
}

Finally assign this function to your button

Assign Script...
showSheet5

You should now be able to click on your button and navigate to "Sheet5". This can also be modified to go to a specific area of the sheet as well.

share|improve this answer
    
Instead of adding a function specific to each button, you can hardcode it to the jump to the sheet named in the "current cell", e.g. change the 2nd line to read var sheet = ss.getSheetByName(ss.getActiveCell().getValue()); - you can then put the "button" on a fixed place on screen and label it "Jump to sheet in current cell" and assign it "showSheetByName" (which now doesn't need a parameter). To use, just highlight the cell with a sheet name (e.g. Sheet5) and click the button. It's a little indirect but works because clicking on a button does not remove the focus from the highlighted cell. –  Motti Strom Jun 6 '13 at 12:32

protected by Community Jul 20 '13 at 7:14

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.