This script takes a mass export of students and creates accounts in both Google (via GAM) and Active Directory (via powershell).

    The same username and password for each user is the same in all locations.

    Originally designed for use with Skyward with Apple's School Manager export structure.

    ==Critical Notes==

    1. AD samAccountName has a unchangeable max of 20 characters.

    2. Processing is dependant on CSV headers matching up. Obviously.

    3. Graduation Year is calculated by comparison current date. Ex) A 5th grader in January graduates a different year than a 5th grader in November.


    1. Start a transcript for reviewing errors.

    2. Check Google & AD Org Units. Create if missing.

    3. Split the mass CSV of students into separate grades. This makes GAM processing faster than doing one student at a time and

        makes it easier to dump them all in the same OU.

    4. Each new CSV is imported to Google via GAM. Creates new users, updates passwords for existing users, and makes sure they're in specified student group.

    5. Active Directory user and home directory are created. Existing users have their passwords updated.

    Author: Andrew Schott

    Company: Joletec, Inc.

    Last Modifed: 2018-02-20


$transcriptPath = 'C:\StudentAccounts.log'

[int]$year = (Get-Date -Format "yyyy")

$studentsFile = 'C:\Users\administrator.BSCS\Desktop\Google School Directory Sync\students.csv'

$gradeFiles = 'C:\Users\administrator.BSCS\Desktop\Google School Directory Sync\grade_level'

$AD = 'BSCS'

$emailDomain = '@bscs.k12.mi.us'

$ADou = 'OU=Students,OU=Users,OU=BSCS,DC=bscs,DC=local'


$FileServer = 'FS-01.bscs.local'

Start-Transcript -path $transcriptPath -append

#Google OU check. Create if missing

$i = $year

While ($i -ne ($year+15)){

    Write-Host "Check Google OU $i"

    if(-Not(GAM info org "Students/$i")){

        #Create missing OU

        GAM create org "$i" parent "/Students"

        Write-Host "Created Google OU $i"




#AD OU check. Create if missing

$i = $year

While ($i -ne ($year+15)){

    Write-Host "Check AD OU $i"


        #Create missing OU

        NEW-ADOrganizationalUnit $i –path $ADou

        Write-Host "Created AD OU $i"




#Split student export into individual files sorted by grade

$data = Import-CSV -Delimiter "," -Path $studentsFile -Header school_id,student_id,first_name,last_name,grade_level,user_name,password  

foreach ($group in $data | Group grade_level){        

    if ($group.name -ne 'grade_level'){

        $data | Where-Object {$_.grade_level -eq $group.name} |

            ConvertTo-Csv -NoTypeInformation |

            foreach {$_.Replace('"','')} |

            Out-File "$gradeFiles\$($group.name).csv"



Write-Host "Separated grade levels"

#Import each grade into Google

$Grades = Get-ChildItem -Path $gradeFiles

foreach($file in $Grades){

Import-Csv -Delim ',' $file.FullName |

    # Modify grade_level to grad_year

    ForEach-Object {$_.grade_level = "Students/$(IF((Get-Date) -ge (Get-Date -Month 09 -Day 01)){"{0:yyyy}" -f (Get-Date).AddYears(13 - [int]$_.grade_level)}else{"{0:yyyy}" -f (Get-Date).AddYears(12 - [int]$_.grade_level)})";$_} |

    # Modify user_name to email_address

    ForEach-Object {$_.user_name = "$($_.user_name+$emailDomain)";$_} |

    Export-Csv $file -Delim ',' -NoTypeInformation

    # Update existing users

        gam csv $file gam update user ~user_name password ~password

    # Create new users

        gam csv $file GAM create user ~user_name firstname ~first_name lastname ~last_name password ~password org ~grade_level

    # Add to Student group

        gam csv $file GAM update group students add member ~user_name

    Write-Host "Finished $file"


# Active Directory User

import-csv $studentsFile | % {

    $GivenName = $_.first_name

    $GivenName = (Get-Culture).TextInfo.ToTitleCase($GivenName.ToLower())

    $Initials = $_.Initials

    $SurName = (Get-Culture).TextInfo.ToTitleCase($_.last_name)

    $SurName = (Get-Culture).TextInfo.ToTitleCase($SurName.ToLower())

    $DisplayName = "$GivenName $SurName"

    echo $DisplayName

    $SamAccountName = $_.user_name

    echo $SamAccountName

    [int]$GradYear = IF((Get-Date) -ge (Get-Date -Month 09 -Day 01)){"{0:yyyy}" -f (Get-Date).AddYears(13 - $_.grade_level)}else{"{0:yyyy}" -f (Get-Date).AddYears(12 - $_.grade_level)}

    $Email = "$SamAccountName$emailDomain"

    $Path = "OU=$GradYear,$ADou"

    $HomeDrive = $HomeDriveLetter

    $HomeDirectory = "\\$FileServer\$GradYear\$SamAccountName"

    $Password = $_.password

    if(dsquery user -samid $SamAccountName){

        #User Exists. Updating password instead

        Set-ADAccountPassword -Identity $SamAccountName -NewPassword (ConvertTo-SecureString $Password -AsPlainText -force)

        echo '--Password reset.'


        New-ADUser -GivenName $GivenName -Initials $Initials -SurName $SurName -Name $DisplayName -DisplayName $DisplayName -SamAccountName $SamAccountName -Email $Email -UserPrincipalName $Email -CannotChangePassword $TRUE -Path $Path -homeDrive $HomeDrive -homeDirectory $HomeDirectory -AccountPassword (ConvertTo-SecureString $Password -AsPlainText -force) -Department $Department -Enabled $true

        Set-ADUser -Identity $SamAccountName -Add @{ProxyAddresses="SMTP:"+$Email}

        write (($HomeDirectory.Split("\") |measure-object).Count -1)

        if((($HomeDirectory.Split("\") |measure-object).Count -1) -gt 3)


            New-Item –path $HomeDirectory -type directory -force

            $colRights = [System.Security.AccessControl.FileSystemRights]"FullControl"

            $InheritanceFlag = [System.Security.AccessControl.InheritanceFlags]"ContainerInherit, ObjectInherit"

            $PropagationFlag = [System.Security.AccessControl.PropagationFlags]::None

            $objType =[System.Security.AccessControl.AccessControlType]::Allow

            $objUser = New-Object System.Security.Principal.NTAccount($AD+'\'+$SamAccountName)

            $objACE = New-Object System.Security.AccessControl.FileSystemAccessRule `

                ($objUser, $colRights, $InheritanceFlag, $PropagationFlag, $objType)

            $objACL = Get-ACL $HomeDirectory


            Set-ACL $HomeDirectory $objACL


        echo '--Account Created.'