SIMS report -> Google Classroom

Shaun_Dark_Lord · February 26, 2018

In a previous post I showed how I'm grabbing SIMS data to create AD accounts using powershell. That was a side-quest to this, my main objective of automatically creating Google Classrooms using SIMS data.

I'm already using GCDS to sync all my staff and students over to G-Suite. That was fairly straightforward to do.

I was initially just going to allow staff to create their own classrooms, and use the SIMS data to populate distribution lists with students so that a teacher could invite their entire class in one simple step. However, at this moment there is not a simple/fast way to update the required permissions on these groups, so I decided to just create and pre-populate all of the classes myself. However, as I had already created all of the distribution groups, I used these to determine class membership. The powershell script I'm using could be adapted to bypass this step and just compare two reports.

To export the reports, I'm using CommandReporter. See my previous post for the permissions and command line I use.

The first SIMS report I created contains the Student UPNs and Class code;

This needed some reformatting as some invalid characters have been used in our SIMS class codes. This reformatting is all done automatically in the first few lines of the powershell script.

The second SIMS report contains the same class codes and the full name of the class teacher;

Lines 64-82 of the powershell script deal with removing titles from the staff name to match our staff usernames (initial+surname) and again reformat the class code to match those in the previous report.

To interact with Google Classroom, I'm using GAM. I've only scratched the surface of what this tool can do, and I'm looking forward to experimenting with it further.

The powershell script;

##Import and modify SIMS Report
$csvInFile="\\SIMSSERVER\Sharedfolder$\UPNClasses.csv"
$csvOutFile="C:\UserCreate\GoogleGroups\UPNClasses.csv"
#This gives me new lines after each $
Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile

Import-Module ActiveDirectory

$dnsroot = '@' + (Get-ADDomain).dnsroot

##Creates mail distribution group for each class
$OU = "OU=Students,OU=CCW Users,DC=ccw,DC=kent,DC=sch,DC=uk"
$GroupOU = "OU=Classes,OU=CCW Groups,DC=ccw,DC=kent,DC=sch,DC=uk"
Import-Module ActiveDirectory
$FileList = Get-ADUser -SearchBase $OU -Filter *
foreach ($User in $FileList){
   $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
   $FullName = $UserInfo.displayname
   $SAM = $UserInfo.samAccountName
   $UPN = $UserInfo.POBox

   if (!$UserInfo.POBox) {
       "UPN Missing from user $SAM"
   }
   ELSE {
       #"Looking for $UPN"
       $ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\UPNClasses.csv'
       $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.POBox })
       foreach ($Item in $FilteredCL){
           $Class = $Item.Class
           if (!$Class) {"Group name blank"} else {
               if (dsquery group -name $Class) {"Group $Class already exists"} else {
                   $GroupEmail = $Class + "@theccw.net"
                   "Creating Group $Class with address $GroupEmail in path $GroupOU"
                   New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU 
               }
               Add-ADGroupMember -Identity $Class -Members $SAM
           }
       }

   }


}

##Cleanup Empty Groups
$EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} 
foreach ($EmptyGroup in $EmptyGroups){ 
   $DN = $EmptyGroup.DistinguishedName 
   "Removing Empty Group $DN"
   Remove-ADObject -Identity $DN -Confirm:$False
}

#$Logfilename = Get-Date -UFormat "%Y%m%d"
#$Logfilepath = "C:\UserCreate\GoogleGroups\Logs\"
#$Logfile = $Logfilepath + $Logfilename + ".csv"

##Grab Class Teachers
##Import and modify SIMS Report
$csvInFile="\\SIMSSERVER\sharedfolder$\ClassTeachers.csv"
##Change Header
Get-Content $csvInFile -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content "C:\UserCreate\GoogleGroups\ClassTeachersTemp.csv"
##Reformat Teacher Names
$Names = import-csv "C:\UserCreate\GoogleGroups\ClassTeachersTemp.csv"
$Names | foreach-object {
   $_.FullName = $_.FullName.replace("Mr ","") #Get rid of titles
   $_.FullName = $_.FullName.replace("Mrs ","")
   $_.FullName = $_.FullName.replace("Ms ","")
   $_.FullName = $_.FullName.replace("Miss ","")
   $_.FullName = $_.FullName.replace(" ","") #Get rid of spaces
   $_.FullName = $_.FullName.replace("-","")
   $_.FullName = $_.FullName.replace("BSmith","WSmith") #If you have any staff whose legal names don't match their usernames, you can swap them one at a time like this.
   }
$Names | export-csv "C:\UserCreate\GoogleGroups\ClassTeacherSAM.csv" -notype

$csvInFile="C:\UserCreate\GoogleGroups\ClassTeacherSAM.csv"
$csvOutFile="C:\UserCreate\GoogleGroups\ClassTeachers.csv"
#Reformat class/teacher list
Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

##Create the classes!
$ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\ClassTeachers.csv'
$FilteredCL = ($ClassList | Where {$_.FullName -ne "" })
foreach ($Item in $FilteredCL){
   $Class = $Item.Class
   $SAM = $Item.FullName
   $TeachEmail = $SAM + "@theccw.net"
   if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) {
       "Creating class $Class with teacher $TeachEmail"
       ##Write to log file##
       #$Loginfo = $Class + ',' + $TeachEmail
       #"$Loginfo" | Out-File $Logfile -append
       & "C:\GAM\gam.exe" create course alias $Class name $Class teacher $TeachEmail
       ##Get Class Students
       $Students = Get-ADGroupMember $Class
       foreach ($Student in $Students) {
           $UserInfo = Get-ADUser -Identity $Student -Properties *
           $StudName = $UserInfo.displayname
           $StudUsername = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           #"Adding Student $StudEmail to class $Class"
           & "C:\GAM\gam.exe" course $Class add student $StudEmail
           ##Write to log file##
           #$Loginfo = $Class + ',' + $StudEmail
           #"$Loginfo" | Out-File $Logfile -append
       }
   }
   else {
       "Class $Class does not contain any students or Teacher does not exist"
   }
   #"Adding $SAM to group $Class"
   #Add-ADGroupMember -Identity $Class -Members $SAM
   #Remove-ADGroupMember -Identity $Class -Members $SAM
}

It takes a few hours to run, but I end up with a mail distribution group for every class (that can't be used to send class invites ) and all of my classes setup in Google Classroom ready for the teacher to activate.

Again, I hope that this is of use to someone else. I was planning to write a longer thread with more annotation, but winter is coming!

Shaun_Dark_Lord · March 5, 2018

Added lines 62-93 to remove students from classrooms if they are no longer members of the class. There may be other tweaks, but it was very late at night when I finally got it running, and I did doze off a few times whilst the test scripts were running.

$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfilepath = "C:\UserCreate\GoogleGroups\Logs\"
$Logfile = $Logfilepath + $Logfilename + ".txt"

##Clear old class data
$Classfilepath = "C:\UserCreate\GoogleGroups\ClassData\"
Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force

##Import and modify SIMS Report
$csvInFile="\\SIMSServer\ReportShare$\UPNClasses.csv"
$csvOutFile="C:\UserCreate\GoogleGroups\ClassData\UPNClasses.csv"
#This gives me new lines after each $
Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot


$OU = "OU=Students,OU=Users,DC=mydomain,DC=sch,DC=uk"
$GroupOU = "OU=Classes,OU=Groups,DC=mydomain,DC=sch,DC=uk"
$FileList = Get-ADUser -SearchBase $OU -Filter *
foreach ($User in $FileList){
   $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
   $FullName = $UserInfo.displayname
   $SAM = $UserInfo.samAccountName
   $UPN = $UserInfo.POBox

   if (!$UserInfo.POBox) {
       "UPN Missing from user $SAM"
   }
   ELSE {
       #"Looking for $UPN"
       $ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\ClassData\UPNClasses.csv'
       $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.POBox })
       foreach ($Item in $FilteredCL){
           $Class = $Item.Class
           if (!$Class) {"Group name blank"} else {
               $Classfile = $Classfilepath + $Class + ".csv"
               if((Test-Path -Path $Classfile )){ #Check to see if file has already been created
                   #"Class file already exists"
               } else {
                   $Classhead = 'UPN,ADUser'
                   "$Classhead" | Out-File $Classfile -append
               }

               if (dsquery group -name $Class) {"Group $Class already exists"} else {
                   ##Write to log file##
                   $Loginfo = 'Created group ' + $Class
                   "$Loginfo" | Out-File $Logfile -append 
                   $GroupEmail = $Class + "@mydomain.com"
                   "Creating Group $Class with address $GroupEmail in path $GroupOU"
                   New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU 
               }
               Add-ADGroupMember -Identity $Class -Members $SAM
               $StudData = $UserInfo.POBox + ',' + $SAM
               "$StudData" | Out-File $Classfile -append
           }
       }
   }
}

