var RTOs = [
  ["A3"],
  ["A8"]
  ];
function SetCellValue(input, value){


 var range = SpreadsheetApp.getActiveSpreadsheet().getRangeByName(input);

}


function HandleNA (){


  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Activity");

  for(var i = 3; i <= 8; i++){


    Logger.log(sheet.getRange("A"+i).getValue());

    if(sheet.getRange("A"+i).getValue() == "N/A"){

      sheet.getRange("C"+i + ":" + "H"+i).setValue("N/A");

    } else if(sheet.getRange("A"+i).getValue() != "N/A" && sheet.getRange("C"+i).getValue() == "N/A"){

      sheet.getRange("C"+i).setValue(0);
      sheet.getRange("H"+i).setValue(0);

    }

  }


}

function Test(){



}


function onMonday(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Activity");
  var RTOSheet = ss.getSheetByName("Activity");
  var RTOrange = RTOSheet.getRange(RTOs[0] + ":" + RTOs[1]);
  var maxValue = RTOSheet.getRange("K13").getValue();
  var RTO = sheet.getRange("C13").getValue();
  var CellToPut = sheet.getRange("L5").getValue();
  SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1MDUwDk0RXYqpmTbESsrs3lo0b-y5vr5d7wwQtiBkXew/edit#gid=0").getSheetByName("Public Roaster").getRange(CellToPut).setValue(RTO);


  var today = new Date();
var dd = today.getDate();
var mm = today.getMonth()+1; //January is 0!

var yyyy = today.getFullYear();
if(dd<10){
    dd='0'+dd;
}
if(mm<10){
    mm='0'+mm;
}
var today = dd+'/'+mm+'/'+yyyy;

  var thisWeek = "C2:C8";
  var lastWeek = "D2:D8";
  var twoweeks = "E2:E8";
  var threeWeeks = "F2:F8";
  var fourWeeks = "G2:G8";

  var thisWeekNumber = "C10";
  var lastWeekNumber = "D10";
  var twoWeeksNumber= "E10";
  var threeWeeksNumber="F10";
  var fourWeeksNumber="G10";

  sheet.getRange(fourWeeksNumber).setValue("");
  sheet.getRange(fourWeeksNumber).setValue(sheet.getRange(threeWeeksNumber).getValue());
  sheet.getRange(threeWeeksNumber).setValue(sheet.getRange(twoWeeksNumber).getValue());
  sheet.getRange(twoWeeksNumber).setValue(sheet.getRange(lastWeekNumber).getValue());
  sheet.getRange(lastWeekNumber).setValue(sheet.getRange(thisWeekNumber).getValue());




  var newNumber = sheet.getRange(thisWeekNumber).getValue() + 1;


  sheet.getRange(thisWeekNumber).setValue(newNumber);





  var thisWeekRange = sheet.getRange(thisWeek);
  var lastWeekRange = sheet.getRange(lastWeek);
  var twoWeeksRange = sheet.getRange(twoweeks);
  var threeWeeksRange = sheet.getRange(threeWeeks);
  var fourWeeksRange = sheet.getRange(fourWeeks);



  fourWeeksRange.setValue("");
  fourWeeksRange.setValues(threeWeeksRange.getValues());
  threeWeeksRange.setValues(twoWeeksRange.getValues());
  twoWeeksRange.setValues(lastWeekRange.getValues());
  lastWeekRange.setValues(thisWeekRange.getValues());
  thisWeekRange.setValue("");
  thisWeekRange.setValue("0");



   sheet.getRange("C2").setValue(today);


}



function onFormSubmit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 3");
  var RTOSheet = ss.getSheetByName("Activity");

  var lastRow = sheet.getLastRow();
  var name = sheet.getRange(lastRow, 2).getValue();
  var score = sheet.getRange(lastRow, 3).getValue();


  // Activity and Points


  var arr = [
   ["Interview + Basic Training", 3.5],
   ["Interview", 2],
   ["Training", 1.5],
   ["Assessed Patrol", 3]
  ];

  for(var i = 0; i<arr.length; i++){

    var curScore = arr[i][0];

    if(score == curScore){

      sheet.getRange(lastRow, 6).setValue(arr[i][1]);






  var RTOrange = RTOSheet.getRange(RTOs[0] + ":" + RTOs[1]);
  var RTOValues = RTOrange.getValues();

      for(var j = 0; j < RTOrange.getNumRows(); j++){




        if(RTOValues[j] == name){


          var oldScore = RTOSheet.getRange(j+3, 3).getValue();

          var newScore = oldScore + arr[i][1];

          var oldTotalScore = RTOSheet.getRange(j+3, 8).getValue();
          var newTotalScore = oldTotalScore + arr[i][1];

          RTOSheet.getRange(j+3, 8).setValue(newTotalScore);
          RTOSheet.getRange(j+3, 3).setValue(newScore);

          break;
        }


      }






      break;


    } else {

      sheet.getRange(lastRow, 6).setValue("ERROR! " + score + " is not equal to " + arr[i][0]);

    }

  }







}