3

Suppose you have a spreadsheet similar to:

            A
1   "text a1"
2   "text a2"
3   "text a3"
4   "text a4" 

...and you'd like to create a new column containing each item followed by two blank rows, such as:

            A          B          C
1   "text a1"  "text a1" 
2   "text a2"
3   "text a3"
4   "text a4"  "text a2"
6
7
8              "text a3"
9
10
11             "text a4"
12
13

How can you do that?

4

After playing around a bit, I landed on the following:

  1. Appending each row with a string containing a delimiter with JOIN(), then
  2. Splitting them with SPLIT() and
  3. Converting the resulting array into a column with TRANSPOSE()

Like this:

=TRANSPOSE(SPLIT(ArrayFormula(JOIN(" , , ,",FILTER(A:A, A:A <> ""))),","))
| improve this answer | |
  • This works as long as the column you're working with does not contain commas. Something more exotic, like a Unicode character above code 257, would be safer. I tend to use ✏ because it's char(9999)... – user79865 Jan 2 '16 at 8:49
  • Works!!!!............. – Riccardo Feb 12 '18 at 12:30
-1

How I use this... Throw this in script editor. Create a drawing (I use a the 'button' shape) and label it addRows or something. Then save and close the drawing box. Right click on the button and then click the drop down arrow in the top right hand corner. Click assign script. Then just write "addRows" without arguments or quotation marks.

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);
  }
}

You could also create a custom menu option instead of the button with something like this...

function onOpen() {

var menu = [{name: "Add rows to current sheet", functionName: "addRows"}];

SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);

}

Hope that helps

| improve this answer | |
  • added some instructions – Heirophant Jan 6 '17 at 2:21

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.