How to Insert Multiple Rows in Google Sheets (Easy to Hard)

Advanced Intermediate Beginner

About this Tutorial

Learn how to add multiple rows to your google sheets by hand, and automatically.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>0:00 I want to show you how to insert multiple rows in Google Sheets . We're going to show you the easy way and a hard way with automation, some way you can automate inserting rows. 0:10 And we're going to have a few problems along the way that I'm going to share with you as solutions, like formatting . 0:16 For instance, there is a very simple way to insert multiple rows.

If you go all the way down to the bottom command down arrow, and you see add 1,000 rows, you can just click add and you can add 1,000 rows at a time. 0:29 You can even add one row at a time if you just click here. If you go up to the top and you're like hey, I don't really want to add rows to the bottom, I'd rather add rows to the top, you can select a couple of rows. 0:40 Select rows, add, and then right click and insert two rows above or below.

And that will insert the rows there. 0:49 Something interesting is going to happen here and I want to show you right now what that is and how to avoid it. 0:54 So if we have some formatting here, we're going to format this with some background color , just yellow. And that's all And then I insert a row below. 1:02 Notice that the background color is copied.

So the formatting of whichever row you're adding to, remember adding to, that is the row that's going to copy the formatting . 1:13 So, if I go down to row 3, the one under it, and I insert row above, you notice the row inserted does not have the formatting above it, it's the one from below it because we're adding one above it. 1:25 So, whichever one you're adding from, it's going to copy that formatting .

Which is actually very useful, especially if you have drop-down menus and you have formatting you want to actually copy. 1:36 Um, and if you want to make a bigger header row , go up to the header . Insert row below or above here, and you're going to copy that header . 1:44 But for now we want to delete these rows here and here, and we just want header and we want some data . 1:51 Now, I showed you a few ways to add rows, right?

We insert a row, we select a row, insert any number of rows you want to enter, and you're going to be able to enter them or add them. 2:03 And you can scroll down and add rows there. But let's see, let's say we want to do this programmatically. So we're going to go to extensions app script . 2:10 We're going to write a little bit of code here, and we're going to insert multiple rows, uh, at the click of a button. 2:17 Maybe we want to specifically add five rows every single time we click a button.

So let's call this project insert rows . 2:27 We're going to call this function insert five rows. First we need a custom menu actually. We get this at betasheets.co slash snippets slash custom menu two functions . 2:38 We're going to copy this. And we're going to insert it above here. So when our ah sheet is opened, we'll have some automations here. 2:48 The first item is going to be insert five rows. We'll call that function function insert five rows. We'll call that function when we hit insert five rows. 3:00 We're going to delete the second function for now.

And what we want to do is, we need spreadsheet .app.getactivespreadsheet.getsheet. Uh, actually we don't need active spreadsheet . 3:14 We want active sheet , whichever sheet we're on, get active sheet , get range . I think we can do, actually no, we just need to do insert rows . 3:26 So we want to insert rows after or before. There are two different functions , and just like I mentioned earlier in this video, it depends what we're trying to do. 3:35 If we are trying to add header rows, we want to use this after one.

But if we want to, insert rows under the header but have the same formatting as the data here, we're going to need to insert rows before. 3:49 And what we're gonna need is just the number of rows and and where they go. So before two, which is the second row. 4:00 And we want to add five rows. So let's save this, command s, once that orange button goes away. We can now close this and we're gonna refresh our sheet or actually reopen it so that we get our custom menu here, our automations menu. 4:17 It'll show up once Apps Script is added. There it is.

We have an automations menu and on this sheet that we have, we can insert five rows. 4:27 We need to authorize first. Once it's authorized, we can actually hit insert five rows. See the script is running and inserts five rows with the formatting from that second row. 4:38 Now let's change this to show you a row. I'm what I mean.

I can tell you as long as I can tell you but I, I think showing you is going to hit home what happens. 4:46 So if we instead do insert rows after and we select one, because this is going to be the first intuitive thing you think about. 4:57 You think oh I want to insert a row under the header row . So I want to insert after one. So now the same function is going to do exactly the same thing but it's going to insert row after one, five of them. 5:09 So let's see, insert five rows, what happens? There you go.

So these all share the formatting of the row it's been inserted under. 5:18 So we don't want to do that. What we want to do is insert. is insert rows before. And maybe we don't want five but maybe we want one every single day. 5:30 So we'll call this insert five rows. We can copy this function and we're going to call this insert one row. 5:38 And we're going to insert before. Before two. Make sure it's before two. We're only going to insert one row.

And let's add that item here to our automations menu. 5:50 We don't need to do this to add a trigger but we just want it just in case we want to ah do this manually. 5:56 So we'll do insert one row. So this spelling here has to be exactly the spelling here in this function . Now, to automate this and do it every single day, you know, at 6am or 5am before we work, we want to insert a row here so we enter our information, whatever information we want to add. 6:14 Maybe we're doing timekeeping, time tracking, project management, maybe we're adding data entry every single day.

Uh, and anytime we do this, we want to make sure that we have the correct function . 6:26 So let's just refresh this sheet again and make sure that even manually, once we open this and manually, it's going to do the right thing. 6:36 So let's actually delete all of this so we can see it. Let's go to our extensions app script again and we're going to need to create a trigger . 6:55 Over on the left side, you see this button triggers. On the bottom right, add a trigger . Choose which function to run. 7:03 We're going to insert one row.

The event source in this case is going to be time-driven. We are going to do every day, so we need a day timer. 7:11 We're going to do it in the morning between 5am and 6am. We only get to choose an hour out of the day. 7:17 We don't get to choose a very specific time if we want to do this every day. What will happen is it will do a random time, but then after that it will go every 24 hours. 7:27 So we'll do 5am to 6am, save, and now every single day between 5am and 6am, one row will be inserted. 7:36 We can see this insert row here. If we have any errors, it may email us.

If we do not want to continue using this, we can always go over here to 3 buttons and click delete trigger . 7:47 We can also edit it if we want to say do it ah every week or maybe every hour, depending on what you want to do. 7:56 So we can edit it as well. Let's save that and just go ahead. and delete it.

So I've shown you how to automatically, which is a hard way, automatically insert multiple rows or any number of rows anywhere in your sheet and I've shown you how to do this manually which is select some cells or some rows, 8:14 insert a row, or go all the way down, command down arrow ah to the bottom and add some rows.</div>