Create Specific Sized Sheet

0:01 So you wanna create a size of a sheet, a new tab with a very specific amount of rows and columns. We're gonna do that in this video. I've made an extension actually that does. Almost this, but not really. It's called Tiny Sheets and you can get it for free. It's a completely free add-on over in extensions add-ons. Get add-ons, and just look for tiny sheets. And this can create a one by one sheet, a one row sheet or a one column sheet. But also you can have your data here and then it's going to delete everything except that data . And there it is. So it would delete blank rows and blank columns outside of the range of data . But what if we wanted to create a two by two, two by three, five by five 20 by nine?

0:46 Let's do that in this video without that extension, because we don't really know the number . We might wanna make a a hundred rows and two columns. We don't know. We're gonna just. Make a cool extension here. We're gonna code it all in app script . So this is what my app script looks like. Knowledge, just name this, it's gonna be Y by Z, or X by X would be weird with three Xs. Let's just rename this. So we need a few things. One, we can go to better sheets psycho slash snippets. And we're gonna get this function on open. It's to create a custom menu so that we can click it and say, Hey, create the sheet. So we're gonna say, create new sheet X by Y.

1:34 And let's call this function , create new sheet X by Y, and we're gonna write function , create new sheet X by Y, and we need to add parentheses and these curly brackets and inside of our function . Let's do that. Well, first we need to ask what's the X? How many rows? So we're gonna say how many rows? Then we're gonna say how many. Columns and then we're gonna create that sheet here. That's gonna be the very end. But let's grab this information, right? Or we can say variable columns. Count equals something variable.

2:21 Columns row, sorry, this is rows count equals something. Let's say it's five and this is 10, and then we can come back in and ask for those later. 'cause I think that's actually more interesting if we do that. And let's see the code to actually do it. Well, we do spreadsheet , app dot, active spreadsheet . We're gonna insert a sheet. And what this does is it inserts a sheet into. Down here, just a new tab. Well just like adding a new sheet, it's always going to be A through Z, which is 26 columns and one through 1000 rows. So what we actually need to do is then delete columns, some

3:07 here, it's gonna be 26 minus. That's the difference and variable delete rows count is equal to 1000 minus however many rows. So that's how many we wanna delete. We wanna essentially de delete like 995 here, and we wanna delete 16 here to get down to 10. So let's try this out and see what happens. We're going to choose our function , create new rows, click run. We will probably have to authorize the very first time we run it. And he actually, it unfortunately. Created two new sheets. And we need to add that we want to start at the first row in the first,

4:42 that column, and then delete this many. So we do one comma here. Let's name this. I think we need to name this new sheet. Let's run it again. And now we only have one sheet and has five columns and 10. 10 columns and five rows, and that is exactly what we wanted here. Five and 10. So we know we're on the right track. Now we have a new sheet, calling it new sheet. Maybe we even ask how many, what do you wanna call it? But for this video, I'm gonna keep it on. We're just gonna ask for rows and columns. So how do we ask for the number of rows we're gonna do variable UI equals spreadsheet , app dot ui. We are gonna say prompt rows equals UI prompt, and here we're

5:34 asking for a number , how many rows. Then if pump rows response get response. Then in here we're gonna set this rows. We'll be prompt rows that get response text, and if we get that, we're gonna do it again. Ui, sorry. Variable prompt columns equals ui. Do prompt. How many columns and here we'll also say if prompt columns that get response text, if there is something there, then how many columns is going to be the response text? So we only get to columns if there's a response for rows

6:22 and this, these two variables will end up here in columns and a row count. So let's try this out. I'm gonna save this. I'm going to actually go and refresh my sheet. And I have a custom menu here that says, create new sheet X by Y. I do want to actually delete this sheet for now, and I'm gonna say create new sheet X by Y. It's gonna ask me how many rows? I'm gonna say 10. Okay. How many columns? 10. Okay. And it has created a 10 by 10 sheet. Very cool, right Extensions app script to go check our script again. So we're saying, Hey, on open, create our menu. Our menu is only accessible to create new sheet by X, by y, asking

7:10 how many rows, how many columns. If we do get a response and then generating that sheet, however, there is one big problem. And I don't know if you've noticed it yet, but if we ha have a number that's greater than 26 or greater than 1000, what do we do? Well, I think we move these delete v uh, variables to here. Why that might be good is because we can then say, if. Rose count is greater than 1000. Then this delete rose count will be something else and actually might be insert,