##Cleanup Groups
$GroupList = Get-ADGroup -filter * -searchbase $GroupOU
foreach ($Group in $GroupList){
   $GN = $Group.Name
   $DN = $EmptyGroup.DistinguishedName
   $Classfile = $Classfilepath + $GN + ".csv"
   if((Test-Path -Path $Classfile)){
       #"Group file for $GN exists"
       $GroupMembers = Get-ADGroupMember $GN
       foreach ($GroupMember in $GroupMembers) {
           $UserInfo = Get-ADUser -Identity $GroupMember -Properties *
           $SAM = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           $ClassList = Import-CSV -Path $Classfile
           if ($ClassList.ADUser -match $SAM ) {
               #"User $SAM found in class list for $GN"
           } else {
               #"User $SAM no longer member of $GN"
               ##Write to log file##
               $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN
               "$Loginfo" | Out-File $Logfile -append
               ##Remove User from Class Group
               Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false
               ##Remove User from Google Classroom
               & "C:\GAM\gam.exe" course $GN remove student $StudEmail
           }
       }

   } else {
       "Group file for $GN does not exist"
   }
}

$EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} 
foreach ($EmptyGroup in $EmptyGroups){
   ##Write to log file##
   $Loginfo = 'Removed empty group ' + $Class
   "$Loginfo" | Out-File $Logfile -append 
   $DN = $EmptyGroup.DistinguishedName 
   "Removing Empty Group $DN"
   Remove-ADObject -Identity $DN -Confirm:$False
}

##Grab Class Teachers
##Import and modify SIMS Report
$csvInFile="\\SIMSServer\ReportShare$\ClassTeachers.csv"
##Change Header
Get-Content $csvInFile -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content "C:\UserCreate\GoogleGroups\ClassData\ClassTeachersTemp.csv"
##Reformat Names
$Names = import-csv "C:\UserCreate\GoogleGroups\ClassData\ClassTeachersTemp.csv"
$Names | foreach-object {
   $_.FullName = $_.FullName.replace("Mr ","")
   $_.FullName = $_.FullName.replace("Mrs ","")
   $_.FullName = $_.FullName.replace("Ms ","")
   $_.FullName = $_.FullName.replace("Miss ","")
   $_.FullName = $_.FullName.replace(" ","")
   $_.FullName = $_.FullName.replace("-","")
   $_.FullName = $_.FullName.replace("MWonford","EWonford")
   $_.FullName = $_.FullName.replace("BBills","WBills")
   }
$Names | export-csv "C:\UserCreate\GoogleGroups\ClassData\ClassTeacherSAM.csv" -notype
##Remove Temp Files
##Remove-Item -Path "C:\UserCreate\GoogleGroups\ClassTeachersTemp.csv" -Force -Recurse

$csvInFile="C:\UserCreate\GoogleGroups\ClassData\ClassTeacherSAM.csv"
$csvOutFile="C:\UserCreate\GoogleGroups\ClassData\ClassTeachers.csv"
##Reformat class/teacher list
Get-Content $csvInFile -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $csvOutFile

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

##Create the classes!
$ClassList = Import-Csv -Path 'C:\UserCreate\GoogleGroups\ClassData\ClassTeachers.csv'
$FilteredCL = ($ClassList | Where {$_.FullName -ne "" })
foreach ($Item in $FilteredCL){
   $Class = $Item.Class
   $SAM = $Item.FullName
   $TeachEmail = $SAM + "@mydomain.com"
   if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) {
       "Creating class $Class with teacher $TeachEmail"
       ##Write to log file##
       #$Loginfo = 'Created ' + $Class + ' with ' + $TeachEmail
       #"$Loginfo" | Out-File $Logfile -append
       & "C:\GAM\gam.exe" create course alias $Class name $Class teacher $TeachEmail
       & "C:\GAM\gam.exe" course $Class add teacher $TeachEmail
       ##Get Class Students
       $Students = Get-ADGroupMember $Class
       foreach ($Student in $Students) {
           $UserInfo = Get-ADUser -Identity $Student -Properties *
           $StudName = $UserInfo.displayname
           $StudUsername = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           #"Adding Student $StudEmail to class $Class"
           & "C:\GAM\gam.exe" course $Class add student $StudEmail
           ##Write to log file##
           #$Loginfo = $Class + ',' + $StudEmail
           #"$Loginfo" | Out-File $Logfile -append
       }
   }
   else {
       "Class $Class does not contain any students or Teacher does not exist"
   }
}

Shaun_Dark_Lord · March 12, 2018

Tidied things up a bit, moved the variables to the top of the script, added a check to make sure the student data exists.

##Set paths and preferences
$LogfilePath = ".\Logs\" ##Path where log files are created.
$Classfilepath = ".\ClassData\" ##Path where data file is created.
$SIMSUPNReport = "\\SIMSServer\ReportShare$\UPNClasses.csv" ##Report containing list of current students
$SIMSTeachList = "\\SIMSServer\ReportShare$\ClassTeachers.csv"
$ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report
$SearchOU = "OU=Students,OU=Users,DC=MySchool,DC=sch,DC=uk" #OU Containing existing Students
$GroupOU = "OU=Classes,OU=Groups,DC=MySchool,DC=sch,DC=uk"
$StudMail = "@MySchool.com" ##Mail domain for student accounts
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.myschool.com" ##SMTP server for emailing log files.
$SMTPTo = "Me " ##Recipient address for log files.
$SMTPFrom = "someone " ##Sender address for log files.
##/Set paths and preferences

## Check that source data exists - Running without source data would be bad!
if ((Test-Path -Path $SIMSUPNReport) -And (Get-Item $SIMSUPNReport).Length -gt $ReportMinSize) {

$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfile = $Logfilepath + $Logfilename + ".txt"

##Clear old class data
Write-Host "Clearing old data files...." -ForegroundColor Green
Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force

##Import and modify SIMS Report
Write-Host "Importing SIMS data from $SIMSUPNReport" -ForegroundColor Blue
$NewSIMSUPNReport = $Classfilepath + "UPNClasses.csv"
#This gives me new lines after each $
Write-Host "Modifying SIMS data." -ForegroundColor Blue
Get-Content $SIMSUPNReport -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSUPNReport

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

Write-Host "Grabbing students from $SearchOU" -ForegroundColor Green
$FileList = Get-ADUser -SearchBase $SearchOU -Filter *
foreach ($User in $FileList){
   $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
   $FullName = $UserInfo.displayname
   $SAM = $UserInfo.samAccountName
   $UPN = $UserInfo.$UPNVariable

   if (!$UserInfo.$UPNVariable) {
       "UPN Missing from user $SAM"
   }
   ELSE {
       #"Looking for $UPN"
       $ClassList = Import-Csv -Path $NewSIMSUPNReport
       $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.$UPNVariable })
       foreach ($Item in $FilteredCL){
           $Class = $Item.Class
           if (!$Class) {"Group name blank"} else {
               $Classfile = $Classfilepath + $Class + ".csv"
               if((Test-Path -Path $Classfile )){ #Check to see if file has already been created
                   #"Class file already exists"
               } else {
                   $Classhead = 'UPN,ADUser'
                   "$Classhead" | Out-File $Classfile -append
               }

               if (dsquery group -name $Class) {
                   #Write-Host "Group $Class already exists" -ForegroundColor Blue
               } else {
                   ##Write to log file##
                   $Loginfo = 'Created group ' + $Class
                   "$Loginfo" | Out-File $Logfile -append 
                   $GroupEmail = $Class + $StudMail
                   Write-Host "Creating Group $Class with address $GroupEmail in path $GroupOU" -ForegroundColor Green
                   New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU 
               }
               ##Check if user is already group member
               $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty Name
               If ($Members -contains $SAM) {
                   #"User $SAM already member of $Class"
                   #Add-ADGroupMember -Identity $Class -Members $SAM
                   $StudData = $UserInfo.$UPNVariable + ',' + $SAM
                   "$StudData" | Out-File $Classfile -append
               } else {
                   Write-Host "Adding $SAM to group $Class" -ForegroundColor Green
                   $Loginfo = 'Added student ' + $SAM + " to " + $Class
                   "$Loginfo" | Out-File $Logfile -append
                   Add-ADGroupMember -Identity $Class -Members $SAM
                   $StudData = $UserInfo.$UPNVariable + ',' + $SAM
                   "$StudData" | Out-File $Classfile -append
               }
           }
       }
   }
}

##Cleanup Groups
Write-Host "Removing old students from groups." -ForegroundColor Green
$GroupList = Get-ADGroup -filter * -searchbase $GroupOU
foreach ($Group in $GroupList){
   $GN = $Group.Name
   $DN = $EmptyGroup.DistinguishedName
   $Classfile = $Classfilepath + $GN + ".csv"
   if((Test-Path -Path $Classfile)){
       #"Group file for $GN exists"
       $GroupMembers = Get-ADGroupMember $GN
       foreach ($GroupMember in $GroupMembers) {
           $UserInfo = Get-ADUser -Identity $GroupMember -Properties *
           $SAM = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           $ClassList = Import-CSV -Path $Classfile
           if ($ClassList.ADUser -match $SAM ) {
               #"User $SAM found in class list for $GN"
           } else {
               Write-Host "User $SAM no longer member of $GN" -ForegroundColor DarkGreen
               ##Write to log file##
               $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN
               "$Loginfo" | Out-File $Logfile -append
               ##Remove User from Class Group
               Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false
               ##Remove User from Google Classroom
               & "C:\GAM\gam.exe" course $GN remove student $StudEmail
           }
       }

   } else {
       Write-Host "Group file for $GN does not exist" -ForegroundColor Red
   }
}
Write-Host "Looking for empty class groups to remove." -ForegroundColor Green
$EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} 
foreach ($EmptyGroup in $EmptyGroups){
   ##Write to log file##
   $Loginfo = 'Removed empty group ' + $Class
   "$Loginfo" | Out-File $Logfile -append 
   $DN = $EmptyGroup.DistinguishedName 
   Write-Host "Removing Empty Group $DN" -ForegroundColor DarkGreen
   Remove-ADObject -Identity $DN -Confirm:$False
}

