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 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 working 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.

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 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 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 rows, you will have to do a little change in the script. In the for loop, replace the number 1 by the number of rows you wanna 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 by the number of blank rows that you want to add.


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

Wrapping it Up

The way I have written here in small baby-steps format may seem lengthy or difficult to you but, I promise, it’s not. You will just have to 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 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 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 right now.

By Deepak K

I write about business and technology, mainly. I share interesting stuff on Twitter too.

35 replies on “Insert Blank Rows after Each Row in Google Sheets”

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 (:

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.


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.

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.


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.

(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)

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

Thanks a lot, buddy. That means a lot to me.

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

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

Hi there

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

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!

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.

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:



Would it be possible? Thanks.

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!!!!!

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) @

What did I do wrong?

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.

Leave a Reply

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