Insert Blank Rows after Each Row in Google Sheets

Adding an empty row after each row, manually, is possible only if you have a Google Sheets spreadsheet with just a few rows.

But, what to do when there’s a large Google Sheets spreadsheet?

Well, I needed to add 2 blank rows after each row in my Google Sheets (with 150+ rows) and needless to mention – that’s quite impossible to do it manually.

So, after researching a lot, I, finally, came up with a solution using which resolved my problem easily.

It may seem complicated to you at first but it’s NOT! Just follow along and, believe me, it’s the easiest way to add blank rows in your Google Sheets spreadsheets.

So, here I go…

Note: Please make a copy of your original document before running the script.

How to Insert Blank Rows after Each Row in Google Sheets

When I started looking for various solutions to add blank rows after each row in Google Sheets I tried multiple solutions online but nothing seemed to work until I found the final one which I’ve explained here.

But, first,

What will be the end result of this tutorial?

Blank Rows in Google Sheets

As you can see in the above image, a blank row has been added after every row. So, if you follow this tutorial, you will be able to do the same in your large Google Sheets spreadsheet.

Adding Rows with the Google Script

Like 100+ other Google products, Google Sheets is a spreadsheet program that is much like Microsoft Excel except that the Sheets works in the browser itself.

I believe it’s needless to talk about Google Sheets here, so let’s jump to the tutorial directly.

I’ve split this into 2 parts.

I have added another method at the bottom of this post that doesn’t require running scripts. Check that out.

Part 1: Creating the Google Script

First of all, open the spreadsheet you want to add blank rows in your browser and open the Script Editor by navigating to the Tools > Script editor as shown in the screenshot below.

Go to the Tools - Script Editor in the Google Sheets

As you click on the Script editor, a new script window will open; give your script any name and clear the pre-written codes in the Code.gs editor area.

Below are the few lines of code which will do all the magic here. You will need to copy the below code and paste it into the Code.gs editor area.

function addRows(){
  var startRow = 1;
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();

for (var i=numRows; i > -1; i--) {
  sheet.insertRowsAfter(i + startRow, 1);
  }
}

After you paste the code, the current screen should look something like the below screenshot.

Google Script Code Editor - Copy and Paste the Code

So, you have successfully created the Script, now, it’s time to run the script.

Part 2: Running the Script

Now, it’s time to run the function named “addRows” that you just created in the previous step. Navigate to Run > Run function > addRows in order to run the function. You can also do so by clicking on the Play button.

Run the Google Script

After you click on the Run function button, it will ask you to Review Permissions as in the screenshot below.

Click on the Review Permissions

You will have to give permissions by logging in with the same Google account that you have created the Spreadsheet in.

Choose an Account to Authorize Permissions

After you log in with your Google account, sometimes, you may get a warning saying “This app isn’t verified” but you will have to click on the Advanced option and then click on the Go to addRows (unsafe).

This app isn't verified option

Finally, click on the Allow button (as shown in the image below) and this is the last step.

Click on the Allow button

After you click on the Allow button, your function will start running and you will be able to view changes by going to the tab where your spreadsheet is opened.

✅ If the blank rows are not added after this, then you can try clicking on the Run button once again and this time you won’t have to Review Permissions again.

How to Add More than 1 Blank Rows after Each Row

In order to add more than 1 blank row, you will have to do a little change in the script. In the for loop, replace the number 1 with the number of rows you want to add after each row.

Let me explain:

function addRows(){
  var startRow = 1;
  var sheet = SpreadsheetApp.getActiveSheet();
  var rows = sheet.getDataRange();
  var numRows = rows.getNumRows();

for (var i=numRows; i > -1; i--) {
  sheet.insertRowsAfter(i + startRow, XXXX);
  }
}

Replace the XXXX in the above code with the number of blank rows that you want to add.

Done.

So, it’s just that simple, isn’t it?

Wrapping it Up

Just follow the on-screen instruction while using Google Script to add blank rows after each row.

Let me put all those baby steps together once again,

  • Open the Google Sheets where you wanna work in
  • Go to Script editor by navigating to the Tools > Script editor
  • Give your script a name and paste the one that I’ve provided in this post
  • Save your script and click on the Run button
  • Review Permissions by logging in with your Google account
  • Click on the Advanced > Go to addRows (unsafe)
  • Click on the Allow button to start the script

So, that’s it.

Now, it’s your turn.

If you find any difficulty doing this, or maybe you have a related query. Either way, let me know by dropping a quick comment below.

Update: 22 May 2022 — Adding blank rows without using the above script

Thanks to David in the comments section below who showed a way to add 1, 2, or more blank rows by using simple Google Sheets formula.

I tried and it works, you can look at that in this Google Sheets document.

If you have to add 2 rows after each row the use the following formula:

=TRANSPOSE(Split(join(", ,",ArrayFormula((A2:A103))),","))

And if you have to add 3 rows, use this formula:

=TRANSPOSE(Split(join(", , ,",ArrayFormula((A2:A103))),","))

You can adjust the formula accordingly for any number of rows.

Hope this helps.

Similar Posts

Leave a Reply to Benjamin Cancel reply

Your email address will not be published. Required fields are marked *

