Increase my script perfomance and speed

Question

I was ran this script. It worked in more than 30 minutes. I don't know how to make it faster. May be have the way to reduce run time to 5-15 minutes .

strong textfunction sumData1() {  

var ss1 = SpreadsheetApp.getActive().getSheetByName('Pivot table');
var ss2 = SpreadsheetApp.getActive().getSheetByName('Summary data');
ss2.getRange('A:D').activate()
ss2.getActiveRangeList().clear({contentsOnly: true, skipFilteredRows: true});

ss2.setFrozenRows(1);
var value = [["Shop order","Part number","count Workstation","Workstation"]];

var range = ss2.getRange("A1:D1");
range.setValues(value);

var data = ss1.getRange(1, 1).getValue()  

for(var i = 1, j = 1; i < ss1.getLastRow()+1; i++) {

var normalCase = true
var aa = ss1.getRange(i, 4).getValue();
var bb = ss2.getRange(j, 3).getValue();
var cc = ss1.getRange(i, 3).getValue();
var dd = ss2.getRange(j, 4).getValue();
var ee = ss1.getRange(i, 1).getValue();
var ff = ss1.getRange(i, 2).getValue();

if(i!=1) {
  if(ss1.getRange(i, 1).getValue() == data) {
    normalCase = false
    ss2.getRange(j, 3).setValue(aa + bb)
    ss2.getRange(j, 4).setValue(cc+ " = " +aa+ ", " +dd)} 
  else {
    j++}}
if(normalCase) {
  ss2.getRange(j, 1).setValue(ee)
  ss2.getRange(j, 2).setValue(ff)
  ss2.getRange(j, 3).setValue(aa)
  ss2.getRange(j, 4).setValue(cc+ " = " +aa)
}
data = ss1.getRange(i, 1).getValue()}
ss2.deleteRow(2);};

Answer 1

Use batch operations instead of unit operations.

In other words, make an effort to avoid or reduce reading / writing of single cells specially in loops. When it is possible read all the values that you will need on a single operation, i.e. instead of using a for loop and getValue() to read a single value on each iteration use getValues() to read all the values on a single call. The same for writing values.

It will be very worthy if you spend some time learning about using JavaScript to handle arrays and 2D arrays.

Related

• Can this script be more optimized and why is it running slow sometimes?
• Optimizing Delete Rows Script