get dotafromzero coach stats via email

email_monthly_coach_stats.gs

function email_Monthly_Coach_Stats() {
  
  // get the current sheet as a 2d array
  sheetData = getSheetData();
  
  // filter the data by current month
  sheetData = filterByCurrentMonth(sheetData);
  
  // process the sheet and get back a nicely formatted object for emailing
  sheetData = processData(sheetData);
  
  
  // email the data
  sendEmail(sheetData)
  
  log(sheetData);
  
}

helpers.gs

//testData = [['1.0', 'Witherspoon', '1v1', ['Witherspoon']], ['2.0', 'Witherspoon', 'Tryout', ['TANE','miyagi','Karkat']], ['3.0', 'Witherspoon', 'Tryout', ['TANE','miyagi','Karkat']]]


// turn helper logging on or off
var hlogging = false;


// helper logging
function hlog(data) {
  if (hlogging == true) {
    return Logger.log(data);
  }
}


// main file logging
function log(data) {
  return Logger.log(data);
}


// get sheet data as 2d array
function getSheetData() {
  
  // sheet as a 2d array
  var sheet = SpreadsheetApp.getActive().getActiveSheet().getDataRange().getValues();
  
  // remove headers
  sheet.shift();
  
  // remove long form date
  sheet = sheet.map(function(row) { 
    row.splice(1,1);
    return row;
  });
  
  // convert student cell from string to array
  sheet = sheet.map(function(row) {
    // if student cell has a single student create an array for that single value to stay consistent
    if(row[3].indexOf(",") == -1) {
      var temp = []
      temp.push(row[3]);
      row[3] = temp;
      return row;
    }
    else {
      // otherwise split up into an array based on commas
      row[3] = row[3].split(",");
      return row;
    }
  });
  
  // collect array values that are not empty
  sheet = sheet.filter(function(row){ return row[0].length > 0 });
  
  
  // convert date cell to an integer representing the month
  var sheet = sheet.map(function(row) { 
    row[0] = new Date(row[0]).getMonth() + 1;
    return row;
  
  })
  
  hlog(sheet);
  return sheet;
}



// takes 2d array and returns rows from the current month
function filterByCurrentMonth(sheetData) {
  
  // get the current month as an integer
  month = new Date().getMonth() + 1;
  
  // filter by month
  sheet = sheetData.filter(function(row) { 
    return row[0] == month;
  })
  
  
  hlog(sheet);
  return sheet;
}



// takes 2d array and returning an object of rows with key/values
function processData(sheetData) {  
  
  obj = sheetData.map(function(row) {
    row = {
      coach: row[1],
      sessionType: row[2],
      students: row[3]
    }
    return row;
  });
  
  hlog(obj);
  return obj;
}




// takes processed data and sends out a table of stats to the head coach
function sendEmail(sheetData) {

  var headCoachEmail = "johnyzaguirre@vassar.edu";

  var table = '<table style="width: 75%;" border="1" cellpadding="5"> <tbody> ';
  
  table += '<tr style="height: 21px;"> <td style="height: 21px;"><b>&nbsp;Coach</b></td> <td style="height: 21px;"><b>Session Type</b></td> <td style="height: 21px;"><b>Students</b></td> </tr>';
  
  // loop through processed data object and insert rows
  for (i = 0; i < sheet.length; i++){
    table += '<tr style="height: 21px;"> <td style="height: 21px;">' + sheetData[i]['coach'] + '</td> <td style="height: 21px;">' + sheetData[i]['sessionType'] + '</td> <td style="height: 21px;">' + sheetData[i]['students'] + '</td> </tr>';
  }
  

  table += ' </tbody> </table>';
  

  MailApp.sendEmail({
      to: "owen.m.h@gmail.com, " + headCoachEmail,
      subject: "DotaFromZero [NA] Coach's stats for the month are ready to view!",
      htmlBody: table
  });
}