Google Sheets - Clone row but with only data from one cell

Question

I'm looking to clone a row 3x, but only keeping data from one column.

So essentially I have the following [Name / Time / Booking], and each row is populated with all 3 properties, I'm trying to create 3 blank rows underneath each current row which is populated with only the persons name.

Can't work how to do it in scripting and can't find a plugin to do this. My data set is over 10,000 big so doing it manually isn't an option.

What I have:

What I want:

JPV · Accepted Answer · 2016-10-27T18:31:00.387

0

UPDATED code:

function duplicateRows() {

var sh, v, arr, c, b;

sh = SpreadsheetApp.getActive()
    .getSheetByName('Blad1')
v = sh.getRange(1, 1, sh.getLastRow(), 40)
    .getValues();
arr = [v[0]];
v.splice(1)
    .forEach(function (r, i) {
        arr.push(r)
        c = 0
        while (c < 3) {
            dup = makeEmptyArrayXEl(40)
            dup[0] = r[0];
            arr.push(dup)
            c += 1;
        }
    })
sh.getRange(1, 1, arr.length, arr[0].length)
    .setValues(arr);
}


function makeEmptyArrayXEl(num) {
var arr = [];
for (var i = 0; i < num; i++) {
    arr.push("")
}
return arr;
}

edited Oct 27 '16 at 18:31

answered Oct 27 '16 at 11:51

JPV

26,499
4
33
48

Do I need to tell it what column to use for keeping data? e.g. define 'Name' or 'Booking'? – JMKelley Oct 27 '16 at 14:03
Getting the following error "Incorrect range width, was 3 but should be 5 (line 20, file "Code")" – JMKelley Oct 27 '16 at 14:13
I removed excess columns and rows - works perfectly! – JMKelley Oct 27 '16 at 14:15
I have 44 columns in the document I want to use this on - what number do I have to change to make it work, currently it's set to 3? – JMKelley Oct 27 '16 at 14:21
Got it to work with my columns, but it seems to have made 16 duplicates instead of 3 :( – JMKelley Oct 27 '16 at 14:45
the number in the while statement is how many rows will be copied (per row). The array that is pushed is the newly created row. arr.push([r[0],"",""]) will be a row with three columns. I updated the code so now a row with 40 columns is created. See if that helps ? – JPV Oct 27 '16 at 18:30
It's creating 15 rows for each original data row for some reason? Is this just down to it being such a large data set? – JMKelley Oct 27 '16 at 23:55
@user3082823: can you maybe set up a sample spreadsheet with some fake data but still representative for what you try to achieve ? – JPV Oct 28 '16 at 08:51
Can you take a look at http://stackoverflow.com/questions/40468119/fuzzy-match-on-google-sheets I think you'll be able to help – JMKelley Nov 07 '16 at 16:42

DeeKay789 · Answer 2 · 2016-10-27T14:14:50.090

0

Would this work for you? It requires a free column to the left of Booking in the original data set. The formula below is a new sheet.

=ArrayFormula(sort({A2:A4,B2:B4,C2:C4;A2:A4,D2:D4,D2:D4;A2:A4,D2:D4,D2:D4;A2:A4,D2:D4,D2:D4},1,FALSE))

edited Oct 27 '16 at 14:14

answered Oct 27 '16 at 14:09

DeeKay789

353
2
4
8

This is also cloning the data in the rows. – JMKelley Oct 27 '16 at 14:11
... but it's a new sheet and I believe gives the result the OP shows. – DeeKay789 Oct 27 '16 at 14:16
@user3082823 It does however sort the original data which indeed was not what was asked for. – DeeKay789 Oct 27 '16 at 14:22

Google Sheets - Clone row but with only data from one cell

2 Answers2