##Grab Class Teachers
##Import and modify SIMS Report
##Change Header
Write-Host "Importing class teacher data from $SIMSTeachList" -ForegroundColor Green
$NewSIMSTeachList = $Classfilepath + "ClassTeachersTemp.csv"
Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content $NewSIMSTeachList
##Reformat Names
Write-Host "Reformatting class teacher data." -ForegroundColor Green
$Names = import-csv $NewSIMSTeachList
$Names | foreach-object {
   $_.FullName = $_.FullName.replace("Mr ","")
   $_.FullName = $_.FullName.replace("Mrs ","")
   $_.FullName = $_.FullName.replace("Ms ","")
   $_.FullName = $_.FullName.replace("Miss ","")
   $_.FullName = $_.FullName.replace(" ","")
   $_.FullName = $_.FullName.replace("-","")
   $_.FullName = $_.FullName.replace("BSmith","WSmith")
   }
$TempSIMSTeachList = $Classfilepath + "ClassTeacherSAM.csv"
$Names | export-csv $TempSIMSTeachList -notype

$FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv"
##Reformat class/teacher list
Get-Content $TempSIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

##Create the classes!
Write-Host "Creating the classes!" -ForegroundColor Green
$ClassList = Import-Csv -Path $FinalSIMSTeachList
$FilteredCL = ($ClassList | Where {$_.FullName -ne "" })
foreach ($Item in $FilteredCL){
   $Class = $Item.Class
   $SAM = $Item.FullName
   $TeachEmail = $SAM + $StudMail
   if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) {
       "Creating class $Class with teacher $TeachEmail"
       ##Write to log file##
       #$Loginfo = 'Created ' + $Class + ' with ' + $TeachEmail
       #"$Loginfo" | Out-File $Logfile -append
       & "C:\GAM\gam.exe" create course alias $Class name $Class teacher $TeachEmail
       & "C:\GAM\gam.exe" course $Class add teacher $TeachEmail
       ##Get Class Students
       $Students = Get-ADGroupMember $Class
       foreach ($Student in $Students) {
           $UserInfo = Get-ADUser -Identity $Student -Properties *
           $StudName = $UserInfo.displayname
           $StudUsername = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           #"Adding Student $StudEmail to class $Class"
           & "C:\GAM\gam.exe" course $Class add student $StudEmail
           ##Write to log file##
           #$Loginfo = $Class + ',' + $StudEmail
           #"$Loginfo" | Out-File $Logfile -append
       }
   }
   else {
       Write-Host "Class $Class does not contain any students or Teacher does not exist" -ForegroundColor Red
   }
}

##Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "No changes today."
   }
   Send-MailMessage @emailOptions
}

} else {
   Write-Host "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" -ForegroundColor Red
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "$SIMSUPNReport does not exist or is smaller than $ReportMinSize"
   }
   Send-MailMessage @emailOptions
}

Shaun_Dark_Lord · March 15, 2018

Found an error;

In version 3, line 73;

$Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty Name

This should be;

$Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty samAccountName

Shaun_Dark_Lord · March 16, 2018

Am not going to list changes line by line, but here are the highlights;

Now checks for and creates missing folders prior to running.

Now checks that GAM is able to grab csv of existing classrooms before proceeding.

Now grabs csv of existing active/provisioned Google Classrooms.

Now grabs csv of participants of each active/provisioned Google Classroom.

GAM results were being displayed as NativeCommandErrors - These are redirected to $null for a cleaner script output - Not ideal, but less messy.

Google Classrooms are now only created if they do not already exist - This should speed up the runtime.

Students are only added to Google Classrooms if they are not already members.

##Set paths and preferences
$LogfilePath = ".\Logs\" ##Path where log files are created.
$Classfilepath = ".\ClassData\" ##Path where data file is created.
$GAMPath = "C:\GAM\gam.exe" ##Path where GAM is installed.
$GAMData = ".\GAMData\" ##Path where GAM data is recorded.
$GAMDataSize = "10KB" ##Minimum expected size for successfully exported GAM Data
$SIMSUPNReport = "\\SIMSServer\ReportShare$\UPNClasses.csv" ##Report containing list of current students
$SIMSTeachList = "\\SIMSServer\ReportShare$\ClassTeachers.csv"
$ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report
$SearchOU = "OU=Students,OU=Users,DC=MySchool,DC=sch,DC=uk" #OU Containing existing Students
$GroupOU = "OU=Classes,OU=Groups,DC=MySchool,DC=sch,DC=uk"
$StudMail = "@MySchool.com" ##Mail domain for student accounts
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.myschool.com" ##SMTP server for emailing log files.
$SMTPTo = "me " ##Recipient address for log files.
$SMTPFrom = "someone " ##Sender address for log files.
##/Set paths and preferences

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

##Clear old GAM data
Write-Host "Clearing old GAM data files...." -ForegroundColor Green
Get-ChildItem -Path $GAMData -Recurse -Force | Remove-Item -Force

##Export Current Google Classes
Write-Host "Grabbing list of existing Google Classrooms....." -ForegroundColor Green
$GAMCurrent = $GAMData + 'GAMCurrent.csv'
& $GAMPath print courses state provisioned state active fields id fields name fields courseState > $GAMCurrent 2> $null

## Check that GAM data exists
if ((Test-Path -Path $GAMCurrent) -And (Get-Item $GAMCurrent).Length -gt $GAMDataSize) {

##Grab current course participants
Write-Host "Grabbing current Google Classroom participants......." -ForegroundColor Green
$CourseList = Import-Csv -Path $GAMCurrent
foreach ($Item in $CourseList){
   $CourseName = $Item.name
   $CourseID = $Item.id
   $CourseCSV = $GAMData + $CourseName + '.csv'
   #"$CourseName - $CourseCSV"
   & $GAMPath print course-participants course $CourseID > $CourseCSV 2> $null
}

## Check that source data exists - Running without source data would be bad!
if ((Test-Path -Path $SIMSUPNReport) -And (Get-Item $SIMSUPNReport).Length -gt $ReportMinSize) {

$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfile = $Logfilepath + $Logfilename + ".txt"

##Clear old class data
Write-Host "Clearing old SIMS data files...." -ForegroundColor Green
Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force

##Import and modify SIMS Report
Write-Host "Importing SIMS data from $SIMSUPNReport" -ForegroundColor Blue
$NewSIMSUPNReport = $Classfilepath + "UPNClasses.csv"
##This gives me new lines after each $
Write-Host "Modifying SIMS data." -ForegroundColor Blue
Get-Content $SIMSUPNReport -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSUPNReport

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

Write-Host "Grabbing students from $SearchOU" -ForegroundColor Green
$FileList = Get-ADUser -SearchBase $SearchOU -Filter *
foreach ($User in $FileList){
   $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
   $FullName = $UserInfo.displayname
   $SAM = $UserInfo.samAccountName
   $UPN = $UserInfo.$UPNVariable

   if (!$UserInfo.$UPNVariable) {
       "UPN Missing from user $SAM"
   }
   ELSE {
       #"Looking for $UPN"
       $ClassList = Import-Csv -Path $NewSIMSUPNReport
       $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.$UPNVariable })
       foreach ($Item in $FilteredCL){
           $Class = $Item.Class
           if (!$Class) {"Group name blank"} else {
               $Classfile = $Classfilepath + $Class + ".csv"
               if((Test-Path -Path $Classfile )){ #Check to see if file has already been created
                   #"Class file already exists"
               } else {
                   $Classhead = 'UPN,ADUser'
                   "$Classhead" | Out-File $Classfile -append
               }

               if (dsquery group -name $Class) {
                   #Write-Host "Group $Class already exists" -ForegroundColor Blue
               } else {
                   ##Write to log file##
                   $Loginfo = 'Created group ' + $Class
                   "$Loginfo" | Out-File $Logfile -append 
                   $GroupEmail = $Class + $StudMail
                   Write-Host "Creating Group $Class with address $GroupEmail in path $GroupOU" -ForegroundColor Green
                   New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU 
               }
               ##Check if user is already group member
               $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty samAccountName
               If ($Members -contains $SAM) {
                   #"User $SAM already member of $Class"
                   #Add-ADGroupMember -Identity $Class -Members $SAM
                   $StudData = $UserInfo.$UPNVariable + ',' + $SAM
                   "$StudData" | Out-File $Classfile -append
               } else {
                   Write-Host "Adding $SAM to group $Class" -ForegroundColor Green
                   $Loginfo = 'Added student ' + $SAM + " to " + $Class
                   "$Loginfo" | Out-File $Logfile -append
                   Add-ADGroupMember -Identity $Class -Members $SAM
                   $StudData = $UserInfo.$UPNVariable + ',' + $SAM
                   "$StudData" | Out-File $Classfile -append
               }
           }
       }
   }
}

