2

I use a spreadsheet to keep track of journeys in a lift-club. Each day has its own row, and the number of lifts given to a person will be 0, 1 or 2.

I realized that the algorithm for the lift scheme is slightly flawed, so now I need to record each journey individually, i.e., I need to split each row in two.

There are already a lot of days on the chart. How can I add a row below each row / i.e., split every row? Usually rows are added at the end of the spreadsheet.

3

There is no default way to do it through UI of Google Spreadsheets. However, I have written a small script that you can use on your Google Spreadsheet to add rows.

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

  for (var i = 1; i <= numRows*2 - 1; i+=2) {
   sheet.insertRowAfter(i);
  }
}

To use this code, follow these steps.

In Google Spreadsheets, navigate to Tools > Script Manager.

screen shot from Google Spreadsheets showing menu option

Click New in the next window, a blank code.gs file will be opened. Copy and Paste the above code in Code.gs and save the project.

Come back to the tab that contains your original document. Open script manager again if it is not already open. Click Refresh at top right. Now you will see the function "addRows". In the screenshot you will see other function that I have created earlier, but you will only see one unless you already created some other function. Select it and click Run. Now rows will be inserted in your spreadsheet at once.

list of functions in script manager

  • Thanks! I also unfreeze rows and columns, and unmerge entire sheet so that I can sheet.getRange(i, 1, 2, numColumns+1).mergeVertically(); after the insertRowAfter function. – caduceus Jan 26 '14 at 10:28
  • Thanks. "Script manager" no longer appears for me, but using "Script editor..." allows me to enter this script. A few notes: it adds an empty line below each line, not only selected lines; and also, it can take several seconds to add each line, so if you have thousands of lines, be prepared for a long wait. – alberto56 Aug 25 at 12:11
1

Here is a solution which does not use scripts and is lightning fast:

Let's say you want to insert one line below rows 451, 452, and 453

| This is row 450 |
| This is row 451 |
| This is row 452 |
| This is row 453 |
| This is row 454 |

Start by adding a new column A and add the formula "=ROW()" to each line under which you want to add an empty line:

|        | This is row 450 |
| =ROW() | This is row 451 |
| =ROW() | This is row 452 |
| =ROW() | This is row 453 |
|        | This is row 454 |

Now copy cells A451:A453 and paste them back but as (hard-coded) values:

|     | This is row 450 |
| 451 | This is row 451 |
| 452 | This is row 452 |
| 453 | This is row 453 |
|     | This is row 454 |

Next, insert 3 lines below 453, and copy-paste the values from A451:A453 into those new lines at column A.

|     | This is row 450 |
| 451 | This is row 451 |
| 452 | This is row 452 |
| 453 | This is row 453 |
| 451 |                 |
| 452 |                 |
| 453 |                 |
|     | This is row 454 |

Now, select the entire lines 451 until the last new line. Go to Data : Sort by range A-Z column A.

This gives you:

|     | This is row 450 |
| 451 | This is row 451 |
| 451 |                 |
| 452 | This is row 452 |
| 452 |                 |
| 453 | This is row 453 |
| 453 |                 |
|     | This is row 454 |

enter image description here

Finally delete the column A you created for this purpose and you end up with:

| This is row 450 |
| This is row 451 |
|                 |
| This is row 452 |
|                 |
| This is row 453 |
|                 |
| This is row 454 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.