google sheets
I have a Chrome bookmark pointing to a time tracking spreadsheet I have created.
Every month I add a new tab for the new month, which becomes the first (leftmost) tab.
Unfortunately this causes my bookmark to "break" in that it will load the previous month's tab (which makes sense as I'm linking to that tab… heh).
Is there a way to indicate in the URL a request for the "first" (leftmost tab), rather than a particular tab ID? Seems like a useful feature…
(Eg, ?tabid=1 or something…)
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.
D3:Cumulative Units:
=ARRAYFORMULA(SUMIF(ROW(B3:B5),"<="&ROW(B3:B5),B3:B5))
G3: Cumulative Quantity:
=ARRAYFORMULA(SUMIF(ROW(F3:F14),"<="&ROW(F3:F14),F3:F14))
H3: Last part unit cost:
=+FILTER(C$3:C$5,IF(G3<=D$3:D$5,C$3:C$5,))
And drag fill.
I3: Previous batch remaining Qty:
=IFERROR(IF(H3<>H2,INDEX(D$3:D$5,MATCH(H2,C$3:C$5,0))-G2,))
And drag fill.
J3: Total unit cost:
=ARRAYFORMULA((F3:F14-I3:I14)*H3:H14+IFERROR(I3:I14*H2:H13))
https://docs.google.com/spreadsheets/d/14Yi0_lykSZBt21vyWIGhM8bGrs7uGauCFrC9jJ2U5CM/edit?usp=sharing
Best Answer
Ok I figured out a simple workaround for this as an answer... pretty obvious but sometimes you have to take the long way around! 😉
If you use the Share > Copy Link feature it will give you a link like this:
Visiting this URL will load you into the leftmost tab (and then do some kind of URL redirect to the leftmost tab's ID).
So just update your bookmark URL to this
/editpath and you are good to go!