CODEWITHSUNDEEP
javascriptarraysgoogle-apps-scriptgoogle-sheets
Cliff Stamp
Cliff Stamp

Reputation: 571

appsScript an elegant way to loop over a range

Is there an elegant way to do this :

function calculateTotal(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var total =0;
 
  for (i=3;i<=60;i++) {
    total=0;
    for (j=3;j<=234;j++) {
      if (sheet.getRange("B"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("B"+j).getFontLines() != 'line-through') ) {
        total++;
      }
      if (sheet.getRange("C"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("C"+j).getFontLines() != 'line-through') ) {
        total++;
      }
      if (sheet.getRange("D"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("D"+j).getFontLines() != 'line-through') ) {
        total++;
      }
      if (sheet.getRange("E"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("E"+j).getFontLines() != 'line-through') ) {
        total++;
      }
      if (sheet.getRange("F"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("F"+j).getFontLines() != 'line-through') ) {
        total++;
      }
      if (sheet.getRange("G"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("G"+j).getFontLines() != 'line-through') ) {
        total++;
      }
      if (sheet.getRange("H"+j).getValue() == sheet.getRange("J"+i).getValue() && 
         (sheet.getRange("H"+j).getFontLines() != 'line-through') ) {
        total++;
      }
    }
    sheet.getRange("K"+i).setValue(total);
  } 
}

In pseudo code it would be

for cellUpdate in range #the column being updated
  for cellScan in range #the rows and columns being scanned
    if isNotStruckOut(cellScann) total++
  cellUpdate = total

I can write functions to check for color which I need to, but there has to be a better way to loop over a column and then a sheet/range.

Upvotes: 0

Views: 129

Answers (2)

Cliff Stamp
Cliff Stamp

Reputation: 571

Based on the above, and talking to a friend of mine (Kyley Harris), here is more sensible way to do this loop :

function calculateTotals(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var total, clientID,cellValues,cellFontLines;

  for (i=3;i<=60;i++) {
    sheet.getRange("K"+i).setValue("calculating");
    clientID=sheet.getRange("J"+i).getValue();
    total=0;
    cellValues=sheet.getRange("B3:H234").getValues();
    cellFontLines=sheet.getRange("B3:H234").getFontLines();
    
    for (j=0;j<=6;j++) {
      for (k=0;k<=231;k++) {
        if (cellValues[k][j] == clientID && cellFontLines[k][j] != 'line-through' ) {
          total++;
        }
      }
    }
    sheet.getRange("K"+i).setValue(total);
  } 
}

The issue is with the calling, this script runs more than 10x faster, the other one would time out. If anyone knows any way to make this more elegant, please let me know.

Upvotes: 0

NightEye
NightEye

Reputation: 11214

Since we are just doing it again and again for B-H, let's just declare it in an array and then loop the array. This is the simplest approach and exactly the same with what you are doing (just shorter and less redundant)

function calculateTotal(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var total =0;
  var columns = ['B', 'C', 'D', 'E', 'F', 'G', 'H'];

  for (i=3;i<=60;i++) {
    total=0;
    for (j=3;j<=234;j++) {
      columns.forEach(function(item){
        if (sheet.getRange(item+j).getValue() == sheet.getRange("J"+i).getValue() && 
           (sheet.getRange(item+j).getFontLines() != 'line-through') ) {
          total++;
        }
      });
    }
    sheet.getRange("K"+i).setValue(total);
  } 
}

Upvotes: 2

Related Questions