Copy and Paste Loop

I have 5 columns of data on sheet 1 (A-E) which I need to take the range (A1:E1), paste it into sheet 2 (Z5:AD5), retrieve a value from sheet 2 cell I3, then paste that value on sheet 1 cell F1. I have 51,751 rows that I need to do this for, but I feel my current code is insufficient, it takes a really long time to run (about 100 rows in 90 seconds). Can someone help me speed up / clean up my code.

function findNewPercentages() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Dist Numbers');
  var sheet2 = ss.getSheetByName('Pivot');
  var lastRow = sheet.getLastRow(); //51751
  for(var i = 2;i<=lastRow;i++){
  var cellValue = sheet.getRange(i, 1, 1, 5).getValues();
   var pivotPaste = sheet2.getRange(5,26,1,5).setValues(cellValue);
   var newPercent = sheet2.getRange(3,9,1,1).getValue();
   var newDistPerc = sheet.getRange(i,6,1,1).setValue(newPercent);
  }
}


Read more here: https://stackoverflow.com/questions/64894987/copy-and-paste-loop

Content Attribution

This content was originally published by Dennis Moran at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: