Google Classroom Statistics > Graphs > SLT Email

[Shaun_Dark_Lord]

Introductory Notes;

This script is intended as an add-on for the “Managing Google Classroom with SIMS data in Powershell (In a MAT environment) V9” script.

 

The purpose of this script was to replicate some of the functionality provided by ShowMyHomework. SLT like to see which departments are using the technology, and regular, automated, graphical reports are missing from Google Classroom (Though I have created a change request).

 

The script creates five Google Image Charts (depreciated but still working for now) and three CSVs. The CSVs contain all of the raw data, so additional analysis can be performed if required. The Google Image charts show the top 10 Google Classroom Teachers and Departments (determined by number of assignments created) in the last week, and the state of all classrooms, as well as breakdowns by year group and department.

 

Requirements;

• A tested and operational GAMADV (4.83.04+) installation.
  
• A standardised naming convention for your classes, which include both the year group and the department. Here, our class names look like “YY#-DD#” where YY is the year group and DD is the department code. If you follow a similar convention, then the search parameters should be easily adjusted. If your class names are all over the place, then you might be in for a headache……..
  

That’s pretty much it. As mentioned, this script was written to run alongside the Google Classroom Management Script, but with a little tweaking could run without it. The only data it relies on is sourced directly from Google, so as long as to can identify your specific classes in a MAT environment, and can determine the year group and department from the class names, you’re good to go.

 

Google Image Charts;

As we wanted graphs to be automatically generated and emailed t SLT, we were limited to using an Image Charts service. It would have been nice to use HTML5 charts, but these all seem to rely on Java, and allowing Java to be run in an email client is obviously not advisable.

 

Google offered (past tense) a free Image Charts service. This service depreciated in 2012 and was “turned off” in March 2019 - It does still work for now, and if/when it does die, image-charts.com does offer a compatible free (watermarked) service, so we would just need to update the graph image urls.

 

The graphs created by this tool appear as normal images to the clients, so work on any device that can load an HTML email.

 

The API is a bit clunky, but the graphs are readable, and can be tested in the “Live Chart Playground” before embedding them in your script. The only limitation I’ve found is the maximum image size of 300000 pixels, which is why my Active department classes graph is so narrow.

 

 

What The Script Does;

• Lines 3-4 create arrays for the Year Groups and Departments that we expect to find in our Google Class Names.
  
• Lines 6-8 define our file paths, including the location of our GAMADV executable.
  
• Line 10 specifies the Google OU where our teachers reside.
  
• Line 11 specifies the prefix we use to name our Google classes in a MAT domain.
  
• Lines 12-15 specify our email parameters.
  
• Lines 21-37 determine the current date, current academic year, the expected Google class name prefix, and check/create the required folders.
  
• Lines 39-45 grab a list of all Google classes (If you’re running this straight after the Google Classroom Management script, you can comment out line 41, as you’ve just downloaded this data.) and count the number of classes matching the class name prefix and the number of active classes in this filtered list.
  
• Lines 47-58 create a csv file for storing active class data and the html file which will become the body of the email. Headers are written to each file.
  
• Lines 60-63 grabs a list of Teachers from the previously specified Google OU. Google account IDs are recorded, as these match against the coursework data we’ll be downloading later, and the name of the staff member to display in the graphs.
  
• Line 66 determines the date one week ago, and saves it in a format that matches the one used by Google in the coursework data.
  
• Lines 67-79 go through the Active class list created on line 44, and for each course, create a CSV of coursework created in the last week. I purposefully minimised the number of data fields, as by default, the coursework data can include a number of line breaks, which mess up the CSV. The CSV entries are then counted, and if no entries are found for a course, the associated CSV is removed.
  
• Lines 81-99 use the Department array created on in 4 to search for matching course CSVs created in lines 67-79. Each matching CSV is measured, and the results are written to a CSV. This CSV containing all department coursework totals is later attached to the email report.
  
• Lines 101-105 sort the Department Coursework CSV, sort the data and output the top 10 highest results to a new CSV.
  
• Lines 107-122 use the Top 10 highest department CSV to create a Google Image Chart. The Y axis range of the chart is determined by the highest value in the CSV. The Chart and associated title are then written to the HTML file.
  
