Google-sheets – Speeding up setFormula Google Apps Script

google sheetsgoogle-apps-script

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%");  

}

Best Answer

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

Related Solutions

Google Apps Script – Fixing Maximum Execution Time Exceeded

It's better not to call setValue in a loop, but instead form a double array of values to be inserted, and put them all at once with setValues. This often helps with keeping resource use under quota.

However, in your case the main issue is not setValue but UrlFetchApp.fetch in a loop. Hitting some API a bunch of times in a loop is an expensive thing to do, both for your script and the API provider. The maximum execution time of Apps Script is 6 minutes, after which the script is terminated with the message you quoted. To avoid this, follow Anton Soradoi's advice:

Store the necessary information (i.e. like a loop counter) in a spreadsheet or another permanent store (i.e. ScriptProperties).

Have your script terminate every five minutes or so.

Set up a time driven trigger to run the script every five minutes(or create a trigger programmatically using the Script service).

On each run read the saved data from the permanent store you've used and continue to run the script from where it left off.

Terminating a script means recording the starting time start = Date.now(), and then checking in the loop whether you are running over 5 minutes (start+5*60*1000). If this happens, exit the loop (hence the script) but set a trigger to resume work in a few minutes. Since you already work with a spreadsheet and record the results there, it will not be difficult to find the place where the script should pick up the work after restart.

Google Apps Script – Check Availability in Google Sheets and Calendar

The getEvents(startTime, endTime) method of the Calendar Class can be used. That method returns an array of events.

First you need to get a calendar and then chain the getEvents() method to the calendar.

var cal = CalendarApp.getDefaultCalendar();//Get a calendar
var array_Of_Events = cal.getEvents(startTime, endTime);

An array has a length property, which you can use to get the number of events that were found:

var number_Of_Events = array_Of_Events.length;//Get the number of events found

You can check the length with a conditional test:

if (theArray.length > 2) {
  //The code got to this line.  The length is 3 or more
  //Send an email to the user that the slots are already taken

  return;//Don't try to add a new calendar event - quit here
};

The documentation shows an example of how to check for the number of calendar events there are in a given time period.

Apps Script Documentation

function fncName() {
  var allEvents,startDt,endDt;

  startDt = sheet.getRange(lr,startDtId,1,1).getValue();
  endDt = sheet.getRange(lr,endDtId,1,1).getValue();

  allEvents = getEvents(startDt, endDt);//Returns an array of events

  if (allEvents.length > 2) {
    //The code got to this line.  The length is 3 or more
    //Send an email to the user that the slots are already taken

    return;//Don't try to add a new calendar event - quit here
  };
};

Best Answer

Related Solutions

Google Apps Script – Fixing Maximum Execution Time Exceeded

Google Apps Script – Check Availability in Google Sheets and Calendar

Related Topic