##Cleanup Groups
Write-Host "Removing old students from groups." -ForegroundColor Green
$GroupList = Get-ADGroup -filter * -searchbase $GroupOU
foreach ($Group in $GroupList){
   $GN = $Group.Name
   $DN = $EmptyGroup.DistinguishedName
   $Classfile = $Classfilepath + $GN + ".csv"
   if((Test-Path -Path $Classfile)){
       #"Group file for $GN exists"
       $GroupMembers = Get-ADGroupMember $GN
       foreach ($GroupMember in $GroupMembers) {
           $UserInfo = Get-ADUser -Identity $GroupMember -Properties *
           $SAM = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           $ClassList = Import-CSV -Path $Classfile
           if ($ClassList.ADUser -match $SAM ) {
               #"User $SAM found in class list for $GN"
           } else {
               Write-Host "User $SAM no longer member of $GN" -ForegroundColor DarkGreen
               ##Write to log file##
               $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN
               "$Loginfo" | Out-File $Logfile -append
               ##Remove User from Class Group
               Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false
               ##Remove User from Google Classroom
               & "C:\GAM\gam.exe" course $GN remove student $StudEmail
           }
       }
   } else {
       Write-Host "Group file for $GN does not exist" -ForegroundColor Red
   }
}
Write-Host "Looking for empty class groups to remove." -ForegroundColor Green
$EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} 
foreach ($EmptyGroup in $EmptyGroups){
   ##Write to log file##
   $Loginfo = 'Removed empty group ' + $Class
   "$Loginfo" | Out-File $Logfile -append 
   $DN = $EmptyGroup.DistinguishedName 
   Write-Host "Removing Empty Group $DN" -ForegroundColor DarkGreen
   Remove-ADObject -Identity $DN -Confirm:$False
}

##Grab Class Teachers
##Import and modify SIMS Report
##Change Header
Write-Host "Importing class teacher data from $SIMSTeachList" -ForegroundColor Green
$NewSIMSTeachList = $Classfilepath + "ClassTeachersTemp.csv"
Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content $NewSIMSTeachList
##Reformat Names
Write-Host "Reformatting class teacher data." -ForegroundColor Green
$Names = import-csv $NewSIMSTeachList
$Names | foreach-object {
   $_.FullName = $_.FullName.replace("Mr ","")
   $_.FullName = $_.FullName.replace("Mrs ","")
   $_.FullName = $_.FullName.replace("Ms ","")
   $_.FullName = $_.FullName.replace("Miss ","")
   $_.FullName = $_.FullName.replace(" ","")
   $_.FullName = $_.FullName.replace("-","")
   $_.FullName = $_.FullName.replace("BSmith","WSmith")
   }
$TempSIMSTeachList = $Classfilepath + "ClassTeacherSAM.csv"
$Names | export-csv $TempSIMSTeachList -notype

$FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv"
##Reformat class/teacher list
Get-Content $TempSIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

##Create the classes!
Write-Host "Creating the classes!" -ForegroundColor Green
$ClassList = Import-Csv -Path $FinalSIMSTeachList
$FilteredCL = ($ClassList | Where {$_.FullName -ne "" })
foreach ($Item in $FilteredCL){
   $Class = $Item.Class
   $SAM = $Item.FullName
   $TeachEmail = $SAM + $StudMail
   if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) {
       $CourseCSV = $GAMData + $Class + '.csv'
       if (Test-Path -Path $CourseCSV) {
           Write-Host "Google Classroom $Class already exists." -ForegroundColor Blue
           & $GAMPath course $Class add teacher $TeachEmail 2> $null
       } else {
           Write-Host "Creating Google Classroom $Class with teacher $TeachEmail" -ForegroundColor Green
           ##Write to log file##
           $Loginfo = 'Created Google Classroom ' + $Class + ' with ' + $TeachEmail
           "$Loginfo" | Out-File $Logfile -append
           & $GAMPath create course alias $Class name $Class teacher $TeachEmail 2> $null
           & $GAMPath course $Class add teacher $TeachEmail 2> $null
       }

       ##Get Class Students
       $Students = Get-ADGroupMember $Class
       foreach ($Student in $Students) {
           $UserInfo = Get-ADUser -Identity $Student -Properties *
           $StudName = $UserInfo.displayname
           $StudUsername = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           if (Test-Path -Path $CourseCSV) {
               ##Google classroom already exists - check if already a member.
               $Participants = Import-CSV -Path $CourseCSV
               if ($Participants."profile.emailAddress" -match $StudEmail ) {
                   Write-Host "$StudUsername already member of Google Classroom $Class" -ForegroundColor Blue
               } else {
                   Write-Host "Adding Student $StudEmail to Google Classroom $Class" -ForegroundColor Green
                   & $GAMPath course $Class add student $StudEmail 2> $null
                   ##Write to log file##
                   $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class
                   "$Loginfo" | Out-File $Logfile -append                    
               }
           } else {
               ##New Google classroom - add the students.
               Write-Host "Adding Student $StudEmail to Google Classroom $Class" -ForegroundColor Green
               & $GAMPath course $Class add student $StudEmail 2> $null
               ##Write to log file##
               $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class
               "$Loginfo" | Out-File $Logfile -append
           }
       }
   }
   else {
       Write-Host "Class $Class does not contain any students or Teacher does not exist" -ForegroundColor Red
   }
}

##Script completes successfully - Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "No changes today."
   }
   Send-MailMessage @emailOptions
}

##If SIMS data does not exist;
} else {
   Write-Host "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" -ForegroundColor Red
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "$SIMSUPNReport does not exist or is smaller than $ReportMinSize"
   }
   Send-MailMessage @emailOptions
}

##If GAM data does not exist;
} else {
   Write-Host "$GAMCurrent does not exist or is smaller than $GAMDataSize" -ForegroundColor Red
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "$GAMCurrent does not exist or is smaller than $GAMDataSize"
   }
   Send-MailMessage @emailOptions
}

mjk · March 16, 2018

Have you thought about using github ?

Edugeek is quite good, but version control isn't one of it's strong points.

Shaun_Dark_Lord · March 16, 2018

Have you thought about using github ?
Edugeek is quite good, but version control isn't one of it's strong points.

I'm not sure that anyone outside of Edugeek's target audience would be interested in this.

mjk · March 16, 2018

That wasn't really my point, it might be a useful tool for you and it would certainly make it easier for others to use, comment upon and modify your code.

You can always post version update here.

Shaun_Dark_Lord · August 30, 2018

Version 4.60 of GAM now allows for exporting of Classroom owner. This now allows the script to check the SIMS data against this info, set the new owner if different, and reset the Class status to "Provisioned" so that classes that are handed over to new teachers are not active until they are ready.

##Set paths and preferences
$LogfilePath = ".\Logs\" ##Path where log files are created.
$Classfilepath = ".\ClassData\" ##Path where data file is created.
$GAMPath = "C:\GAM\gam.exe" ##Path where GAM is installed.
$GAMData = ".\GAMData\" ##Path where GAM data is recorded.
$GAMDataSize = "10KB" ##Minimum expected size for successfully exported GAM Data
$SIMSUPNReport = "\\SIMSServer\ReportShare$\UPNClasses.csv" ##Report containing list of current students
$SIMSTeachList = "\\SIMSServer\ReportShare$\ClassTeachers.csv"
$ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report
$SearchOU = "OU=Students,OU=Users,DC=MySchool,DC=sch,DC=uk" #OU Containing existing Students
$GroupOU = "OU=Classes,OU=Groups,DC=ccw,DC=kent,DC=sch,DC=uk"
$StudMail = "@myschool.co.uk" ##Mail domain for student accounts
$UPNVariable = "POBox" ##AD option used to store SIMS UPN
$SMTPServer = "smtp.myschool.co.uk" ##SMTP server for emailing logs.
$SMTPTo = "Alerts " ##Recipient address for logs.
$SMTPFrom = "DC001 " ##Sender address for logs.
##/Set paths and preferences

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

##Revert Custom Class Names to match SIMS Data
$CustomClassList = Import-Csv -Path CourseCorrection.csv
$FilteredCustomCL = ($CustomClassList | Where {$_.FriendlyName -ne "" })
foreach ($Item in $FilteredCustomCL){
   $CustID = $Item.id
   $CustSIMSName = $Item.SIMSName
   $CustFriendlyName = $Item.FriendlyName
   Write-Host "Renaming $CustFriendlyName to $CustSIMSName" -ForegroundColor Green
   & $GAMPath update course $CustID name $CustSIMSName
}

##Clear old GAM data
Write-Host "Clearing old GAM data files...." -ForegroundColor Green
Get-ChildItem -Path $GAMData -Recurse -Force | Remove-Item -Force

##Export Current Google Classes
Write-Host "Grabbing list of existing Google Classrooms....." -ForegroundColor Green
$GAMCurrent = $GAMData + 'GAMCurrent.csv'
& $GAMPath print courses state provisioned state active state declined fields id fields name fields courseState owneremail > $GAMCurrent 2> $null