41 Comments

  1. OMG that is sooo smart! After reading your instructions, I was able to add 2 rows per preexisting row in about 3 1/2 minutes time! (I started out with approximately 250 rows) Thank you Deepak. I feel like a genius (:

    1. Thanks, Jill.

      Glad that it worked for you. 🙂

  2. What if I don’t have a “script editor” option under tools? Should I use a different browser?

    1. Which browser are you using?

      I’ve tested this on Opera, Chrome and Firefox; and the “Script Editor” option appears perfectly.

      Make sure you have created a Google Sheets document.

      Hope that works for you.

      Thanks.

  3. MATTHEWMAN says:

    It seems to come up sayin these rows are out of bounds

  4. Hi Deepak, thanks for the script. I’m getting the following error:
    ‘Those rows are out of bounds. (line 8, file “Code”) -‘
    and the term ‘startRow’ is highlighted in row 8.

    Can you think of why this might be happening? Thanks.

    1. I don’t get that error though and no idea why it’s happening to you.

      Checking out Chase’s comment here might help.

      I myself struggle a lot with the codes.

      However, I had come up with these after a lot of experimentation.

      Thanks.

  5. Hi Deepak, and thank you for the script!

    I’m unfortunately getting the following error message and would be extremely grateful for any help (y sheet has 1485 rows so adding the lines manually would be a tremendously arduous task!

    Here’s the error: ‘Those rows are out of bounds. (line 8, file “Code”)’
    In the script editor the following term becomes highlighted as part of the error: ‘startRow’

    I’ve tried in Chrome and Brave browsers and have no merged cells and frozen rows/columns.

  6. (so sorry about the repeat comment, I left the first one using Brave browser which showed an error and no confirmation of having worked, so I left the next one using Chrome and then saw that the initial comment had registered)

  7. The script doesn’t work if you have data in the last row on the spreadsheet. One simple solution is to add a blank row at the end. Another is to change this line:
    sheet.insertRowsAfter(i + startRow, XXXX)

    to this:
    sheet.insertRowsAfter(i + startRow – 1, XXXX)

    @Deepak Thank you for making a tool like this available to the public, not trying to steal your thunder, just trying to help the community. Feel free to delete my comment if it’s unwelcome

    1. Thanks a lot, buddy. That means a lot.

      I’m a not programmer, I just found this solution by experimenting.

      Someone who has some knowledge about this might even create a Google Sheets AddOn.

  8. Hi, very nice! how to add rows with an text instead of white?

    1. I am not sure about that, sorry.

  9. Hi there

    This is amazing! How can I edit this code so that rows are only added IF a certain column is not blank

    1. Hi Kia,

      Sorry, I am not sure about that.

  10. Miguel Kirk says:

    Covid-19 guy here, hoping to grow my Dynasty Fantasy Baseball League analysis so the members have some data to chew on.

    I am a Chrome and Safari user, encountered the same I have the same issue as “Russ” and “Russell” above.

    “Exception: Those rows are out of bounds. (line 8, file “Code”)”

    Has anyone found a solution for this? Please help!

    1. Gypsy Gold says:

      Yea dude, the very last row in the sheet can’t have anything in it. Just insert a blank row below the final line in the sheet.

  11. Hi, I wonder if the same logic applies if for example, I have 4 consecutive rows of Person1, then 3 consecutive rows of Person2 and so on and so forth; and I want a blank after every Person Rows.

    Like this:
    Person1
    Person1
    Person1
    Person1

    Person2
    Person2
    Person2

    Person3
    Person3
    Person3

    Would it be possible? Thanks.

    1. Hi Mae,

      I’m sorry to say, but this is not possible with this script.

      Thanks.

  12. How to add a row only if the value is not equal a specific cell

  13. Hi! I was wondering if there is a way to edit this script in order to do the same thing but with columns instead of rows?

  14. Deepak, you are my hero! I’m a simple locksmith who uses sheets to track and label key codes, and I followed your directions and they worked like a charm. THANK YOU SO MUCH!!!!!

    1. Glad that it helped you.

  15. Worked perfectly. Thanks a lot!

  16. Hi,
    I am super not a computer programmer, so I need a lot of help! I am getting an error that says:

    ReferenceError: document is not defined
    (anonymous) @ Code.gs:3

    What did I do wrong?

    1. Hi Bonnie,

      I think there was some problem with the code. I have updated in just now. And, it should work properly.

      Please check and let me know.

      Thanks.

  17. This was wildly helpful. Thank you so much!

    1. Glad it helped.

      Thanks a lot, JP.

  18. someone grateful says:

    Thanks, this was really well done!

    1. Glad it helped.

      Thank you.

    1. Glad you found it helpful, Kassiah.

  19. Hi Deepak. Here is a Script free solution by using only one formula which composite split and Join. please see the example here:
    https://docs.google.com/spreadsheets/d/1jILIom3zYnqHA6h0_3eeZAJj9vcd2rsdlwsWbflL71M/edit#gid=0

    1. Wow. It’s clever, David.

      If you’ve written a blog post on the topic or plan to write, please attach it to this comment. People may find it useful.

      Thank you.

  20. Thanks. I had over 1000 lines I needed to add 3 rows after each. this was a huge time saver!

    1. DeepakNess says:

      Glad it helped, Jared.

  21. Thank you Deepak, you’re a genius!! This was perfect : )

    I was trying to search for a formula way rather than spending time right now to learn google code or using google code at all for that matter to import the google sheet into R, and then back and forth as needed. I saw that formulas *could be* used back and forth between Google sheets and R (from here https://googlesheets4.tidyverse.org/reference/gs4_formula.html) , but google code was abit of mystery, the formula seems easy to use and is perfect for what I need right now!

    Thank you : )

    1. DeepakNess says:

      Glad you finally got it working, Pratik. Cheers!

  22. Perfect!!!! It happened in seconds…Thanks a million!!! Stay Happy and Healthy!

    1. DeepakNess says:

      Glad it worked for you, Asmi! Cheers 🥂