woensdag 27 november 2019

Multidimensional array in Javascript; Comparing running times (slow, slower, slowst)

Today GAS filled 100.000 cells in a fresh & fruity Google Spreadsheet with some random stuff. It took some time before I realised that the variable row must be in the first loop and not before the loops.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
function fillSlowSlowerSlowst(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = ss.getSheetByName("SlowSlowerSlowst");
  
  var data = [];
  
  for(var h=1;h<1001;h++){
    var row = [];
    for (var i=1; i<101;i++){
      
      row.push(Math.random() * h * i);
      
    }
    
    data.push(row);
  }
  sheet.getRange(1,1,data.length,data[0].length).setValues(data);
}

Then GAS was running the next script, which was inspired by this blog of Dariusz Kuśnierek, every 5 minutes for a few hours.


 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
function countSlowSlowerSlowst(){
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();  
  var sheet = ss.getSheetByName("SlowSlowerSlowst");
  var sheetB = ss.getSheetByName("SlowSlowerSlowstVervolg");
  var seconds = []
  
  seconds.push((new Date()).getTime());
  
  var rangeGetLast = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn());
  var dataGetLast = rangeGetLast.getValues();
  
  seconds.push((new Date()).getTime());
  
  var rangeGetDataRage = sheet.getDataRange();
  var dataGetDatarange = rangeGetDataRage.getValues();
  
  seconds.push((new Date()).getTime());
  
  var rangeGetMax = sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns());
  var dataGetMax = rangeGetMax.getValues();
  
  seconds.push((new Date()).getTime());
      
  sheetB.appendRow(seconds)
  
}

And the winners are the siblings getMaxRows() & getMaxColumns()



Sources:
How to keep track of Google Apps Script’s total execution time, Dariusz Kuśnierek
Google Spreadsheet Formulas (Minus, Average)
Gepost door op

Geen opmerkingen:

Een reactie posten

Abonneren op: Reacties posten (Atom)