Activate A Certain Sheet When Opening a Spreadsheet

<div>0:00 I'm going to share with you one line of code that solves one small problem that is very much a usability issue if you are sharing your sheet with others uh either selling it or giving it away or even just sharing it with colleagues uh so in this sheet I created a sheet that automatically has a GID of 0:20 zero. Now what is GID? I'll tell you that in a second. And whenever we create a new sheet, we get a new GID. 0:26 Again, I'll share with you what GID is.

And there's a page here called Start, but this Start is not GID. 0:32 It is not the first page of my sheet, but I want to have everyone start here. And I'm going to show you that script, but first I'm going to explain what GID is. 0:48 Sheet.new, if you go there or create a sheet any other way in your Google Drive, you'll see up in the URL that it has the ID of the entire spreadsheet file, and then it has edit hyphen, or sorry, edit hashtag GID equals zero. 1:04 This is the very first ah sheet or tab inside of your entire spreadsheet file.

Every time we click new tab, here we have sheet 5, this is the fifth sheet, we are gonna get a new GID, so up here in the URL bar you can see GID equals 1273159016, it's very different than zero. 1:25 And it's not two, it's not like it's gonna go iterate up 1, 2, 3, it's gonna be a random number and every time we create a new tab you're gonna get a new GID. 1:33 This is an incredibly useful URL because we're gonna share it can, you can do with this is you can share this entire URL with someone who has access to the sheet, either editor or view access or even if you share with anyone in the world. 1:46 If you share this URL with the GID involved, you can share in it.

Uh, we're going to copy that. I'm actually going to open an incognito window and go to that page and now I am on sheet six. 2:01 But if I do GID equals zero, I'm going to be on zero down here. You can probably see it a little bit here. 2:11 But what if we want to create a new sheet and call it start here and have everybody start here? This is a problem because if we share even the URL, the base URL with anyone, where will we start? 2:23 We'll start at the very first tab in the sheet.

This is very problematic if you want people to start on the start tab and it's not the very first tab. 2:33 Now, you're probably saying, hey, why don't you just make the start tab the very first tab? Totally fine. But sometimes people want to start here and then go somewhere else and have at the very beginning something else. 2:46 You may want that. So I'm going to share with you how to activate, and that's a key term here, activate the start tab whenever we open a sheet. 2:54 And it's one line of code. And it looks like this.

It's function on open, which is a, it's a simple trigger that we just type in on open, and it's going to be spreadsheet app dot get active spreadsheet dot get sheet by name. 3:10 We're going to use a start name. That is it. So I'm going to command s. Now, anytime that this sheet opens, and I have access to this app script , without having to actually access the app script , I can go to that start page. 3:24 It'll, will automatically do it. So let's go here.

Actually, I will start on GID equals zero, create a new tab, uh sorry, open in a new tab. 3:35 And now, it's going to automatically activate to the start tab, no matter where I land. So even if I use GID, this 127, create a new tab, go to the sheet, I'm opening the spreadsheet , and it's going to activate that, dot activate. 3:50 There it goes. It's going to always bring me to the start here tab. So, this is pretty cool, right?

We're using function on open, if you haven't ever used this before, you may. 4:02 It may look like this, it might go, it might look like this whenever you go up to extensions Apps Script . 4:10 Okay, and I'm going to show you how, show you how to find this.

So, on any sheet whatsoever, you can go up to extensions Apps Script , open it up, and it's going to have at least some blank function here, function my function . 4:22 What we're going to have to do, uh, in order to use the open trigger without having to install any triggers, we just type this in, take my function and type in open trigger . 4:32 On, and then a capital O-P-E-N, open.

And in here, we're going to do spreadsheet , and once we have the capital S, so if we just do, actually it even shows up autocomplete there, but capital S, spreadsheet , and spreadsheet app. 4:50 Then we're going to do a dot, and anytime after this, we have to put in parentheses.

So we're going to do get active spreadsheet , that's what it looks like, you can type it, or you can use the autofill to find it. 5:02 We have to add at the end here, parentheses, and then another dot, get sheet by name, and inside the parentheses we're going to name the tab. 5:14 Again this is the exact name here, if you have a space at the end you have to put the space here. 5:18 If you don't have any spaces, you have to put the exact text that you got here in between the quotation marks here. 5:26 And the final thing we have to do here is dot activate.

And then end with parentheses.

Hit command S or there's a little b- disc up here you can click save project and now you have the function on open which anytime the spreadsheet will be opened it will go here. 5:44 I will tell you there is one caveat is that the person must have access to the sheet either as a viewer or as an editor . 5:52 If it is non-logged in user then Google Sheets has this protective layer that basically says we are not going to execute any JavaScript or any of this app script unless we know who is doing it. 6:03 umm this helps prevent malicious sheets from doing things.

things that that we don't want them to do uh and helps spread the helps negate the spread of malicious code. 6:16 And so you'll find that if you open this in an incognito window and you're not logged in it won't work. 6:23 uh but. That is just something we have to deal with. So you're sharing the sheet with someone you may want to uh actually have logged in. 6:31 Make sure they're logged in. That was the big caveat here. But I want to give you some more extra stuff here to automate your sheets and automate this particular uh. 6:40 Line of code. We've written this line of code.

You haven't working in your sheet. It- it absolutely works whenever we open any tab. 6:49 Let's go and check it again. So we're just opening the tab and we are gonna get reader and we're to be or activate the sheet that we want. 6:58 But let's say we're somewhere else. Like we're here and we don't know where the start tab is. Maybe we have hundreds of tabs. 7:04 Dozens of tabs here and we want to go to a specific tab. I'm gonna show you one more thing we can do. 7:10 We can add an on open menu.

And we can google for that and I'm just gonna copy the code from google itself. 7:18 It's right here function on open. Make sure we can, we don't need this function on open. We do need this very- variable ui equals spreadsheetapp .getui. 7:30 We can delete this comment and I'm gonna rename this menu go to start. And then I'm gonna add add an item here. 7:45 I'm gonna delete everything else except that add.ui. The first item is I'm gonna say go to start. And we're gonna name a function here. 7:56 We're gonna actually take this. I'm gonna also add it here. Function , go start.

And use the exact same code. But this go start, we're gonna add to this menu item. 8:11 And so whenever we click on go start in our menu, it'll actually go to the start. So let's say give this. 8:16 Again, we have to do command S or save. I'm gonna close this and I'm gonna refresh this so that it opens again. 8:24 And we can even take this, go here, check it if it's gonna do it. It's gonna stay the same.

Alright, we're gonna go to our start. 8:31 But now we- We have this extra, I don't know if you can see over here next to help, we have an extra menu. 8:37 If we click that, we have go to start. So if I'm on zero, click go to start, go to start. 8:43 It's gonna run a script. It will need authorization the very first time you run it. But we will authorize it here with a few clicks and we don't have to do this every single time. 8:52 But let's go to start again. Go to start. We're gonna activate that. So now we have a way to navigate. 8:58 No matter where this tab is.

We have it available in this list but again maybe there's dozens and dozens of tabs. 9:04 And we know we wanna go to the start tab. And so we have this extra menu up here and a custom menu . 9:11 I hope this is very helpful for you to add a little bit of automation and a little bit of cool navigation to your sheet. 9:17 And activate a certain sheet whenever you,</div>