Insert Blank Rows after Each Row in Google Sheets

Top - Adding Blank Rows after Each RowAdding 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…

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

Done.

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

Wrapping it Up

The way I have written here in small baby-steps format, it 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.

Share this Article with the People who may find it usefulLet 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.

Deepak

Founder, digiTechnolog | Blogger, Digital Marketer, and a WordPress Expert. Writes about the SEO, WordPress Optimization and Various other Internet-related Topics.

Leave a Reply