• Lines 124-164 perform similar steps to determine Teacher department coursework totals, a Teacher Top 10 list and finally output a Google Image Chart. The notable difference is that the Image Chart is horizontal rather than vertical, so for some reason the chart labels must be recorded in reverse order.
  
• Lines 166-174 use the data generated in lines 39-45 to create an Image Chart for the total number of active/inactive classes in the school.
  
• Lines 176-198 filter the list of active/inactive classes by year group, and both update the CSV file created on line 47, and create an Image Chart containing this data.
  
• Lines 200-222 filter the list of active/inactive classes by department, and both update the CSV file created on line 47, and create an Image Chart containing this data.
  
• Lines 224-227 write the HTML file footer.
  
• Lines 229-238 send the email, using the HTML file as the message body, and attaching individual CSV files for Active Class Data, Department Coursework Weekly Totals and Individual Teacher Coursework Weekly Totals. The CSVs are intended to be used for further analysis by SLT if needed.
  
• 
  

[Shaun_Dark_Lord]

##Set preferences
#Class Settings
   $YearGroups = @('7','8','9','10','11','c6') ##Yeargroups as they appear in Google Class Names
   $Departments = @('Ar','Ax','Bi','Bu','Cd','Da','Dr','En','Fd','Fi','Fn','Fr','Fs','Ge','Gg','Hi','Hs','It','Ma','Mu','Pa','Pe','Po','Pv','Px','Py','Sc','Sp','Te') ##Departments as they appear in Google Class Names
#File Paths and Misc Settings
   $LogfilePath = ".\Logs\" ##Path where log files are created.
   $GAMPath = "C:\GAM\GAMADV\gam.exe" ##Path where GAMADV is installed.
   $GAMData = ".\GAMData\" ##Path where GAM data is recorded.
#School and Email Settings
   $orgUnitPath = "'/Staff/Teaching Staff'" ##GSuite OU where your teachers reside. (Don't forget to include the single quotes)
   $SchoolPrefix = "MySchool" ##Prefix to add to Class Alias to prevent MAT conflicts.
   $SMTPServer = "aspmx.l.google.com" ##SMTP server for emailing log files.
   $SMTPTo = "SLT " ##Recipient address for log files.
   $SMTPBcc = "Alerts " ##Recipient address for log files.
   $SMTPFrom = "IT Support " ##Sender address for log files.
##/Set preferences

##Determine Start Time
$StartTime = Get-Date

##Determine Current Academic Year to add to Class Alias prefix
$year=(Get-Date).Year
$month=(Get-Date).Month
if ($month -In 1..8) {$AcaYear = ($year -1)} else {$AcaYear = $year}
$ClassPrefix = $SchoolPrefix + $AcaYear + "_*"

##Check paths
if (Test-Path -Path $LogfilePath) {
   #"Log folder exists"
} else {
   New-Item $LogfilePath -type directory #Creates log folder.
}
if (Test-Path -Path $GAMData) {
   #"GAM Data folder exists"
} else {
   New-Item $GAMData -type directory #Creates log folder.
}

Write-Host "Grabbing Updated list of Google Classrooms....." -ForegroundColor Green
$GAMFinal = $GAMData + 'GAMFinal.csv'
& $GAMPath print courses state provisioned state active state declined aliases fields id fields name fields courseState owneremail > $GAMFinal 2> $null
$FinalList = Import-Csv -Path $GAMFinal | Where {$_.Aliases -like $ClassPrefix}
$EndCourses = $FinalList.count # Count Number of Classes after changes have been made.
$StatsActiveList = Import-Csv -Path $GAMFinal | Where {($_.Aliases -like $ClassPrefix) -and ($_.courseState -eq "ACTIVE")}
$ActiveCourses = @($StatsActiveList).count # Count Number of Classes after changes have been made.

$Statsfilename = Get-Date -UFormat "%Y%m%d"
$StatsHTMLFile = $Logfilepath + "Stats" + $Statsfilename + ".html"
$Statsfile = $Logfilepath + "Stats" + $Statsfilename + ".csv"
$StatsHeader = 'Focus,Scope,Active,Total'
"$StatsHeader" | Out-File $Statsfile -append