7:56 but then else. You say this, so if it is not over a thousand, you're gonna do this. We will need new rows. Count equals, that'll be rows count minus 1000. So we go down here and we say if rows count and columns count is greater than 26, we're gonna do something here. Else we'll do something here. So I think there's four possibilities, right? So if they're both above, we do new sheet insert columns , and I think we need one extra thing, which is here seem as this, if. Columns count is greater than 26.

8:42 We're gonna do something else. We're gonna do this here. New calls count equals. Columns count minus 26. So at the end here, we're gonna say, Hey, we probably need both of these new sheet. insert rows and we're gonna grab that count up here. New rows count because we wanna insert just enough rows. We wanna add two more ifs if rows count is greater than 1000 and columns count is less than 26. A than or equal to 26 actually, then we'll need the rows to be inserted and the columns to be deleted.

9:27 But that deleted might be zero, right? If it's 26. And then we want to break here, or actually return would be better to be to end. And then if the opposite meaning row count is less than 1000, and columns count is greater than 26. Again, we wanna return at the end of this. We will need the opposite. We need to delete rows and insert columns , and at the end of here, we will return both of these. So that's the end of the. Function for both of the, all four of these possibilities, right? So this is less than or equal to there. So now we have all four possibilities. It's either one or the other of rows and columns, or it's both, or it's neither.

10:16 So there we go. We have a possibility for each one. We can go here, create a new sheet. We can check this if it's correct. How many rows? 1010. How many columns? Let's do two columns. It's probably gonna say new sheet already exists. Yeah. So what we might do is, well, we'll just delete this for now. We don't need to know. It's always gonna be a new sheet. Hmm. Let's add a timestamp here. New sheet plus new date, so we can create that over and over again. create new sheet . We can always rename it later. How many rows? 1010. How many columns? Two. Here's our new sheet calls. Count's not defined. Let's go back. I think we have

11:02 rose Count was over. Column count was under, Hmm. Let's see. We didn't get any of that Done. Oh, we need to get this variable. Columns count. Here is calls count, so we just need to change that to columns. Count discount now this'll work. So let's delete our sheet. We can also, let's get the new name of the sheet, name sheet prompt, or rather. Prompt name sheet equals ui, prompt sheet name , and here we say if this prompt sheet name has something, meaning it's not no get

11:49 response text is gonna be what we want. Then variable sheet name equals that response, and then we take all of this and put it inside of this curly bracket here, and instead of this new sheet date. Call it sheet name . So this variable is whatever we respond to here. Very cool, right? We can ask three questions. What's the name of the sheet? How many rows, how many columns? It's running it. Sheet name new. How many rows? Two. How many columns? Let's say 30. And we have two rows and it just didn't make enough. Let's see where we went Wrong.

12:35 So insert column , new columns count. It's one of these. It's this one was here, we said 30 minus 26, so it should have been four. See what this insert columns needs. Just needs a. I could say start at one and have that number . The calls count. Let's do that for all of these new ones. Save that and let's go back and try it again. Create a new sheet. X, X, X, X, X. Okay. How many rows? Five. How many columns? Let's do 30 again, and we have it four extra rows or sorry, columns. So 26 plus four is 30, and we have five. So let's check the rows is working. This one's gonna be called toll.

13:21 How many rows? We want 1010. How many columns? Just three. There's our three columns. Perfect. And 1010 rows. Perfect. So it's working. Let's see it work for both of them. If they're both above 26 and a thousand bigger, let's make a bigger sheet. How many rows. 1,100. How many columns? 50. That's a really big sheet. Let's see. A x equals column 50 and 1,100 rows. Perfect. So we got through all of that. We've created our custom menu .

14:08 We ask for a sheet name , ask for how many rows. Ask for how many columns within each, we get a variable if we need to add or subtract, and then we have the actual doing of it. Create those, that sheet, create those rows, delete the columns, whatever we need to do. One of four possibilities here. Thanks for watching and hope you enjoyed this. If you'd like to see more app script , check out Udemy and. Master spreadsheet automation over on Udemy. It's a great course that goes through a lot of this stuff. You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before.

14:54 I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.