入力された予定を集計してセルを着色.gs

function aggregate() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  
  if(sheet == null){
    return;
  }
  
  for(var row = 2; row <= 40; row++){
    var okCount = 0;
    var pendingCount = 0;
    var ngCount = 0;
    
    var tankCount = 0;
    var healerCount = 0;
    
    for(var col = 2; col <= 9; col++){
      var value = sheet.getRange(row, col).getValue();
      
      if(value == "○"){
        okCount++;
        if(col == 2 || col == 3){
          tankerCount++;
        } else if(col == 8 || col == 9){
          healerCount++;
        }
      } else if(value == "△"){
        pendingCount++;
      } else if(value == "×"){
        ngCount++;
      }
    }
    
    if(okCount > 0 && pendingCount == 0 && ngCount == 0){
      sheet.getRange(row, 1).setBackgroundRGB(166, 255, 164);
    } else if(okCount > 0 && pendingCount > 0 && ngCount == 0){
      sheet.getRange(row, 1).setBackground("#F0E68C");
    } else {
      sheet.getRange(row, 1).setBackground("#eff3f4");
    }
    
     if(tankerCount + healerCount >= 2){
      sheet.getRange(row, 1).setFontColor("red");
    }
  }
}