##StatsHTMLHeader
$StatsHTMLHeader = '


'
"$StatsHTMLHeader" | Out-File $StatsHTMLFile -append

##Grab List of teachers
Write-Host "Grabbing list of teachers....." -ForegroundColor Green
$GAMTeachers = $GAMData + 'GAMTeachers.csv'
& $GAMPath print users query orgUnitPath=$orgUnitPath fields id name primaryemail > $GAMTeachers 2> $null

##Grab Coursework and Submissions
$OneWeekAgo = "{0:yyyy-MM-dd}" -f (Get-Date).AddDays(-7) #To match Classroom time format (eg 2018-05-17T12:33:19.600Z)
foreach ($Course in $StatsActiveList) {
   $CourseID = $Course.id
   $CourseAlias = $Course.Aliases
   $CourseWorkCSV = $GAMData + 'Work_' + $CourseAlias + '.csv'
   Write-Host "Grabbing Coursework for $CourseAlias....." -ForegroundColor Green
   & $GAMPath print coursework course $CourseID fields courseid creatoruserid creationtime timefilter creationtime start $OneWeekAgo > $CourseWorkCSV 2> $null
   $WorkCountCSV = Import-Csv -Path $CourseWorkCSV
   $WorkCount = @($WorkCountCSV).count
   if ($WorkCount -eq 0) {
       #"No Work found for course $CourseAlias - Removing related CSV."
       Remove-Item $CourseWorkCSV
   }
}

##Create Department CSV
$CourseWorkCSVPrefix = 'Work_' + $ClassPrefix
$CourseWorkCSVs = Get-ChildItem $GAMData -Filter "$CourseWorkCSVPrefix"
$DTfilename = Get-Date -UFormat "%Y%m%d"
$DepartmentTotals = $Logfilepath + "DepartmentTotals" + $DTfilename + ".csv"
$DTHeader = 'Department,Work'
"$DTHeader" | Out-File $DepartmentTotals -append
foreach ($Department in $Departments) {
   $WorkCount = 0
   $Searchstring = "*-" + $Department + "*"
   $DepartmentCSVs = $CourseWorkCSVs.where({$_ -like $Searchstring})
   foreach ($DepartmentCSV in $DepartmentCSVs) {
       $DepartmentCSV = $GAMData + $DepartmentCSV
       $DepartmentCSV = Import-Csv -Path $DepartmentCSV
       $WorkCount = $WorkCount + @($DepartmentCSV).count
   }
   $DTData = $Department + ',' + $WorkCount
   "$DTData" | Out-File $DepartmentTotals -append
}

##Create Top 10 Department CSV
$DepartmentSorted = $Logfilepath + "DepartmentSorted" + $DTfilename + ".csv"
Import-Csv -Path $DepartmentTotals | Sort-Object { [int]$_.Work } –Descending | Export-Csv $DepartmentSorted -NoTypeInformation
$DepartmentTop10 = $Logfilepath + "DepartmentTop10" + $DTfilename + ".csv"
Get-Content $DepartmentSorted | select -First 11 | Out-File $DepartmentTop10

##Create Top 10 Department Graph
$GraphLabels = ""
$GraphData = ""
$GraphRange = 5 ##Set minimum graph axis range to suit data.
$DepartmentTop10CSV = Import-Csv -Path $DepartmentTop10
foreach ($Item in $DepartmentTop10CSV) {
   $Dept = $Item.Department
   $Work = $Item.Work
   if ($GraphRange -le [int]$Work) {$GraphRange = [int]$Work + 5} ##Adjust graph axis range to suit data.
   $GraphLabels = $GraphLabels + $Dept + '|'
   $GraphData = $GraphData + $Work + ','
}
$GraphLabels = $GraphLabels.Substring(0,$GraphLabels.Length-1) ##Remove trailing comma
$GraphData = $GraphData.Substring(0,$GraphData.Length-1) ##Remove trailing comma
$DeptTop10Graph = 'Department Work Set in Last Week - Top 10
'
"$DeptTop10Graph" | Out-File $StatsHTMLFile -append

