CODEWITHSUNDEEP
google-sheets
sean
sean

Reputation: 119

Google sheets copy and paste row 120 times in the same sheet

So I have a sheet with about 800 rows. What I need to do is copy and paste each one 120 times. Instead of doing this manually I am hoping there is an automated way to do this in one shot. I have searched for a few things but everything I found had to do with copying the rows conditionally.

I don't need conditions. I just need all, each one of the 800 rows to be duplicated 120 times.

Any help or ideas is greatly appreciated.

Cheers

Upvotes: 0

Views: 880

Answers (2)

Jack Brown
Jack Brown

Reputation: 5892

Modified @SpiderPig code to clone in a interleaved fashion

function duplicateRows() {
      var sheet = SpreadsheetApp.getActiveSheet()
      var numRows = sheet.getLastRow()
      var numColumns = sheet.getLastColumn()
      var numberOfClones = 120
 for(var i = 0; i < numRows; i++) {
    range = sheet.getRange((i*numberOfClones)+ 1, 1, 1, numColumns);
    sheet.insertRows((i*numberOfClones)+ 2, numberOfClones-1)
    range.copyTo(sheet.getRange((i*numberOfClones)+ 2 , 1, numberOfClones-1, numColumns));
 }
}

Also instead of using a loop with range.copyTo(destination) to paste 120 times, you can expand the destination range to 120 rows. It will automatically paste the same value over the entire range.

So instead of this

for(var i = 1; i <= 120; i++) {
    range.copyTo(sheet.getRange(numRows * i + 1, 1, numRows, numColumns));
  }

you can do this once

    range.copyTo(sheet.getRange(numRows * i + 1, 1, 120*numRows, numColumns));

Upvotes: 2

Tesseract
Tesseract

Reputation: 8139

Here is a script that will copy all the rows in the current sheet 120 times.

function duplicateRows() {
  var sheet = SpreadsheetApp.getActiveSheet(),
      numRows = sheet.getLastRow(),
      numColumns = sheet.getLastColumn(),
      range = sheet.getRange(1, 1, numRows, numColumns);
  for(var i = 1; i <= 120; i++) {
    range.copyTo(sheet.getRange(numRows * i + 1, 1, numRows, numColumns));
  }
}

Upvotes: 1

Related Questions