00:00 Hello, better sheet members. Thanks for watching this video. I dunno what it's gonna be called because this is a work in progress.
00:07 I wanted to try to come up with a new essentially a lead magnet actually for better sheets. I wanted to help people come up with some really cool use cases for formulas and show examples and, and share sort of a resource that links out to blogs and videos all different types of formulas and formula combinations.
00:32 And it's gonna be called Spreadsheet Alchemy. This was a course that I, the name of the course that I was doing before, now it'll be this this set of spell binding Google Sheet formulas and app description and URL spells, things that are very simple tricks not simple tricks, simple use cases of these tricks in Google Sheets.
00:58 But here's the thing. In this Google Sheet, I wanted to make a new type of navigation. So in newsletter, virtual mall, I created an elevator here.
01:09 You, so like there's a landing page on the sheet, you enter the elevator and it looks like the buttons of an elevator.
01:16 And then when you go to a level, let's go to level two. It looks like a mall. You're navigating around a mall and you have little things here.
01:25 When I made sheets.com with four e, I wanted to have a left-handed navigation. So normally when we're working in Google Sheets on the bottom you see, you know, a bunch of tabs.
01:36 And there's also up in the url, there's the G I d, the, the idea of a sheet. So you can sort of navigate with those links.
01:45 But I thought, oh, I've never seen a Google sheet work, like a website where we have a lot of like, like ne left hand navigation meaning like there's buttons or something.
01:56 This is sort of like an old style of website as well. When there was like a link list on the left, I don't know if anyone remembers this, but I wanted the navigation to stay the same across the entire spreadsheet file as you move between sheets.
02:12 I wanted this left hand side to stay exactly the same, so I had to make all these sheets the same, but as you click on them as well, it looks like nothing moves.
02:22 It looks like actually just the hand moves as you click through these, right? So this left-handed navigation sort of worked really well.
02:30 I, I, I sort of was proud of it and I think it's pretty cute. However, it's, it's, it's a cumbersome to create.
02:39 I don't think anyone's actually going to do this unless they're working on some, like presentation maybe or some interactive games.
02:46 There's a few use cases where this is interesting. And then it brought me to the idea of presentation. Like usually right Google Slides is hands down better for presentation.
02:58 But As I started thinking about this and I thought about spreadsheet alchemy and how I wanted, I want the page to feel like a book.
03:07 And so I wanted you to be able to turn the page essentially. And I accidentally found this thing in Canva, this sort of page turn, p n g not p nng file.
03:18 It was a file image of this page turning. So in the bottom right you have this button here, oh, on not button.
03:27 Now I'm, now I'm spoiling it. But basically it's an image with a script and it becomes a button that can be clicked.
03:34 And now, so I, I did the app script and I'll show you the whole app script here. You can click this page, turn and turn the page.
03:45 It goes through each of the tabs. Obviously I'm still work. This will work in progress. So once this spreadsheet alchemy is done, I'll reveal, I'll reveal this to better sheet members first, and then I'll let other people in on it.
04:00 But I just thought this was cool. And as you get, if you get to the final page, the final tab, it will go back to the original, the first page.
04:11 So how is it, what is this and how does it work? It is an app script. I will show you the app script.
04:17 I will walk you through it right now. And you'll be able to access this once spreadsheet alchemy is available. I'm not giving access yet cuz it's still a work in progress.
04:28 Very much so. As you can see, I am barely into it. I have nothing on these pages. And I don't know if, if the end result will exactly have this.
04:36 I just thought it was a really cool addition to a sheet. If you're thinking about different navigations or you are trying to present your your sheets I think this might be a cool way to do it.
04:49 So how did I do it? First off, I did insert image over sells. That's the first thing I did. And I did this p and g that's like a page curl from the tip.
05:02 I think there's a couple. You can sort of find any stock image or a png file or create your own kind of image.
05:11 Maybe it is a button, you know, maybe you have a hand going next. It could be any image. And if we click on it here in these three buttons we assign script and the script is just called next page.
05:23 And all of them on every page has the exact same script. Next page. So it's only one script. In order to power this the first thing we do up here, we call it next page, we're going to have a variable spreadsheet dot get active spreadsheet.
05:38 That's just getting the entire file. And then we first get the number of sheets. We want to know how many sheets are here.
05:45 This is actually useful. The, the, the way that I set up the script is like, I don't know how many sheets I'm gonna have, so I don't want to, I don't wanna assign like a script that when It's on this one go to this one.
05:58 If it's on this one, go to this one. I just wanted to set every page to go to the next one plus one.
06:05 So I said, okay, how many sheets are there? Then we said get sheets. Now what this skit sheets does is it actually provi provides you a an array of objects, which each one of them is the sheet.
06:17 And so this array of objects we can iterate through with a for loop and I need to know where I am.
06:26 So I've called it variable. Now what sheet am I on right now? And that's spreadsheet app dot get active, sp active sheet.
06:32 So this is different than get active spreadsheet. I know that this function will be running when someone is using the file.
06:40 This is not a script that we're gonna create a trigger for and we're gonna run it in the dead of night.
06:45 We are going to be clicking on that button. And so we know we're gonna have an active sheet so we don't have to do get active spreadsheet, get the sheet.
06:53 We can just do spreadsheet app dot get active sheet dot get sheet name. So now I know two things. I know how many sheets there are cuz I got it with number of sheets.
07:05 I know I have an array of the sheets by just getting this spreadsheet app dot get active spreadsheet dot get sheets.
07:12 And then I also have the sheet that I'm on right now and I know the name of it. Okay? And I know that no sheet will ever have the same name as another one.
07:21 It's, it's literally impossible to have two sheets with the same name. So I know, okay, I know where I'm at now I know how many sheets there are and I have an object of all the, an array of all objects which are sheets.
07:32 Okay? I'm using these loggers to log things as I was testing it out. I'm just gonna delete them right now so we can see what is actual code or not.
07:43 Now this four loop, so if you're not familiar with Four Loops, I do go over it in spreadsheet Automation 1 0 1.
07:49 So if you're a Better Sheets member and you're have access to this video right now that you're watching on Better sheets.co, then go over to Better sheets.co/courses and go sign up and you can take, you have full access if you have access to this video, you have access to that whole entire course spreadsheet, automation 1 0 1 and in that course it's three hours long total over, across, across like 28, 29 videos.
08:15 And the four loop is talked about there. So I'm just gonna go over this pretty quickly. We got the I equals zero to start off.
08:24 We are only gonna go until we have the same, the i is the same number as the num, the as how many sheets we have.
08:33 And then we're always gonna iterate I plus one after we go through this. If the name of the sheet, the first sheet, like I, this is I, this is a bracken notation, I so zero if it's, if that name is equal to the name of the sheet now and the number the I is less is equal to the number of sheets minus one, meaning it's the last one.
08:57 So number of sheets minus one with I cause I is starting at zero. So if there's four sheets, it's 0, 1, 2, 3.
09:06 So if, if it's the third one or the the final one, sorry, the fourth one, number three, what we wanna do is we wanna go all the way to the back to to the front, back to the front and go there.
09:16 So we go Nick sheet is Ss get sheets, we're using BRCA notation here of zero. And then we get the name of that sheet and then we just set it active.
09:27 So the actual thing that all of this code is doing, this one thing, which is setting the active sheet as the sheet with the name of the next sheet, which at the very end is gonna be the very first one.
09:41 And if not, so if it's not the la if it, if the name of the sheet are on now is something and it's not the very last sheet, then this is all skipped.
09:55 Then what? Well if the sheet is the one that we're on now, then we're gonna go get the sheet I plus one.
10:04 So we're using this BRCA notation instead of just going zero to the first one, we know we can say I plus one, which is gonna be the next one cuz I the BRCA notation for I is gonna be the one we're on now.
10:16 So we go get the name of that and then we set the next one active. So this is spreadsheet app dot set, active again and just spreadsheet app, get sheet by name, get the sheet.
10:29 That's the next name, that's named the next one. And that's it. That is just pretty cool navigation here. That allows us to loop through and go to the next page and act like a book.
10:43 So now we can turn our Google sheets into essentially a page turning book or presentation. Maybe this is better for like in more interesting for presentations.
10:53 Look, I know the <laugh>, it is so close right to down here, these tabs that we can just switch these tabs.
11:04 But I just thought this would be a fun thing and also I did not intend it to be on the bottom.
11:08 I actually, I think I'm gonna move it, I'm gonna flip this p and g around and I think I'm gonna put it on the top right corner because it is a little too close.
11:16 I mean if we're coming down here to mo turn the page, we might as well just click on the next tab.
11:21 But I think that navigation up here in the top right will look really cool. Thanks. As we're watching this video, you will have access to this script.
11:31 I'm gonna put the video at now, but you will have ac you will have access to the script once spreadsheet alchemy is available.
11:38 Bite.