Averaging values pulled from sheets in an array

Question

I am trying to pull student scores from a sheet, average them for each student then output them to a new tab in the same sheet. Each student is scored by eight teachers on their character and leadership.

I am not a programer by trade so I'm learning as I go. I have figured out using the Google sheet scripting tool how to add the student and their scores into an array and output them to a new tab in sheets. I have found examples of code on how to average the numbers in an array but I haven't been able to get any of them to work. I'm assuming I need to add the code to average the values before doing the output, but all the examples I find seem to be in different programming languages than what sheets uses as I get errors when I put an any of the code I find. This is what I have working to get the students and the scores into a new tab.

function CharAvg(){

    var app = SpreadsheetApp;
    var sheet = app.getActiveSpreadsheet().getSheetByName("Sheet1");

    var map = {};

    for(var row = 3; row <=297; row++)
    {
        var studentname = sheet.getRange(row, 2).getValues();

        if(studentname != " " && studentname != null && studentname != "")
        {
            if(!(studentname in map))
            {
                map[studentname] = [];
            }

            var character = sheet.getRange(row, 3).getValue();


            if(character != " " && character != null)
            {
                map[studentname].push(character);
            }

            var leadership = sheet.getRange(row, 5).getValue();

            if(leadership != " " && leadership != null)
            {
                map[studentname].push(leadership);
            }

        }
        else
        {
            Browser.msgBox(studentname);
        }
    }

    var outputsheet = app.getActiveSpreadsheet().insertSheet("OutPut");
    outputsheet.getRange(1, 1).setValue("Student Name");
    outputsheet.getRange(1, 2).setValue("Character Avg.");
    outputsheet.getRange(1, 3).setValue("Leadership Avg.");
    var row = 2;
    for (var studentname in map)
    {
        outputsheet.getRange(row, 1).setValue(studentname);

        for(var character in map[studentname])
        {

            outputsheet.getRange(row, 2).setValue(map[studentname][character]);
            for(var leadership in map[studentname])
            {
                outputsheet.getRange(row, 3).setValue(map[studentname][leadership]);
            }

            row ++
        }

        row ++
    }

}

If anyone could help me figure out how to do this in a Google sheet script it would make our lives easier. Otherwise we can get by writing a formula in the sheet manually to get the averages, but we would like to automate the process as much as possible. Thank you.

input

Answer 1

I did not see where you are doing any averaging. But this script does what I think you were trying to do.

function CharAvg() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Sheet123");
  //var rg=sh.getRange(3,1,295,5);//This was your range
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());//this was for my data
  var vA=rg.getValues();
  var sA=[];//student array
  var index=0;
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {vA[i][j]=vA[i][j].toString().trim();}
    if(vA[i][1] && vA[i][2] && vA[i][4]) {
      sA.push({studentName:vA[i][1],character:vA[i][2],leadership:vA[i][4]});//each array element is an object  
    }else{
      SpreadsheetApp.getUi().alert('Student Name: ' + vA[i][1]);
    }
  }
  //var osh=ss.insertSheet("outPut");//This was your output spelled differently
  var osh=ss.getSheetByName('Sheet117');//This was for my data
  osh.appendRow(['Student Name','Character Avg','Leadership Avg']); //appending header
  for(var i=0;i<sA.length;i++) {
    osh.appendRow([sA[i].studentName,sA[i].character,sA[i].leadership]);//appending rows  
  }
}

My input sheet:

My output sheet:

I presume that your data is already averaged because I didn't see any averaging process at all. This approach will run much faster because I just get data once and process it all in one array and then output the lines by appending them.

Does this look like the input data sheet

Using the above table for input and the following code:

Calculating Student Averages

function CharAvg() {
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName("Sheet118");
  //var rg=sh.getRange(3,1,295,5);//This was your range
  var rg=sh.getRange(2,1,sh.getLastRow()-1,sh.getLastColumn());//this was for my data
  var vA=rg.getValues();
  var sA=[];//student array
  var sObj={sA:[]};
  var index=0;
  for(var i=0;i<vA.length;i++) {
    for(var j=0;j<vA[i].length;j++) {vA[i][j]=vA[i][j].toString().trim();}
    if(vA[i][1] && vA[i][2] && vA[i][4] && vA[i][5]) {
      if(sObj.hasOwnProperty(vA[i][1])) {
        sObj[vA[i][1]].character=Number(sObj[vA[i][1]].character)+Number(vA[i][2]);
        sObj[vA[i][1]].leadership=Number(sObj[vA[i][1]].leadership)+Number(vA[i][4]);
        sObj[vA[i][1]].submitted=Number(sObj[vA[i][1]].submitted)+Number(vA[i][5]);
        sObj[vA[i][1]].count++;
      }else{
        sObj[vA[i][1]]=new Score(vA[i][2],vA[i][4],vA[i][5]);
        sObj.sA.push(vA[i][1]);
      }

      sA.push({studentName:vA[i][1],character:vA[i][2],leadership:vA[i][4]});//each array element is an object  
    }else{
      SpreadsheetApp.getUi().alert('Student Name: ' + vA[i][1]);
    }
  }
  var osh=ss.getSheetByName('Sheet126');//This was for my data
  osh.clearContents();
  osh.appendRow(['Student Name','Character Avg','Leadership Avg','Submitted Avg','Count']); //appending header
  for(var i=0;i<sObj.sA.length;i++) {
    var row=[sObj.sA[i]].concat(sObj[sObj.sA[i]].row());
    osh.appendRow(row);
  }
}

function Score(character,leadership,submitted) {
  if(character && leadership && submitted) {
    this.character=character;
    this.leadership=leadership;
    this.submitted=submitted;
    this.count=1;
    this.characterAvg=function(){return Number(this.character)/Number(this.count);};
    this.leadershipAvg=function(){return Number(this.leadership)/Number(this.count);};
    this.submittedAvg=function(){return Number(this.submitted)/Number(this.count);};
    this.row=function(){return [this.characterAvg(),this.leadershipAvg(),this.submittedAvg(),this.count];}
  }
}

Table of Averages: