javascript - appsScript an elegant way to loop over a range

Question

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.

Answer 1

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