Google-sheets – Increase the script perfomance and speed

google sheetsgoogle-apps-script

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);};

Best Answer

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.

Google-sheets – How to speed up the Google Spreadsheet Script

A first thing to think about would be to grab all the data at once with range.getValues(), then grab the bits you need.

 var twoWeapons = AttackMath.getRange("C8").getValue()
 var twoWeaponTable = AttackMath.getRange("A30:A48")

would become

var attackMathRange = AttackMath.getRange("A1:C48").getValues()

// note that arrays are zero based
// so C maps to 2 and 8 maps to 7
var twoWeapons = attackMathRange[2][7] 

// slice to get a range of values
// slice gets all values from first index up to
// but NOT including the second index
var twoWeaponTable = attackMathRange[0].slice(29, 48)

Every call you can reduce will speed up the script. You could even just get the entire range of values on a sheet, make adjustments appropriately, then use range.setValues once all changes are complete.

Google-sheets – Script to insert new row and copy down formulas that auto increase according to row index

Instead of getFormulas and setFormulas, use copyTo method:

range.copyTo(newRange);

This will have the same effect as if you selected the last row and copied it below. In particular, it will also copy down any values that were entered directly into cells. If you prefer such values to not be copied, use getFormulas/setFormulas combination on newRange:

range.copyTo(newRange);
newRange.setFormulas(newRange.getFormulas());

The effect of the last line: no change of formulas, but the cells that didn't contain a formula will become empty.

R1C1 approach

You can also try replacing the last three rows of addRow with

var formulas = range.getFormulasR1C1();
var newRange = sh.getRange(lRow+1,1,1,lCol);
newRange.setFormulasR1C1(formulas);

This will make sure the references in formulas are remapped as if you copy-pasted them. However, this has a major limitation: setFormulasR1C1 doesn't work correctly with empty string as its argument, which will result in #ERROR! messages in the cells that should be left blank.

The reason R1C1 works differently is that getFormulasR1C1 retrieves formulas in a relative form: for example, a reference from A3 to D2 becomes R[-1]C[3], meaning "one row up, three columns to the right". When the formula is set to, for example, E4 with setFormulasR1C1, the cell reference will appear as H3.

Best Answer

Related Solutions

Google-sheets – How to speed up the Google Spreadsheet Script

Google-sheets – Script to insert new row and copy down formulas that auto increase according to row index

R1C1 approach

Related Topic