##Create Teacher CSV
$TeacherTotals = $Logfilepath + "TeacherTotals" + $DTfilename + ".csv"
$TeacherHeader = 'Name,Work'
"$TeacherHeader" | Out-File $TeacherTotals -append
$TeacherList = Import-Csv -Path $GAMTeachers
$CourseWorkCSVs = Get-ChildItem $GAMData -Filter "$CourseWorkCSVPrefix"
foreach ($Teacher in $TeacherList) {
   $TeachID = $Teacher.id
   $TeachName = $Teacher."name.fullName"
   $WorkCount = 0
   foreach ($CourseWorkCSV in $CourseWorkCSVs) {
       $CourseWorkCSV = $GAMData + $CourseWorkCSV
       $CourseWorkCSV = Import-Csv -Path $CourseWorkCSV | Where {($_.creatorUserId -eq $TeachID)}
       $WorkCount = $WorkCount + @($CourseWorkCSV).count
   }
   $TeachData = $TeachName + ',' + $WorkCount
   "$TeachData" | Out-File $TeacherTotals -append
}

##Create Top 10 Teacher CSV
$TeacherSorted = $Logfilepath + "TeacherSorted" + $DTfilename + ".csv"
Import-Csv -Path $TeacherTotals | Sort-Object { [int]$_.Work } –Descending | Export-Csv $TeacherSorted -NoTypeInformation
$TeacherTop10 = $Logfilepath + "TeacherTop10" + $DTfilename + ".csv"
Get-Content $TeacherSorted | select -First 11 | Out-File $TeacherTop10

##Create Top 10 Teacher Graph
$GraphLabels = ""
$GraphData = ""
$GraphRange = 5 ##Set minimum graph axis range to suit data.
$TeacherTop10CSV = Import-Csv -Path $TeacherTop10
foreach ($Item in $TeacherTop10CSV) {
   $Name = $Item.Name
   $Work = $Item.Work
   if ($GraphRange -le [int]$Work) {$GraphRange = [int]$Work + 5} ##Adjust graph axis range to suit data.
   $GraphLabels = $Name + '|' + $GraphLabels ##Axis labels are reversed for some strange reason!
   $GraphData = $GraphData + $Work + ','
}
$GraphLabels = $GraphLabels.Substring(0,$GraphLabels.Length-1) ##Remove trailing comma
$GraphData = $GraphData.Substring(0,$GraphData.Length-1) ##Remove trailing comma
$TeachTop10Graph = 'Teacher Work Set in Last Week - Top 10
'
"$TeachTop10Graph" | Out-File $StatsHTMLFile -append

##Generate Total Active Class Data
"$ActiveCourses / $EndCourses Total courses active"
$StatsInfo = 'All,,' + $ActiveCourses + ',' + $EndCourses
"$StatsInfo" | Out-File $Statsfile -append

$ActiveCoursesPC = [math]::Round(((100 / $EndCourses) * $ActiveCourses),2)
$InactiveCoursesPC = (100 - $ActiveCoursesPC)
$TotalGraph = 'Total Active Classes
'
"$TotalGraph" | Out-File $StatsHTMLFile -append

##Generate Yeargroup Active Class Data
$GraphAxis = ""
$GraphAct = ""
$GraphInAct = ""
$GraphHeight = 20
foreach ($YearGroup in $YearGroups) {
   $Searchstring = "*_" + $YearGroup + "*-*"
   $StatsList = Import-Csv -Path $GAMFinal | Where {($_.Aliases -like $ClassPrefix) -and ($_.Aliases -like $Searchstring)}
   $Courses = @($StatsList).count # Count Number of Classes after changes have been made.
   $StatsActiveList = Import-Csv -Path $GAMFinal | Where {($_.Aliases -like $ClassPrefix) -and ($_.Aliases -like $Searchstring) -and ($_.courseState -eq "ACTIVE")}
   $ActiveCourses = @($StatsActiveList).count # Count Number of Classes after changes have been made.
   "$ActiveCourses / $Courses Year $YearGroup courses active."
   $StatsInfo = 'YearGroup,' + $YearGroup + ',' + $ActiveCourses + ',' + $Courses
   "$StatsInfo" | Out-File $Statsfile -append
   $GraphAxis = "Year+" + $YearGroup + "+(" + $ActiveCourses + "/" + $Courses + ")" + "|" + $GraphAxis ##Axis labels are reversed for some strange reason!
   $GraphAct = $GraphAct + $ActiveCourses + ","
   $GraphInAct = $GraphInAct + ($Courses - $ActiveCourses) + ","
   $GraphHeight = $GraphHeight + 27 ##Increase Graph Height
}
$GraphAct = $GraphAct.Substring(0,$GraphAct.Length-1) ##Remove trailing comma
$GraphInAct = $GraphInAct.Substring(0,$GraphInAct.Length-1) ##Remove trailing comma
$YearGraph = 'Yeargroup Active Classes
'
"$YearGraph" | Out-File $StatsHTMLFile -append