## Check that GAM data exists
if ((Test-Path -Path $GAMCurrent) -And (Get-Item $GAMCurrent).Length -gt $GAMDataSize) {

##Grab current course participants
Write-Host "Grabbing current Google Classroom participants......." -ForegroundColor Green
$CourseList = Import-Csv -Path $GAMCurrent
foreach ($Item in $CourseList){
   $CourseName = $Item.name
   $CourseName = $CourseName.replace("\","-")
   $CourseName = $CourseName.replace("/","-")
   $CourseID = $Item.id
   $CourseCSV = $GAMData + $CourseName + '.csv'
   $CourseOwner = $Item.ownerEmail
   & $GAMPath print course-participants course $CourseID > $CourseCSV 2> $null
}

## Check that source data exists - Running without source data would be bad!
if ((Test-Path -Path $SIMSUPNReport) -And (Get-Item $SIMSUPNReport).Length -gt $ReportMinSize) {

$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfile = $Logfilepath + $Logfilename + ".txt"

##Clear old class data
Write-Host "Clearing old SIMS data files...." -ForegroundColor Green
Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force

##Import and modify SIMS Report
Write-Host "Importing SIMS data from $SIMSUPNReport" -ForegroundColor Blue
$NewSIMSUPNReport = $Classfilepath + "UPNClasses.csv"
Write-Host "Modifying SIMS data." -ForegroundColor Blue
Get-Content $SIMSUPNReport -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSUPNReport

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

Write-Host "Grabbing students from $SearchOU"
$FileList = Get-ADUser -SearchBase $SearchOU -Filter *
foreach ($User in $FileList){
   $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
   $FullName = $UserInfo.displayname
   $SAM = $UserInfo.samAccountName
   $UPN = $UserInfo.$UPNVariable

   if (!$UserInfo.$UPNVariable) {
       "UPN Missing from user $SAM"
   }
   ELSE {
       $ClassList = Import-Csv -Path $NewSIMSUPNReport
       $FilteredCL = ($ClassList | Where {$_.UPN -eq $UserInfo.$UPNVariable })
       foreach ($Item in $FilteredCL){
           $Class = $Item.Class
           if (!$Class) {"Group name blank"} else {
               $Classfile = $Classfilepath + $Class + ".csv"
               if((Test-Path -Path $Classfile )){ #Check to see if file has already been created
                   #"Class file already exists"
               } else {
                   $Classhead = 'UPN,ADUser'
                   "$Classhead" | Out-File $Classfile -append
               }

               if (dsquery group -name $Class) {
                   #Write-Host "Group $Class already exists" -ForegroundColor Blue
               } else {
                   $Loginfo = 'Created group ' + $Class
                   "$Loginfo" | Out-File $Logfile -append 
                   $GroupEmail = $Class + $StudMail
                   Write-Host "Creating Group $Class with address $GroupEmail in path $GroupOU" -ForegroundColor Green
                   New-ADGroup -Name $Class -GroupCategory Distribution -GroupScope Global -DisplayName $Class -OtherAttributes @{'mail'=$GroupEmail} -Path $GroupOU 
               }
               ##Check if user is already group member
               $Members = Get-ADGroupMember -Identity $Class -Recursive | Select -ExpandProperty samAccountName
               If ($Members -contains $SAM) {
                   $StudData = $UserInfo.$UPNVariable + ',' + $SAM
                   "$StudData" | Out-File $Classfile -append
               } else {
                   Write-Host "Adding $SAM to group $Class" -ForegroundColor Green
                   $Loginfo = 'Added student ' + $SAM + " to " + $Class
                   "$Loginfo" | Out-File $Logfile -append
                   Add-ADGroupMember -Identity $Class -Members $SAM
                   $StudData = $UserInfo.$UPNVariable + ',' + $SAM
                   "$StudData" | Out-File $Classfile -append
               }
           }
       }
   }
}

##Cleanup Groups
Write-Host "Removing old students from groups." -ForegroundColor Green
$GroupList = Get-ADGroup -filter * -searchbase $GroupOU
foreach ($Group in $GroupList){
   $GN = $Group.Name
   $DN = $EmptyGroup.DistinguishedName
   $Classfile = $Classfilepath + $GN + ".csv"
   if((Test-Path -Path $Classfile)){
       #"Group file for $GN exists"
       $GroupMembers = Get-ADGroupMember $GN
       foreach ($GroupMember in $GroupMembers) {
           $UserInfo = Get-ADUser -Identity $GroupMember -Properties *
           $SAM = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           $ClassList = Import-CSV -Path $Classfile
           if ($ClassList.ADUser -match $SAM ) {
               #"User $SAM found in class list for $GN"
           } else {
               Write-Host "User $SAM no longer member of $GN"
               $Loginfo = 'Removed ' + $StudEmail + ' from ' + $GN
               "$Loginfo" | Out-File $Logfile -append
               Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false
               & "C:\GAM\gam.exe" course $GN remove student $StudEmail
           }
       }
   } else {
       Write-Host "Group file for $GN does not exist"
   }
}
$EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $GroupOU | where {!$_.members} | where {!$_.membersof} 
foreach ($EmptyGroup in $EmptyGroups){
   ##Write to log file##
   $Loginfo = 'Removed empty group ' + $Class
   "$Loginfo" | Out-File $Logfile -append 
   $DN = $EmptyGroup.DistinguishedName 
   Remove-ADObject -Identity $DN -Confirm:$False
}

##Grab Class Teachers
##Import and modify SIMS Report
##Change Header
$NewSIMSTeachList = $Classfilepath + "ClassTeachersTemp.csv"
Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "Full Name","FullName"} | Set-Content $NewSIMSTeachList
##Reformat Names
$Names = import-csv $NewSIMSTeachList
$Names | foreach-object {
   $_.FullName = $_.FullName.replace("Mr ","")
   $_.FullName = $_.FullName.replace("Mrs ","")
   $_.FullName = $_.FullName.replace("Ms ","")
   $_.FullName = $_.FullName.replace("Miss ","")
   $_.FullName = $_.FullName.replace(" ","")
   $_.FullName = $_.FullName.replace("-","")
   }
$TempSIMSTeachList = $Classfilepath + "ClassTeacherSAM.csv"
$Names | export-csv $TempSIMSTeachList -notype

$FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv"
##Reformat class/teacher list
Get-Content $TempSIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList

Import-Module ActiveDirectory
$dnsroot = '@' + (Get-ADDomain).dnsroot

##Create the classes!
$ClassList = Import-Csv -Path $FinalSIMSTeachList
$FilteredCL = ($ClassList | Where {$_.FullName -ne "" })
foreach ($Item in $FilteredCL){
   $Class = $Item.Class
   $SAM = $Item.FullName
   $TeachEmail = $SAM + $StudMail
   if ((dsquery group -samid $Class) -And (dsquery user -samid $SAM)) {
       $CourseCSV = $GAMData + $Class + '.csv'
       if (Test-Path -Path $CourseCSV) {
           #Write-Host "Google Classroom $Class already exists."
           $CourseList = Import-Csv -Path $GAMCurrent
           $FilteredCourseList = ($CourseList | Where {$_.name -eq $Class })
           foreach ($FilteredItem in $FilteredCourseList){
               $CurrentOwner = $FilteredItem.ownerEmail
               $CourseName = $FilteredItem.name
               $CourseName = $CourseName.replace("\","-")
               $CourseName = $CourseName.replace("/","-")
           }
           if (($CurrentOwner -eq $TeachEmail)) {
               #"Owner already set"
           } else {
               $Loginfo = 'Google Classroom ' + $Class + ' already exists - changing owner to ' + $TeachEmail + ' and reset course to provisioned.'
               "$Loginfo" | Out-File $Logfile -append
               & $GAMPath course $Class add teacher $TeachEmail 2> $null
               & $GAMPath update course $Class owner $TeachEmail status PROVISIONED 2> $null
           }
       } else {
           Write-Host "Creating Google Classroom $Class with teacher $TeachEmail"
           $Loginfo = 'Created Google Classroom ' + $Class + ' with ' + $TeachEmail
           "$Loginfo" | Out-File $Logfile -append
           & $GAMPath create course alias $Class name $Class teacher $TeachEmail 2> $null
           & $GAMPath course $Class add teacher $TeachEmail 2> $null
       }

       ##Get Class Students
       $Students = Get-ADGroupMember $Class
       foreach ($Student in $Students) {
           $UserInfo = Get-ADUser -Identity $Student -Properties *
           $StudName = $UserInfo.displayname
           $StudUsername = $UserInfo.samAccountName
           $StudEmail = $UserInfo.EmailAddress
           if (Test-Path -Path $CourseCSV) {
               ##Google classroom already exists - check if already a member.
               $Participants = Import-CSV -Path $CourseCSV
               if ($Participants."profile.emailAddress" -match $StudEmail ) {
                   #Write-Host "$StudUsername already member of $Class"
               } else {
                   Write-Host "Adding Student $StudEmail to Google Classroom $Class"
                   & $GAMPath course $Class add student $StudEmail 2> $null
                   $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class
                   "$Loginfo" | Out-File $Logfile -append                    
               }
           } else {
               ##New Google classroom - add the students.
               Write-Host "Adding Student $StudEmail to Google Classroom $Class"
               & $GAMPath course $Class add student $StudEmail 2> $null
               $Loginfo = 'Added ' + $StudEmail + ' to Google Classroom ' + $Class
               "$Loginfo" | Out-File $Logfile -append
           }
       }
   }
   else {
       Write-Host "Class $Class does not contain any students or Teacher does not exist"
   }
}

##Script completes successfully - Email log file##
if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "See Attached"
       'Attachments' = $Logfile
   }
   Send-MailMessage @emailOptions
} else {
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "No changes today."
   }
   Send-MailMessage @emailOptions
}

