Activate A Certain Sheet When Opening a Spreadsheet

0:00 I'm going to share with you one line of code that solves one small problem, but this is very much a usability issue. If you are sharing your sheet with others, either selling it or giving it away, or even just sharing it with colleagues. So in this sheet, I created A sheet that automatically has a GID of 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. Again, I'll show, share with you what GID is. And there's a page here called start, but this start is not GID zero. It is not the first page of my sheet, but I want to have everyone start here. So how do we get someone to start here? And I'm going to show you that script, but first I'm going to explain what GID is. It's really cool. When we start a new sheet, so sheet. new, if you go there or create a sheet any other way in your Google Drive, Activate A Certain Sheet When Opening a Spreadsheet Make It Easier For Others To Use Your Google Sheets Get Sheet ID and Tab GID Into Your Google Sheet Find Your Spreadsheet ID in Google Sheets

0:45 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. This is the very first sheet or tab inside of your entire spreadsheet file. Every time we click new, tab. Here we have sheet five. This is the fifth sheet. We are going to get a new GID. So up here in the URL bar, you can see GID equals 6. It's very different than zero. And it's not too, it's not like it's going to go iterate up 1, 2, 3. It's going to be a random number . And every time we create a new tab, you're going to get a new GID. This is an incredibly useful URL because what can you can do with this is you can share this entire URL with someone who to the sheet, either editor or view access , or even if you share with anyone in the world. Activate A Certain Sheet When Opening a Spreadsheet Make It Easier For Others To Use Your Google Sheets Get Sheet ID and Tab GID Into Your Google Sheet Find Your Spreadsheet ID in Google Sheets

1:33 If you share this URL with the GID involved in it, we're going to copy that. I'm actually going to open incognito window and go to that page. And now I am on sheet six. But if I do GID equals zero, I'm going to be on zero down here. You can probably see it a little bit. See a little bit. 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? 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. 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 Activate A Certain Sheet When Opening a Spreadsheet Make It Easier For Others To Use Your Google Sheets Make a Tour of Your Sheet How to Add Shortcut to Tab in Google Folder

2:20 at the very beginning something else. 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 want. Open a sheet 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. We're going to use a start name dot activate. 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. It'll will automatically do it. So let's go here. Actually, I will start on GID equals zero, create a new tab. Sorry, open in a new tab. And now it's going to automatically activate to the start tab. Okay. No matter where I land. Activate A Certain Sheet When Opening a Spreadsheet Getting Started Coding in Apps Script Activate A Certain Sheet When Opening a Spreadsheet Jump to Specific Cell Automatically

3:05 So even if I use G ID, this one, two, seven, create a new tab, go to the sheet, I'm opening the spreadsheet and it's going to activate that dot activate. 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 it may look like this. It might go, it might look like this whenever you go up to extensions, Apps Script . 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 , what we're going to have to do in order to use the open trigger without having to install any triggers, we just type this in, take my function and type in 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. Activate A Certain Sheet When Opening a Spreadsheet Getting Started Coding in Apps Script Activate A Certain Sheet When Opening a Spreadsheet Create Button in Sheets to Other Sheets

3:52 So if we just do actually even shows up auto complete there, but capital S spreadsheet and Spreadsheet app, 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 . So it looks like you can type it or you can use the autofill to find it. 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 spreadsheet . the tab again. This is the exact name here. If you have a space at the end, you have to put the space here. If you don't have any spaces, you have to put the exact text that you got here in between the quotation marks here. 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 Disco up here. You can click save project and now you have the function on open Activate A Certain Sheet When Opening a Spreadsheet Getting Started Coding in Apps Script Activate A Certain Sheet When Opening a Spreadsheet How to Retype Header Automatically

4:40 which anytime this spreadsheet will be opened It will go here. 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 . If it is a non logged in user, then Google sheets has this protective layer that basically says we're not going to execute any Java script or any of this app script unless we know who's doing it. This helps prevent malicious sheets from doing things that we don't want them to do and, and, and. It helps spread the, helps negate the spread of malicious code. And so you'll find that if you open this in an incognito window and you're not logged in, it won't work. But that is just something we have to deal with. So when you're sharing the sheet with someone, you may want to actually have logged in, 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. Activate A Certain Sheet When Opening a Spreadsheet When did you learn the secrets of Google Sheets? | Sheet Talking Episode 8 Esa Make Spreadsheet Private OnlySheets

5:28 Line of code. We've written this line of code. You have it working in your sheet. It, it absolutely works. Whenever we open any tab, let's go and check it again. So we're just opening a tab and. We are going to get redirected or activate the sheet that we want, 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, dozens of tabs here, and we want to go to a specific tab. I'm going to show you one more thing we can do. We can add an on open menu and we can Google for that. And I'm just going to copy the code from Google itself. It's right here. Function on open. Actually, we can, we don't need this function on open. We do need this variable UI equals spreadsheet app dot get UI. I can delete this comment and I'm going to rename this menu. Go to start. And then I'm going to add an item here. Activate A Certain Sheet When Opening a Spreadsheet Learn to Code in Google Sheets, For Programmers | For Advanced Google Sheet Users Activate A Certain Sheet When Opening a Spreadsheet How to Add Shortcut to Tab in Google Folder

6:15 I'm going to delete everything else except that add dot UI. The first item is I'm going to say go to start. And we're going to name a function here. We're going to actually take this. I'm going to also add it here. Function . Function Go start and use the exact same code, but this go start, we're going to add to this menu item. And so whenever we click on go start in our menu, it'll actually go to the start. So let's save this. Again, we have to do command S or save. I'm going to close this and I'm going to refresh this so that it opens again. And we can even take this, go here, check it, if it's going to do it, it's going to stay the same, right? We're going to go to our start, but now we have this extra, I don't know if you can see over here next to help. We have an extra menu. If we click that, we have go to start. So if I'm on zero, click, go to start, go to start, it's going to run a script. Activate A Certain Sheet When Opening a Spreadsheet Custom Menu - 2 functions Advanced Coding Tips in Google Sheets for Programmers

7:01 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, but let's go to start again. Again, go to start. And activate that. So now we have a way to navigate no matter where this tab is. We have it available in this list, but again, maybe there's dozens and dozens of tabs here and we know we want to go to the start tab. And so we have this extra menu up here in a custom menu . And I hope this is very helpful for you to add a little bit of automation, a little bit in cool navigation to your sheet and activate a certain sheet whenever you open your spreadsheet file. If you're watching this on YouTube, check out better sheets.co become a member, and you can get the spreadsheet file you see in this tutorial. In fact, every tutorial at bettersheets.co. Includes the spreadsheet file you see in the tutorial. Activate A Certain Sheet When Opening a Spreadsheet Make It Easier For Others To Use Your Google Sheets Activate A Certain Sheet When Opening a Spreadsheet How to Create a Custom Automation Menu in Google Sheets

7:47 Become a master of Google sheets. at your own pace. Automate Google Sheets With Zero Experience How to Gain Confidence in Google Sheets Automate Google Sheets With Zero Experience How To Sell Google Sheets For Fun And Profit