Google Sheets – How to Split Multiple Rows or Insert Below Existing Rows

google sheets

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.

Best Answer

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

Code

function result(range) {
  var splitCol = 1; // split on column B
  var output2 = [];
  for(var i=0, iLen=range.length; i<iLen; i++) {
    var s = range[i][splitCol].split("\n");    
    for(var j=0, jLen=s.length; j<jLen; j++) {
      var output1 = []; 
      for(var k=0, kLen=range[0].length; k<kLen; k++) {
        if(k == splitCol) {
          output1.push(s[j]);
        } else {
          output1.push(range[i][k]);
        }
      }
      output2.push(output1);
    }    
  }
  return output2;
}

Explanation

The script evaluates each row, and particularly the second column of each row (in JavaScript arrays are zero based, so column 2 corresponds to index 1 of the array). It splits the contents of that cell, into multiple values and uses the "\n" as delimiter (line feed). After that it adds the existing info to an array and only add the individual results, when it hits index 1 (k == 1). The newly prepared row is then added to another array, that's being returned to show the result.

Screenshot

data
enter image description here

result
enter image description here

Example

I've created an example file for you: multi-line cells into new rows.

Add the script under Tools > Script editor.
Save the script, the name doesn't matter.
Use the custom function in a cell just like a built in (see the screenshot above).

Best Answer

Related Solutions

Google-sheets – way to count how many entries in a given column have a particular value

Google Sheets – How to Split Multi-Line Cells into New Rows

Code

Explanation

Screenshot

Example

Related Topic