##If SIMS data does not exist;
} else {
   Write-Host "$SIMSUPNReport does not exist or is smaller than $ReportMinSize" -ForegroundColor Red
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "$SIMSUPNReport does not exist or is smaller than $ReportMinSize"
   }
   Send-MailMessage @emailOptions
}

##If GAM data does not exist;
} else {
   Write-Host "$GAMCurrent does not exist or is smaller than $GAMDataSize"
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "$GAMCurrent does not exist or is smaller than $GAMDataSize"
   }
   Send-MailMessage @emailOptions
}

Shaun_Dark_Lord · May 2, 2019

I'll soon be starting on a new version of this script, as we've joined a MAT's Google domain, and the old script would likely cause havoc with the other school's classes.

While I'm working on this, are there any features/changes you would like to see? I'll be using GAMADV with the new script, so there are plenty of new options available.

Shaun_Dark_Lord · May 24, 2019

Requirements;

A tested and operational GAMADV (4.83.04+) installation.
An agreed class alias prefix for your school.
Your teacher’s email addresses to be stored in SIMS and set as their “Work Email”. It is technically possible to use AD for this, but the amount of customisation required versus entering 100 email addresses into SIMS is difficult to justify.
Either your student’s email address stored in SIMS and set as their “Primary Email”, or their email address stored in AD, along with their SIMS Admission Number.
A clean(ish) Google Classroom environment. The ideal would be to run this at the beginning of a new academic year, after all your old classes have been archived. You could however, write a script to set the Aliases of your existing classes to their expected values and start running this script against already created classes.

Getting the data from SIMS;

I like to use SIMS built in CommandReporter tool to schedule data exports. Strangely, the CSVs it exports are a lot easier to read than those generated from within SIMS.net.

In our environment, we have setup a SIMS user account with the minimum permissions required to run our reports. We then schedule a task to run on the SIMS server itself to execute the following command;

Program/Script: “C:\Program Files (x86)\SIMS\SIMS .net\CommandReporter.exe”
Arguments: /USER:useraccount /PASSWORD:password /SERVERNAME:simsserver\sqlinstance /DATABASENAME:sims /REPORT:Reportname /OUTPUT:"C:\GoogleClasses\reportname.csv"

Two reports are required for this script;

The first SIMS report we use is focused on “Class” and pulls SIMS Class names and associated Staff Email addresses that we use to determine the Google classroom names and owners.

To remove some invalid entries, the report is then filtered;

The second SIMS report we use is focused on “Student” and pulls Admission number, Primary Email and associated Class names.

As long as you save the SIMS report with the same name referenced in the scheduled task above, running the task should output a CSV. You can either run a separate task for each report, or add two actions to a single task. As long as you have two csv files ready to go before you run the script, then you’re good to go!

What The Script Does;

Line 2 determines whether we will be pulling Student email addresses directly from SIMS reports or matching them against AD.
Lines 3-8 set our AD variables. If you’re purley running against SIMS data, these do not need to be set. The last two options here determine whether you would like an AD group created for each class. We find this useful for print management and file screens/quotas. AD group creation is only currently possible if AD is your source for Student email addresses.
Lines 9-13 specify the location of our two exported SIMS reports, the minimum expected size of a valid student/class report and the report field containing student email addresses (if that’s what you chose on line 2).
Lines 14-19 set where we’ll be storing our logs and class data (relative to the powershell script), as well as a minimum expected size for a list of existing Google Classrooms (If you’re testing this script and don’t have any Google Classrooms yet, set this to “0KB”)
Lines 20 - 25 set your school prefix (used in the Class Alias) and your email settings for reporting.
Lines 28 - 67 determine the script execution time and setup counters for reporting. The current academic year is determined for use in the Class Alias and End of Year process. File paths are checked and created.
Lines 69 - 79 clears previously used Google Class data, downloads the current list of Google Classes and checks that the list contains data.
Lines 81-116 perform the end of year process. This checks whether it is currently August, and if so, all classes with an Alias matching the current academic year are set to archived. If you chose to enable AD group creation on line 7, these groups are also removed.
Lines 121-137 check for the existence of the SIMS student/class report and then import and modify the SIMS reports. Characters in class names that would later cause the script to fail are replaced.
Lines 139-237 are run if AD is the source of Student email addresses;
Lines 144-150 search the specified AD OU for users with an Admission number and email address.
Lines 153-167 imports the SIMS student/class report and matches the data against AD, creating a CSV file for each class containing student email addresses from AD.
Lines 169-193 add the matching AD students to an AD group (if set on line 7).
Lines 195-237 remove invalid members from the AD groups, and removes any empty groups (if set on line 7). This process was previously used to also determine whether Google Classes could be archived mid-year, but this has not been implemented here.
Lines 239-264 are run if SIMS is the source of Student email addresses. The SIMS student/class report is filtered and split, creating a CSV file for each class containing student email addresses.
Lines 266-270 imports and filters the Teacher/Class SIMS report to ensure each Class has a corresponding teacher email address (I know the SIMS report is supposed to do this during creation, but it doesn’t always).
Lines 271-278 determine the Class Alias for each class in the SIMS report, using the School prefix, academic year and SIMS cass name. The list of existing Google Classes we generated in lines 69-79 is then imported and filtered to only include classes matching our school’s prefix.
Line 279 checks that a CSV file exists for each class containing student email addresses. If not, then the class is not created/updated.
Lines 281-298 are run if the Class already exists in Google Classroom. The existing owner is checked, and if it does not match, a new Class owner is assigned and the class status is reset to “PROVISIONED”.
Lines 300-306 are run if the Class does not already exist. The Class is created with the teacher specified in the SIMS report.
Lines 309-314 Sync the student membership of each valid Google Class with the Student Email CSV files we generated in lines 153-167 or 239-264.
Lines 316-329 are executed if a class exists in SIMS, has a valid teacher email address, but does not have any matching students. No new classroom is created, and if the Class already existed, it is archived.
Lines 331-345 determine the total changes made, and the time taken to execute the script.
Lines 347-417 email summaries and log files.

Shaun_Dark_Lord · May 24, 2019

The Powershell Script;

##Set preferences
$StudMailSource = "AD" ##Valid options are "AD" or "SIMS". If AD, then $MailVariable and $AdNoVariable are mandatory.  If SIMS, then $SIMSClassCSVMail is mandatory.
#AD Settings (Used if you're using  AD to store/verify your Staff/Student addresses)
   $MailVariable = "gmail" ##AD option used to store email address.
   $AdNoVariable = "AdNo" ##AD option used to store SIMS Admission Number.
   $StudOU = "OU=Students,OU=Users,DC=myschool,DC=kent,DC=sch,DC=uk" #OU Containing existing Students
   $GroupCreate = "Yes" ##Valid options are "Yes" or "No". If Yes, an AD group will be created in $ClassGroupOU for each class.
   $ClassGroupOU = "OU=Classes,OU=Groups,DC=myschool,DC=kent,DC=sch,DC=uk"
#SIMS Settings
   $SIMSStudList = "\\simsserver\GoogleClasses$\SIMSStudList.csv" ##Report containing list of current Classes and a corresponding Student identifier (Either AdNo or Email address)
   $SIMSTeachList = "\\simsserver\GoogleClasses$\SIMSTeachList.csv" ##Report containing a list of current Classes and their teacher's email address (Work Email)
   $ReportMinSize = "200KB" ##Minimum expected size for successfully exported SIMS report ($SIMSStudList)
   $SIMSClassCSVMail = "Primary Email" ##SIMS report field containing Student Email addresses
#File Paths and Misc Settings
   $LogfilePath = ".\Logs\" ##Path where log files are created.
   $Classfilepath = ".\ClassData\" ##Path where data file is created.
   $GAMPath = "C:\GAM\GAMADV\gam.exe" ##Path where GAMADV is installed.
   $GAMData = ".\GAMData\" ##Path where GAM data is recorded.
   $GAMDataSize = "10KB" ##Minimum expected size for successfully exported GAM Data
#School and Email Settings
   $MailDomain = "@mydomain.com" ##Mail domain used for group creation.
   $SchoolPrefix = "TWS" ##Prefix to add to Class Alias to prevent MAT conflicts.
   $SMTPServer = "aspmx.l.google.com" ##SMTP server for emailing log files.
   $SMTPTo = "Alerts " ##Recipient address for log files.
   $SMTPFrom = "DC001 " ##Sender address for log files.
##/Set preferences

if (($StudMailSource -eq "AD")) {
   Import-Module ActiveDirectory
   $dnsroot = '@' + (Get-ADDomain).dnsroot
}

##Determine Start Time
$StartTime = Get-Date

##Setup Counters
$ClassCreated = 0
$ClassModified = 0
$ClassRemoved = 0
$InvalidClass = 0

##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}
#"We are in Academic Year $AcaYear"

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