##Generate Department Active Class Data
$GraphAxis = ""
$GraphAct = ""
$GraphInAct = ""
$GraphHeight = 20
foreach ($Department in $Departments) {
   $Searchstring = "*-" + $Department + "*"
   $StatsList = Import-Csv -Path $GAMFinal | Where {($_.Aliases -like $ClassPrefix) -and ($_.Aliases -like $Searchstring)}
   $Courses = @($StatsList).count # Count Number of Classes after changes have been made.
   $StatsActiveList = Import-Csv -Path $GAMFinal | Where {($_.Aliases -like $ClassPrefix) -and ($_.Aliases -like $Searchstring) -and ($_.courseState -eq "ACTIVE")}
   $ActiveCourses = @($StatsActiveList).count # Count Number of Classes after changes have been made.
   "$ActiveCourses / $Courses $Department courses active."
   $StatsInfo = 'Department,' + $Department + ',' + $ActiveCourses + ',' + $Courses
   "$StatsInfo" | Out-File $Statsfile -append
   $GraphAxis = $Department + "+(" + $ActiveCourses + "/" + $Courses + ")" + "|" + $GraphAxis ##Axis labels are reversed for some strange reason!
   $GraphAct = $GraphAct + $ActiveCourses + ","
   $GraphInAct = $GraphInAct + ($Courses - $ActiveCourses) + ","
   $GraphHeight = $GraphHeight + 27 ##Increase Graph Height
}
$GraphAct = $GraphAct.Substring(0,$GraphAct.Length-1) ##Remove trailing comma
$GraphInAct = $GraphInAct.Substring(0,$GraphInAct.Length-1) ##Remove trailing comma
$DeptGraph = 'Department Active Classes
'
"$DeptGraph" | Out-File $StatsHTMLFile -append

##StatsHTMLFooter
$StatsHTMLFooter = '
'
"$StatsHTMLFooter" | Out-File $StatsHTMLFile -append

$EmailOptions = @{
   'SMTPServer' = $SMTPServer
   'To' = $SMTPTo
   'Bcc' = $SMTPBcc
   'From' = $SMTPFrom
   'Subject' = "Weekly Statistics from Google Classroom"
   'Attachments' = $Statsfile, $TeacherTotals, $DepartmentTotals
}
$body = Get-Content $StatsHTMLFile -Raw
Send-MailMessage @emailOptions -Body $body -BodyAsHtml

##Determine End Time
$TotalTime = $("{0:hh\:mm\:ss}" -f (New-TimeSpan -Start $StartTime -End $(Get-Date)))
Write-Host "Script completed in $TotalTime" -ForegroundColor Green

[Shaun_Dark_Lord]

Google very kindly introduced a "feature" this week, where the API now returns a list of all your classes including the deleted ones. As there's currently no way to identify the deleted classes (there's no "DELETED" status), Ross over at GAMADV has very quickly worked his magic, and updated GAM to compensate for classes that are listed, but not actually there.......

 

So, in a nutshell, update to at least GAMADV 4.86.07

[Cam_liddell]

I'm not well versed with Google API. However, I think there is a status for classes that are "Active" instead of those that are deleted.

[Shaun_Dark_Lord]

I'm not well versed with Google API. However, I think there is a status for classes that are "Active" instead of those that are deleted.

 

There is indeed - However, many of my "deleted" classes show their status as "Active"............ No response from Google yet, though they do appear to be working on the issue.