Speeding up setFormula Google Apps Script

Question

I have a script which does exactly what I intend it to do. It sets needed formats and especially it sets formulas to intended cells. However, the problem is that it's extremely slow. How can I speed it up? I've tried looking into using "getLastRow" or similar so I don't have to batch it to e.g. G4:G1000 but I can't wrap my head around it.

Here is the script:

function NumberFormat() {  

var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

    var range1 = sheet.getRange("G4:G1000");
  range1.setNumberFormat("yyyy-mm-dd");

    var range2 = sheet.getRange("H4:H1000");
  range2.setNumberFormat("hh:mm");

  var range3 = sheet.getRange("I4:I1000");
  range3.setFormula('=IF(G4="","",WEEKNUM(G4)&"/"&YEAR(G4))');

  var range4 = sheet.getRange("J4:J1000");
  range4.setFormula('=IF(G4="","",MONTH(G4)&"-"&YEAR(G4))');

  var range5 = sheet.getRange("U4:U1000");
  range5.setFormula('=IFERROR(SUMPRODUCT(--($K$2:$T$2=U$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,U$2,$K4:$T4,"<>N/A"),"")');
   range5.setNumberFormat("0.0%");

    var range6 = sheet.getRange("V4:V1000");
  range6.setFormula('=IFERROR(SUMPRODUCT(--($K$2:$T$2=V$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,V$2,$K4:$T4,"<>N/A"),"")');
     range6.setNumberFormat("0.0%");

  var range7 = sheet.getRange("W4:W1000");
  range7.setFormula('=IFERROR(SUMPRODUCT(--($K$2:$T$2=W$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,W$2,$K4:$T4,"<>N/A"),"")');
     range7.setNumberFormat("0.0%");  

   var range8 = sheet.getRange("X4:X1000");
  range8.setFormula('=IFERROR(SUMPRODUCT(--($K$2:$T$2=X$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,X$2,$K4:$T4,"<>N/A"),"")');
     range8.setNumberFormat("0.0%");

   var range9 = sheet.getRange("Y4:Y1000");
  range9.setFormula('=IFERROR(SUMPRODUCT(--($K$2:$T$2=Y$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,Y$2,$K4:$T4,"<>N/A"),"")');
     range9.setNumberFormat("0.0%");

   var range10 = sheet.getRange("Z4:Z1000");
  range10.setFormula('=IFERROR(SUMPRODUCT(--($K$2:$T$2<>"")*(LOWER($K4:$T4)<>"n/a"),$K$3:$T$3,$K4:$T4)/SUMIF($K4:$T4,"<>N/A",$K$3:$T$3),"")');
     range10.setNumberFormat("0.0%");  

}

Answer 1

Any function that operates on actual spreadsheet cells, are bound to be slow. So use them as little as possible. In your script, you have 19 such calls: getRange, setNumberFormats and setFormula.

Instead of getting 10 different ranges, and calling setFormula and setNumberFormat on each of them, get a single range, alter it, and write it back, using setNumberFormats and setFormulas (note the plural form).

There are API functions for getting arrays of existing formulas and number formats. This could serve as an example:

var range = sheet.getDataRange(); // Get a single, large range of all the cells in the sheet
var formulas = range.getFormulas();
var numberFormats = range.getNumberFormats();

// This script operates on two different 'ranges', as an example. Write similar FOR blocks for operations you need to perform.

// Example: Applying formatting and a formula to G4:G1000. 
for (var row = 3; row < 1000; row++) { // the array is 0-based, so 3 refers to row 4
   var column = 6; // refers to column G 
   formulas[row][column] = '=IF(G4="","",WEEKNUM(G4)&"/"&YEAR(G4))';
   numberFormats[row][column] = 'yyyy-mm-dd';
}

// Example: Applying formatting and formula to V4:V1000
for (var row = 3; row < 1000; row++) { // the array is 0-based, so 3 refers to row 4
   var column = 21; // refers to column V
   formulas[row][column] = '=IFERROR(SUMPRODUCT(--($K$2:$T$2=V$2)*($K4:$T4<>"N/A"),$K$3:$T$3,$K4:$T4)/SUMIFS($K$3:$T$3,$K$2:$T$2,V$2,$K4:$T4,"<>N/A"),"")'
   numberFormats[row][column] = "0.0%";
}

range.setFormulas(formulas); // Write the formulas to the actual cells
range.setNumberFormats(numberFormats); // Apply number formats to the actual cells

So here we have limited the number of API calls to 4:

• Get an array of existing formulas
• Get an array of existing number formats
• Write back an array of formulas
• Write back an array of number formats

... which should improve speed significantly.

See the documentation for setFormulas and setNumberFormats