##Set Logfile Name
$Logfilename = Get-Date -UFormat "%Y%m%d"
$Logfile = $Logfilepath + $Logfilename + ".txt"

##Clear old GAM data
Write-Host "Clearing old GAM data files...." -ForegroundColor Green
Get-ChildItem -Path $GAMData -Recurse -Force | Remove-Item -Force

##Export Current Google Classes
Write-Host "Grabbing list of existing Google Classrooms....." -ForegroundColor Green
$GAMCurrent = $GAMData + 'GAMCurrent.csv'
& $GAMPath print courses state provisioned state active state declined alias fields id fields name fields courseState owneremail > $GAMCurrent 2> $null

## Check that GAM data exists
if ((Test-Path -Path $GAMCurrent) -And (Get-Item $GAMCurrent).Length -gt $GAMDataSize) {

   ##End of Year
   if ($month -eq 8) {
       "Doing the End of Year Process!"
       $ClassPrefix = $SchoolPrefix + $AcaYear + "_*"
       $ClassList = Import-Csv -Path $GAMCurrent | Where {$_.Aliases -like $ClassPrefix}
       $ClassRemoved = @($ClassList).count
       Write-Host "$ClassCount classes found in $AcaYear to be deleted." -ForegroundColor Green
       foreach ($Class in $ClassList){       
           $Alias = $Class.Aliases
           $Loginfo = 'Removed class ' + $Alias
           "$Loginfo" | Out-File $Logfile -append
           & $GAMPath update course $Alias status ARCHIVED 2> $null
           if(($GroupCreate -eq "Yes")) {
               $GroupCN = "CN=" + "$Alias" + "," + $ClassGroupOU
               Write-Host "Removing Old Group $Alias" -ForegroundColor DarkGreen
               Remove-ADObject -Identity $GroupCN -Confirm:$False
           }
       }
       $TotalTime = $("{0:hh\:mm\:ss}" -f (New-TimeSpan -Start $StartTime -End $(Get-Date)))
       ##Script completes successfully - Email log file##
       if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
               $EmailOptions = @{
                   'SMTPServer' = $SMTPServer
                   'To' = $SMTPTo
                   'From' = $SMTPFrom
                   'Subject' = "Results from Google Classes Script End of Year Process"
                   'Body' = "Script completed in $TotalTime
   Classes Removed:  $ClassRemoved"
                   'Attachments' = $Logfile
               }
               Send-MailMessage @emailOptions
       } else {
           Write-Host "End of Year process made no changes." -ForegroundColor Green
       }

       break #Terminate the script
   } else {
       "Running normally, as it's not August."
   }

   ## Check that source data exists - Running without source data would be bad!
   if ((Test-Path -Path $SIMSStudList) -And (Get-Item $SIMSStudList).Length -gt $ReportMinSize) {

       ##Clear old class data
       Write-Host "Clearing old SIMS data files...." -ForegroundColor Green
       Get-ChildItem -Path $Classfilepath -Recurse -Force | Remove-Item -Force

       ##Reformat class/teacher/email list
       $FinalSIMSTeachList = $Classfilepath + "ClassTeachers.csv"
       Get-Content $SIMSTeachList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $FinalSIMSTeachList

       ##Reformat class/student/AdNo list
       Write-Host "Importing SIMS data from $SIMSStudList" -ForegroundColor Blue
       $NewSIMSStudList = $Classfilepath + "StudClasses.csv"
       ##This gives me new lines after each $
       Write-Host "Modifying SIMS data." -ForegroundColor Blue
       Get-Content $SIMSStudList -Raw | Foreach {$_ -replace "`r`n ,",","} | Foreach {$_ -replace "/","-"} | Foreach {$_ -replace '"',''} | Foreach {$_ -replace ':',''} | Foreach {$_ -replace ' ','_'} | Set-Content $NewSIMSStudList
      
       if(($StudMailSource -eq "AD")) {
           Write-Host "AD set as authoritative source for student email addresses." -ForegroundColor Green
           $Loginfo = 'AD set as authoritative source for student email addresses.'
           "$Loginfo" | Out-File $Logfile -append
           Write-Host "Grabbing students from $StudOU" -ForegroundColor Green
           $ADStudList = Get-ADUser -SearchBase $StudOU -Filter * -Properties * | where {($_.$MailVariable -ne $null) -and ($_.$AdNoVariable -ne $null)}
           foreach ($User in $ADStudList){
               $UserInfo = Get-ADuser -Identity $User -Properties * # Grab all user properties
               $FullName = $UserInfo.displayname
               $SAM = $UserInfo.samAccountName
               $AdNo = $UserInfo.$AdNoVariable
               $StudEmail = $UserInfo.$MailVariable

               #"Looking for $AdNo"
               $ClassList = Import-Csv -Path $NewSIMSStudList
               $FilteredCL = ($ClassList | Where {$_.Adno -eq $UserInfo.$AdNoVariable })
               foreach ($Item in $FilteredCL){
                   $Class = $Item.Class
                   $ClassAlias = $SchoolPrefix + $AcaYear + "_" + $Class
                   if (!$Class) {"Group name blank"} else {
                       $Classfile = $Classfilepath + $ClassAlias + ".csv"
                       if((Test-Path -Path $Classfile )){ #Check to see if file has already been created
                           #"Class file already exists"
                       } else {
                           $Classhead = 'SAM,Email'
                           "$Classhead" | Out-File $Classfile -append #Create Blank class file
                       }
                       $StudData = $SAM + ',' + $StudEmail
                       "$StudData" | Out-File $Classfile -append #Add student's email address to class file.

                       if(($GroupCreate -eq "Yes")) {
                           if (dsquery group -name $ClassAlias) {
                               #Write-Host "Group $Class already exists" -ForegroundColor Blue
                           } else {
                               ##Write to log file##
                               $Loginfo = 'Created group ' + $ClassAlias
                               "$Loginfo" | Out-File $Logfile -append 
                               $GroupEmail = $ClassAlias + $MailDomain
                               Write-Host "Creating Group $ClassAlias with address $GroupEmail in path $ClassGroupOU" -ForegroundColor Green
                               New-ADGroup -Name $ClassAlias -GroupCategory Distribution -GroupScope Global -DisplayName $ClassAlias -OtherAttributes @{'mail'=$GroupEmail} -Path $ClassGroupOU 
                           }
                           ##Check if user is already group member
                           $Members = Get-ADGroupMember -Identity $ClassAlias -Recursive | Select -ExpandProperty samAccountName
                           If ($Members -contains $SAM) {
                               #"User $SAM already member of $Class"
                           } else {
                               Write-Host "Adding $SAM to group $ClassAlias" -ForegroundColor Green
                               $Loginfo = 'Added student ' + $SAM + " to " + $ClassAlias
                               "$Loginfo" | Out-File $Logfile -append
                               Add-ADGroupMember -Identity $ClassAlias -Members $SAM
                           }
                       }
                   }
               }
           }

           ##Cleanup Groups
           if(($GroupCreate -eq "Yes")) {
               Write-Host "Removing old students from groups." -ForegroundColor Green
               $GroupList = Get-ADGroup -filter * -searchbase $ClassGroupOU
               foreach ($Group in $GroupList){
                   $GN = $Group.Name
                   #$DN = $EmptyGroup.DistinguishedName
                   $Classfile = $Classfilepath + $GN + ".csv"
                   if((Test-Path -Path $Classfile)){
                       #"Group file for $GN exists"
                       $GroupMembers = Get-ADGroupMember $GN
                       foreach ($GroupMember in $GroupMembers) {
                           $UserInfo = Get-ADUser -Identity $GroupMember -Properties *
                           $SAM = $UserInfo.samAccountName
                           #$StudEmail = $UserInfo.EmailAddress
                           $ClassList = Import-CSV -Path $Classfile
                           if ($ClassList.SAM -match $SAM ) {
                               #"User $SAM found in class list for $GN"
                           } else {
                               Write-Host "User $SAM no longer member of $GN" -ForegroundColor DarkGreen
                               ##Write to log file##
                               ##Remove User from Class Group
                               Remove-ADGroupMember -Identity $GN -Member $SAM -Confirm:$false
                           }
                       }
                   } else {
                       Write-Host "Group file for $GN does not exist" -ForegroundColor Red
                   }
               }

               Write-Host "Looking for empty class groups to remove." -ForegroundColor Green
               $EmptyGroups = Get-ADGroup -filter * -Properties members,memberof -searchbase $ClassGroupOU | where {!$_.members} | where {!$_.membersof} 
               foreach ($EmptyGroup in $EmptyGroups){
                   ##Write to log file##
                   $Loginfo = 'Removed empty group ' + $EmptyGroup
                   "$Loginfo" | Out-File $Logfile -append 
                   $DN = $EmptyGroup.DistinguishedName
                   $GroupClassName = $EmptyGroup.Name
                   Write-Host "Removing Empty Group $GroupClassName" -ForegroundColor DarkGreen
                   Remove-ADObject -Identity $DN -Confirm:$False
               }

           }
       } else {
           Write-Host "SIMS set as authoritative source for student email addresses." -ForegroundColor Green
           $Loginfo = 'SIMS set as authoritative source for student email addresses.'
           "$Loginfo" | Out-File $Logfile -append
           $ClassList = Import-Csv -Path $NewSIMSStudList
           $SIMSClassCSVMail = $SIMSClassCSVMail -replace "`r`n ,","," -replace "/","-" -replace '"','' -replace ':','' -replace ' ','_' #During the CSV reformat, some characters are replaced.  This line ensures that the correct CSV header is being used.
           $FilteredCL = ($ClassList | Where {($_.Class -ne $null) -and ($_.$SIMSClassCSVMail -ne $null)})
           $FilteredCount = @($FilteredCL).count
           Write-Host "$FilteredCount student/class records found" -ForegroundColor Green
           foreach ($Item in $FilteredCL){
               $Class = $Item.Class
               $ClassAlias = $SchoolPrefix + $AcaYear + "_" + $Class
               $StudEmail = $Item.$SIMSClassCSVMail
               if (!$Class) {"Group name blank"} else {
                   $Classfile = $Classfilepath + $ClassAlias + ".csv"
                   if((Test-Path -Path $Classfile )){ #Check to see if file has already been created
                       #"Class file already exists"
                   } else {
                       $Classhead = 'Email'
                       "$Classhead" | Out-File $Classfile -append #Create Blank class file
                   }

                   "$StudEmail" | Out-File $Classfile -append #Add student's email address to class file.
               }
           }

       }

To be continued.....

Shaun_Dark_Lord · May 24, 2019

        Write-Host "Creating the classes!" -ForegroundColor Green
       $ClassList = Import-Csv -Path $FinalSIMSTeachList
       $FilteredCL = ($ClassList | Where {$_.Work_Email -ne "" })
       $SIMSClassCount = @($FilteredCL).count # Count Number of SIMS Classes.
       Write-Host "$SIMSClassCount classes found in SIMS." -ForegroundColor Green
       foreach ($Item in $FilteredCL){
           $Class = $Item.Class
           $ClassAlias = $SchoolPrefix + $AcaYear + "_" + $Class
           $TeachEmail = $Item.Work_Email
           $CourseCSV = $Classfilepath + $ClassAlias + '.csv'
           $ClassPrefix = $SchoolPrefix + $AcaYear + "_*"
           $ExistingCourseList = Import-Csv -Path $GAMCurrent | Where {$_.Aliases -like $ClassPrefix}
           $StartCourses = $ExistingCourseList.count # Count Number of Classes before we do anything.
           if (Test-Path -Path $CourseCSV) {
               #Write-Host "Student list exists for $ClassAlias" -ForegroundColor Blue
               $FilteredExistingCourseList = ($ExistingCourseList | Where {$_.Aliases -eq $ClassAlias })
               if ($FilteredExistingCourseList) {
                   #$CurrentOwner = $_.ownerEmail
                   foreach ($Item in $FilteredExistingCourseList){
                       $CurrentOwner = $Item.ownerEmail
                       #$CourseName = $Item.Aliases
                   }
                   if (($CurrentOwner -eq $TeachEmail)) {
                       #Write-Host "Owner for class $ClassAlias already set" -ForegroundColor Blue
                   } else {
                       #Write-Host "Google Classroom $ClassAlias already exists - changing owner to $TeachEmail and reset course to provisioned." -ForegroundColor Green
                       $Loginfo = 'Google Classroom ' + $ClassAlias + ' already exists - changing owner to ' + $TeachEmail + ' and reset course to provisioned.'
                       "$Loginfo" | Out-File $Logfile -append
                       $ClassModified = $ClassModified + 1
                       & $GAMPath update course $ClassAlias status PROVISIONED 2> $null
                       & $GAMPath course $ClassAlias add teacher $TeachEmail 2> $null
                       & $GAMPath update course $ClassAlias owner $TeachEmail 2> $null
                   }
               } else {
                   #Write-Host "Creating Google Classroom $ClassAlias with teacher $TeachEmail" -ForegroundColor Green
                   ##Write to log file##
                   $Loginfo = 'Created Google Classroom ' + $ClassAlias + ' with ' + $TeachEmail
                   "$Loginfo" | Out-File $Logfile -append
                   $ClassCreated = $ClassCreated + 1
                   & $GAMPath create course alias $ClassAlias name $Class teacher $TeachEmail 2> $null
                   & $GAMPath course $ClassAlias add teacher $TeachEmail 2> $null
               }

               ##Sync class students with Class CSV
               #Write-Host "Syncing Google Classroom $ClassAlias students with file $CourseCSV" -ForegroundColor Green
               $RawCourseCSV = $Classfilepath + "Raw_" + $ClassAlias + ".csv"
               Get-Content $CourseCSV -Raw | Set-Content $RawCourseCSV #Reencode course CSV to RAw so that it can be read by GAM.
               $CSVFile = $RawCourseCSV + ":Email" #Set the source CSV file and data field for the sync.
               & $GAMPath course "$ClassAlias" sync students csvfile $CSVFile 2> $null
           } else {
               Write-Host "Class $ClassAlias does not contain any students." -ForegroundColor Red
               $FilteredExistingCourseList = ($ExistingCourseList | Where {$_.Aliases -eq $ClassAlias })
               if ($FilteredExistingCourseList) {                
                   & $GAMPath update course $ClassAlias status ARCHIVED 2> $null
                   $Loginfo = 'Class ' + $ClassAlias + ' - No students found. Class Archived.'
                   "$Loginfo" | Out-File $Logfile -append

               } else {
                   $Loginfo = 'Error creating class ' + $ClassAlias + ' - No students found.'
                   "$Loginfo" | Out-File $Logfile -append
               }
               $InvalidClass = $InvalidClass + 1
           }
       }

       $ScriptChanges = [Math]::Abs($ClassCreated - $ClassRemoved) #Redundant, as we no longer remove empty classes mid-year.  Left in case I ever figure out how to do this efficiently.
       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.
       $GAMChanges = [Math]::Abs($EndCourses - $StartCourses)
       $Errors = [Math]::Abs($ScriptChanges - $GAMChanges)

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

       Write-Host "Script completed in $TotalTime" -ForegroundColor Green
       Write-Host "Expected changes: $ScriptChanges" -ForegroundColor Green

       ##Script completes successfully - Email log file##
       if((Test-Path -Path $Logfile)){ #Test to see if anything has been logged
           if ($ScriptChanges -eq $GAMChanges) {
               $EmailOptions = @{
                   'SMTPServer' = $SMTPServer
                   'To' = $SMTPTo
                   'From' = $SMTPFrom
                   'Subject' = "Results from Google Classes Script"
                   'Body' = "Script completed in $TotalTime
                   Classes Created:  $ClassCreated
                   Classes Modified: $ClassModified
                   Classes Removed:  $ClassRemoved
                   Invalid Classes:  $InvalidClass"
                   'Attachments' = $Logfile
               }
               Send-MailMessage @emailOptions
               Write-Host "Actual changes: $GAMChanges" -ForegroundColor Green
           } else {
               $EmailOptions = @{
                   'SMTPServer' = $SMTPServer
                   'To' = $SMTPTo
                   'From' = $SMTPFrom
                   'Subject' = "Results from Google Classes Script"
                   'Body' = "Script completed with errors in $TotalTime
                   Classes Created:  $ClassCreated
                   Classes Modified: $ClassModified
                   Classes Removed:  $ClassRemoved
                   Invalid Classes:  $InvalidClass
                   Errors:           $Errors"
                   'Attachments' = $Logfile
               }
               Send-MailMessage @emailOptions
               Write-Host "Actual changes: $GAMChanges" -ForegroundColor Red
           }
       } else {
           $EmailOptions = @{
               'SMTPServer' = $SMTPServer
               'To' = $SMTPTo
               'From' = $SMTPFrom
               'Subject' = "Results from Google Classes Script"
               'Body' = "No changes today.
               Script completed in $TotalTime minutes."
           }
           Send-MailMessage @emailOptions
       }

   ##If SIMS data does not exist;
   } else {
       Write-Host "$SIMSStudList does not exist or is smaller than $ReportMinSize" -ForegroundColor Red
       $EmailOptions = @{
           'SMTPServer' = $SMTPServer
           'To' = $SMTPTo
           'From' = $SMTPFrom
           'Subject' = "Results from Google Classes Script"
           'Body' = "$SIMSStudList does not exist or is smaller than $ReportMinSize"
       }
       Send-MailMessage @emailOptions
   }

##If GAM data does not exist;
} else {
   Write-Host "$GAMCurrent does not exist or is smaller than $GAMDataSize" -ForegroundColor Red
   $EmailOptions = @{
       'SMTPServer' = $SMTPServer
       'To' = $SMTPTo
       'From' = $SMTPFrom
       'Subject' = "Results from Google Classes Script"
       'Body' = "$GAMCurrent does not exist or is smaller than $GAMDataSize"
   }
   Send-MailMessage @emailOptions
}

Shaun_Dark_Lord · June 17, 2019

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

SIMS report -> Google Classroom

Recommended Posts

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

mjk

Shaun_Dark_Lord

mjk

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

Shaun_Dark_Lord

Create an account or sign in to comment

Create an account

Sign in

Recent Threads

Confirm acknowledgement of parental consent requirement for the Additional Google services

Lenovo Tablet Firmware

Outloook 365 - Classic

Home

Forums

What's New