Google Sheets – Insert Empty Rows Under Occupied Rows

google sheets

How do I insert empty rows under all (already occupied) rows simultaneously in Google Spreadsheets?

Lets say in row 1 it says "A" and in row 2 it says "B", "C" in row 3, etc. I would like it to say "B" in row 3, and "C" in row 5, etc. from now on. Inserting a row underneath one by one is simple. I'm asking about inserting empty rows on a larger scale.

Best Answer

I think this requires a script, such as this one:

function myFunction() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var sheet = ss.getActiveSheet();
   var range = sheet.getDataRange();
   var values = range.getValues();
   for (var i = values.length; i>=1; i--) {
     if (values[i-1].join("")) {
       sheet.insertRowsAfter(i, 1);
     }
  }
}

The command getDataRange() selects the range containing data; but since it's possible you have empty rows between filled ones, there is a separate check for nonemptiness of rows: values[i-1].join("") evaluates to True only if some data is present. The offset i-1 is needed because the index in an array is 0-based, while the row numbers begin with 1. I use i for row number.

The rows are inserted from bottom up (i--), to avoid the situation where newly added rows move down the ones that are still to be processed.

To use this:

  1. Go to Tools->Script Editor.
  2. Copy-paste the script there.
  3. Save, renaming to something descriptive like "Extra lines".
  4. Click "Run" button.
  5. At the first execution (only) you'll be prompted to authorize the script.

script

Related Topic