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?
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.
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.
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.
After you click on the Run function button, it will ask you to Review Permissions as in the screenshot below.
You will have to give permissions by logging in with the same Google account that you have created the Spreadsheet in.
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).
Finally, click on the Allow button (as shown in the image below) and this is the last step.
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.
