1

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);};
  • Please edit your question and tell us what this is supposed to do. Some sample data before/after will also help. – user55949 Sep 27 at 7:39
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.