Search code examples
performancegoogle-apps-scriptgoogle-sheetsbatch-processing

How to speed up if statements with for loop

I am trying to create an on-edit function in Google Apps Script that automatically checks/changes formatting for certain checkboxes on Sheet 2 ("interventionSheet") based on which checkboxes are selected on Sheet 1 ("sessionFocus"). Each checkbox on Sheet 1 is associated with its own unique group of checkboxes on Sheet 2. I have figured out how to do this by using repeated if statements, but this is slowing down the processing speed significantly. I am looking for help with how to use batch processing (arrays, for statements, etc.) to make the code more efficient. See current (inefficient) code below.

function populateInterventions(e) 
{
  var sessionFocus = SpreadsheetApp.getActive().getSheetByName('Session Focus');
  var interventionSheet = SpreadsheetApp.getActive().getSheetByName('Therapeutic Intervention');

  if(e.range.getA1Notation()=='B23')
  {
  var range3a = interventionSheet.getRangeList(['J78','Q69','Q71','Q72']);
  var range3b = interventionSheet.getRangeList(['J78','K78','Q69','R69','Q71','R71','Q72','R72']);

  if(sessionFocus.getRange('B23').isChecked()==true)
      { 
        range3a.check();
        range3b.setBackground('#cfe2f3');
        range3a.setBorder(null,null,null,true,null,null,'#cfe2f3',null);
      }
  else
      {
        range3a.uncheck();
        range3b.setBackground('#ffffff');
        range3a.setBorder(null,null,null,true,null,null,'#ffffff',null);
      }
  }

  if(e.range.getA1Notation()=='B24')
  {
  var range4a = interventionSheet.getRangeList(['J18','J20','J32','J84','J85']);
  var range4b = interventionSheet.getRangeList(['J18','K18','J20','K20','J32','K32','J84','K84','J85','K85']);

  if(sessionFocus.getRange('B24').isChecked()==true)
      { 
        range4a.check();
        range4b.setBackground('#cfe2f3');
        range4a.setBorder(null,null,null,true,null,null,'#cfe2f3',null);
      }
  else
      {
        range4a.uncheck();
        range4b.setBackground('#ffffff');
        range4a.setBorder(null,null,null,true,null,null,'#ffffff',null);
      }
  }

  if(e.range.getA1Notation()=='B26')
  {
  var range5a = interventionSheet.getRangeList(['J86','J87','N61','Q79','Q80']);
  var range5b = interventionSheet.getRangeList(['J86','K86','J87','K87','N61','O61','Q79','R79','Q80','R80']);

  if(sessionFocus.getRange('B26').isChecked()==true)
      { 
        range5a.check();
        range5b.setBackground('#cfe2f3');
        range5a.setBorder(null,null,null,true,null,null,'#cfe2f3',null);
      }
  else
      {
        range5a.uncheck();
        range5b.setBackground('#ffffff');
        range5a.setBorder(null,null,null,true,null,null,'#ffffff',null);
      }
  }
Solution

  • In this specific case it doesn't look to make sense using a loop because the script has code sections that only executes when an specific cell is edited.

    In order to make your scripts more efficients in terms of their execution time whenever is possible you have to minimize the calls to the Google Apps Script services.

    1. Avoid having calls to the Google Apps Script services in loops
    2. Take advantage of variables

    i.e. instead of

    if(e.range.getA1Notation()=='B23')
if(e.range.getA1Notation()=='B24')
if(e.range.getA1Notation()=='B26')

    before do a variable declaration

    const cellAddress = e.range.getA1Notation()

    then use the variable instead of doing multiple Google Apps Script service calls

    if(cellAddress=='B23')
if(cellAddress=='B24')
if(cellAddress=='B26')

    Regarding using arrays, for loops etc., that might help to write better scripts, making them more readable, maintainable, extensible, etc. but you might find that they will not have a really relevant impact on the script performance when using only the "basic" services (i.e. SpreadsheetApp).

    On more complex scripts it might be worthy to use Advanced Sheets Service in order to be able do multiple changes "at once" by using batchUpdate.

    Resources