Google app script for loop is extremely slow-CodePudding

I have a pivot table set up in a google sheet file where I've labeled the sheet pivot table 1. I want to get each value of the first column of the pivot, duplicate each values 12 times in an array and paste these values in the 3rd column of sheet 5. However, it seems extremely slow to do with the script never really completing (just takes 10 minutes before I've cancelled it).

The pivot has approximately 3000 lines, which would result in a 3000 * 12 = 36000 array.

Any thoughts on how I can optimize this?


function test2() {
  
// activating current spreadsheet for use
  var spreadsheet = SpreadsheetApp.getActive();
  //empty array
  var array_dept = []

  // returns (integer #) the last row of the pivot table 1 sheet that has content
  var last_row = spreadsheet.getSheetByName("Pivot Table 1").getLastRow();



  // Get value in pivot table 1 from range of row 1 (dept name), column 1, all the way to last row
  // Then paste it in sheet5 from row 1, column 3, all the way to the last row defined above
  
  

  for (var i = 1; i < last_row; i   )
  {
  //get value and then paste it in a destination
  var value_dept = spreadsheet.getSheetByName("Pivot Table 1").getRange(i,1).getValue();
  array_dept.fill(value_dept, -12   (12*i) , 12*i)
  }

destination_dept = spreadsheet.getSheetByName("Sheet5").getRange(1,3, last_row);
  
destination_dept.setValues(array_dept);


}

CodePudding user response：

You don't need use a loop if you know the first row and the last row on the source column. You can just define the range:

var pivotRange = pivot.getRange(1,1,last_row)
var targetRange = target.getRange(1,3,last_row)
doc ref; this is just one of five methods to define a range.

In the OP script, there would be 3000xgetRange 3000xgetValue. In this answer there are: 2xgetRange and 1 x getValue. This should account for a substantial amount of script processing. Of course, we know nothing of the rest of the spreadsheet (its size, formula, functions, triggers, etc). But all other things being equal, this should improve the performance of the script.

function test2() {
  
// activating current spreadsheet for use
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  var pivotSheetName = "Pivot Table 1"
  var pivot = spreadsheet.getSheetByName(pivotSheetName)

  //temporary array
  var array_dept = []

  // returns (integer #) the last row of the pivot table 1 sheet that has content
  var last_row = pivot.getLastRow();
  //Logger.log("DEBUG: last row in the pivot table:" last_row)
  var pivotRange = pivot.getRange(1,1,last_row)
  // Logger.log("DEBUG: the range for the pivot range = " pivotRange.getA1Notation())
  var pivotData = pivotRange.getValues()

  //Then paste it in sheet5 from row 1, column 3, all the way to the last row defined above
  var targetSheetName = "Sheet5"
  var target = spreadsheet.getSheetByName(targetSheetName)
  var targetRange = target.getRange(1,3,last_row)
  // Logger.log("DEBUG: the range for the target range = " targetRange.getA1Notation())
  targetRange.setValues(pivotData)
  Logger.log("Done")
}