Create a simple script to open a particular tab. In this case we're opening to a random motivational quote each time.
Academy ↓
Hey there stranger!
Sign up to get access.
Open Spreadsheet to Motivational Quote Automatically
About this Tutorial
Sheet Resources
Video Transcript
0:00 So in this video, we're going to create a app script that automatically opens a certain spreadsheet in your script in your sheet And then we'll display this motivational quote which will be totally random each and every time we're going to use this index, rand, and count all formulas to do this We already
0:17 have a list of all the spreadsheet, all the motivational quotes in our spreadsheet in a tab called Quotes And the tab we want to open is called Open Me So let's start off with just getting this text here And then we'll just go into the Apps Script.
0:33 We're going to do equals index And index is just going to get a reference and we're going to get quotes Uh we know it's a colon a And we want to know which row to go to.
0:46 Well this row is going to be random It's going to be rand And we're going to multiply it By the number of Uh items in that list Meaning rand is actually only A number between 0 and 1 So we need to multiply it by Uh a number to get like a whole number We're going to get use count all or count A I like
1:09 to say And we're going to quotes same index as before We also can round this if we want to, round up if we want And we can hit enter and we're going to get this random quote every single time Uh, this pool will re-randomize if we do some kind of edit or anything on the sheet But the moment we open the
1:31 sheet it's going to be one quote and then we don't want to see it anymore We want to sort of just go and use our sheet But when we open the sheet, we want it to open here Why is this important is because sometimes when we're creating sheets Uh, we can create the very first tab we create is going to be
1:48 g id equals zero Even though the entire spreadsheet file is at this url and the very first tab in the sheet will usually be the one that the sheet opens on So if I open this in another tab, you'll see without the g id, I will show up on quotes or I can just leave If I want to show up on sheet 2, I include
2:11 this g id up here in the url and I can open a new tab. This url has the g id already here and if I go to that, it'll go to sheet 2.
2:21 But no matter what, I want this sheet to open on openme every single time. So I'm going to show you how to do that.
2:28 Go to extensions app script and we're going to write a little app script. So here in our app script, we're going to use function on open.
2:35 Function on open is a built in trigger that every single time this spreadsheet opens, we, we can even refresh it and it will execute here.
2:45 So what are we going to do? We're going to do spreadsheet dot spreadsheet app dot get active spreadsheet dot get sheet by name and the sheet we're going to name is openme which is the tab that we want to open and we're going to do activate.
2:59 That is it. So we're going to save that. We can hit the save project appear or command s. I'm going to close this and now if I go to this page in this GID it will ah open this page.
3:12 This tab. But if I just take the blank ah without the GID, the URL without the GID, I'll go to that and see what happens.
3:23 So we open up, it's on quotes right? And now it's automatically opening me into OpenMe. So no matter what, even if I go to quotes and include this, maybe this sheet 1, include this GID, as before this should go to sheet 1 and it will open at sheet 1 but it will automatically activate the sheet that I
3:42 want to start on. So I'll go here. It'll open up, and once it opens and runs that script, I'm now in OpenMe.
3:49 So this is a really simple Apps Script over here, uh, that will allow you to activate whichever tab you want, uh, based on the name, as, whenever it's needed.
4:00 anyone opens the sheet. And just to review, we're using the function onOpen, which is a simple trigger. It's already built in.
4:06 We just have to use the correct nomenclature here. Function onOpen with a capital O, well, lowercase O for on and uppercase for O, and SheetApp.getActiveSpreadsheet, we're getting the entire file, we're then getting the sheet by name, whichever sheet you want to open, and then we're doing .activate.
4:23 And that's it. That's how you automatically open to a particular tab.
0:17 have a list of all the spreadsheet, all the motivational quotes in our spreadsheet in a tab called Quotes And the tab we want to open is called Open Me So let's start off with just getting this text here And then we'll just go into the Apps Script.
0:33 We're going to do equals index And index is just going to get a reference and we're going to get quotes Uh we know it's a colon a And we want to know which row to go to.
0:46 Well this row is going to be random It's going to be rand And we're going to multiply it By the number of Uh items in that list Meaning rand is actually only A number between 0 and 1 So we need to multiply it by Uh a number to get like a whole number We're going to get use count all or count A I like
1:09 to say And we're going to quotes same index as before We also can round this if we want to, round up if we want And we can hit enter and we're going to get this random quote every single time Uh, this pool will re-randomize if we do some kind of edit or anything on the sheet But the moment we open the
1:31 sheet it's going to be one quote and then we don't want to see it anymore We want to sort of just go and use our sheet But when we open the sheet, we want it to open here Why is this important is because sometimes when we're creating sheets Uh, we can create the very first tab we create is going to be
1:48 g id equals zero Even though the entire spreadsheet file is at this url and the very first tab in the sheet will usually be the one that the sheet opens on So if I open this in another tab, you'll see without the g id, I will show up on quotes or I can just leave If I want to show up on sheet 2, I include
2:11 this g id up here in the url and I can open a new tab. This url has the g id already here and if I go to that, it'll go to sheet 2.
2:21 But no matter what, I want this sheet to open on openme every single time. So I'm going to show you how to do that.
2:28 Go to extensions app script and we're going to write a little app script. So here in our app script, we're going to use function on open.
2:35 Function on open is a built in trigger that every single time this spreadsheet opens, we, we can even refresh it and it will execute here.
2:45 So what are we going to do? We're going to do spreadsheet dot spreadsheet app dot get active spreadsheet dot get sheet by name and the sheet we're going to name is openme which is the tab that we want to open and we're going to do activate.
2:59 That is it. So we're going to save that. We can hit the save project appear or command s. I'm going to close this and now if I go to this page in this GID it will ah open this page.
3:12 This tab. But if I just take the blank ah without the GID, the URL without the GID, I'll go to that and see what happens.
3:23 So we open up, it's on quotes right? And now it's automatically opening me into OpenMe. So no matter what, even if I go to quotes and include this, maybe this sheet 1, include this GID, as before this should go to sheet 1 and it will open at sheet 1 but it will automatically activate the sheet that I
3:42 want to start on. So I'll go here. It'll open up, and once it opens and runs that script, I'm now in OpenMe.
3:49 So this is a really simple Apps Script over here, uh, that will allow you to activate whichever tab you want, uh, based on the name, as, whenever it's needed.
4:00 anyone opens the sheet. And just to review, we're using the function onOpen, which is a simple trigger. It's already built in.
4:06 We just have to use the correct nomenclature here. Function onOpen with a capital O, well, lowercase O for on and uppercase for O, and SheetApp.getActiveSpreadsheet, we're getting the entire file, we're then getting the sheet by name, whichever sheet you want to open, and then we're doing .activate.
4:23 And that's it. That's how you automatically open to a particular tab.