google sheetslinks
I'm curious to know if there's a way to create a Table of Contents within a Google Sheets. I would like to be able to set the active cell in the spreadsheet based on what cell (or contents of the cell like a hyperlink) in the TOC is clicked.
It doesn't look like there is a way to do this with hyperlinks as they only can link you to a spreadsheet but not any specific cell or sheet.
Perhaps some code could capture a cell clicked event and set the active cell accordingly?
The only sense in which a hyperlink can be placed in the middle of a cell is by centering the cell. If what you mean is a cell containing a hyperlink and also other plain text, that is not currently possible.
Parameters can be used in a URL. A URL is simply a string and can be a constant value or can be built up from functions or other cells. For example:
= hyperlink( "https://www.google.com/search?q=" & B2 )
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")
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...
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.
Best Answer
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:
for Link and click Apply.