0:00 So we saw this tweet, I wish Google sheets would default to top align and it got me thinking what if we could just do that ourselves and we can.
0:08 So in a normal sheet we have headers and some data here and the normal align if you go over to vertical align you have to click and then you have to click top if you want to change it but normally it's bottom but what if we could make this a little simpler we maybe not have to use the umm this feature
0:27 button or bring it up over here we can create a custom menu or we can even automatically. We'll see Top align everything all the time or rather maybe once a day as we do our data maybe we don't even think about the alignment and we just let it do what it needs to and then that overnight it does it automatically
0:44 so I'm gonna run through all of these options this is sort of one of those weird Little interesting formatting things we can do pretty easily in Apps Script and I'm gonna show you all the cool things we can do with it.
0:54 So I've already started writing this function function top align everything and we need to edit this a little bit. We have spreadsheet app dot get active spreadsheet dot get range and we're only getting one which is the A1 and set vertical alignment top.
1:07 Let's see if this actually works. We'll hit run. We will have to authorize it the very first time it runs.
1:13 Let's just go through that process right now. Allow and let's see if it works. well run it and we'll see yes it worked.
1:24 So it only worked for A1 but how do we get it for the entire sheet? Well there's a few ways we can do this but I'm gonna do row.
1:30 Uh actually we'll start on one one and then we'll go to max rows comma max. And we're gonna need those variables so we'll do variable which will get spreadsheet active spreadsheet.
1:45 And we'll call this variable s sheet equals and then we'll do variable rows are max. max rows i max rows equals sheet dot get max rows.
2:01 Then for columns max columns equals sheet dot get. It's simple as that and we can also change this to sheet.
2:15 And so now we'll save it and we'll see will this run from one it will start in one one and go through the entire sheet from maximum.
2:24 And we'll set alignment to top for everything. Let's hit one. See if we get any errors we don't and we go back to sheet and it is like this.
2:32 So if we want to run this automatically let's see how we can do this. So it's called top of line everything.
2:38 We can go over to triggers. Thank you can use the only one we have so far. We're going to run a time-driven.
2:49 We want to run it actually every hour. We'll say every hour it's going to just top line everything and it's going to keep it.
2:56 Everything top line. So if we ever change the formatting, do anything, maybe copy, paste some data. Within an hour it will be automatically aligned to the top.
3:04 So we have that trigger. Now what if we don't want this sort of just to happen all the time or every hour we want it to run it when we want it quote automatically, similar automatically.
3:14 We need to create a custom menu for that so we can hit that button once and get it. So we can go to better sheets.co slash snippets and the first one here custom menu is going to be what we need.
3:25 We're going to copy the clipboard. We're going to enter that up at the top and we want only one item so far on the top of line everything and all we do is rename this second item here top line everything the exact function name and this first text is what we want to show to the user or us.
3:44 So we want to align everything top. Hit save and now in order to see this we need to refresh it'll close our But once it refreshes and opens again, it'll create a custom menu and now we can top mine everything.
4:02 Let's let's vertically align bottom everything and now without even highlighting anything we just go to custom menu click the script here.
4:09 We do have to authorize it once again but again just the first time we run it we'll need to authorize it.
4:16 Let's try one more time and it has aligned everything. So now we have two ways we're automating this right. Automating this semi automatically with a custom menu and automatically it'll happen every hour.
4:34 But this is the entire sheet and it's only the active sheet. What if we want a range? What if we want to highlight a range and same thing we do here instead of clicking this button we click this button over here.
4:47 That's a little close. Those are a little easier to at least we set it up ourselves. What if we just want the active range?
4:53 We can do that. So we can copy this entire function again and I'm going to say top align a range.
4:59 I'm going to call it here. We're going to add it to our custom menu. Just that one line, add menu.
5:07 Align range. We're going to rename this top align range. And instead of get active sheet into get active range. I'm going to, call this range.
5:21 And we do not need the range here. We to just range.set vertical alignment. We don't need macros. We don't need max columns.
5:33 Okay we'll save that. And again we would, We'll actually be able to see if this works. Run, top, align, range.
5:43 Run we will probably ah we don't need to. Uhh. There it is. Great we don't have to authorize but we do need to refresh The page if we want to see it in the custom menu.
5:56 So let's highlight the entire header row, a line range, and it'll do that. So what if we wanted to, let's say we wanted to do everything except this header.
6:07 So sometimes, We have the header that is bottom, and we want the everything else to be top aligned. How could we do that?
6:16 Let's go back the uh header. So that's just the first row we want to. So align everything, align not header.
6:35 Okay, so we take this align every thing. We'll rewrite it and we'll call it align everything but the header. And we're getting the max rows here and we're saying get range.
6:53 So we are starting on range Thank you. Row 1 column 1. So all we have to do is change the first thing to column 2 and max rows we're not getting the total max rows.
7:05 We're just getting max rows minus 1. And so this will align everything less than row 1. Thank you. So again we can go check it out and see let's refresh and see it in our menu.
7:20 And so hopefully it will not align the header row line not header. And that's it. That is everything's aligned to the top except our header.
7:31 So that's pretty cool. We now have a number of functions in here. And we can automate any or all of them at any amount of time we want.
7:38 We can automate it through this custom menu. Upon clicking or if we want to we can. And align it every hour or so.
7:47 We can also do automations with this function on edit. So that means every time a value is changed in a cell.
7:55 Let's see if this works. We're going to run this function every time that there's an edit. And so we can align these back to the top and say we want to edit data too.
8:14 You can see everything is now aligned to the top. So this is now every single time there's an edit in the sheet, we're going to align everything but the header.
8:22 Pretty cool right? So we can see let's just edit something. We can, if we edit uh, unalign everything to the middle, actually to the bottom.
8:32 Thank you. Make it more, we just edit one thing, we just maybe paid this here. Everything gets aligned to the top.
8:40 Pretty cool right? So now we're automating this via time. We're automating this through every edit that happens on the sheet.
8:48 And the entire spreadsheet file. And we're also creating a menu here that allows us to one click do this action.
8:55 Again, gives us a little bit extra motivation, a little bit extra information to automate this instead of having to select the range itself.
9:05 We know, oh we didn't know what's a, this is everything, this is the range we're on and this is everything but the header.
9:11 So it gives us a little more customization than using just this menu function.