First version of a PowerShell script used to upload all required Apple School Manager CSV files based on AD and CSV.

AppleSchoolManagerUpdater.ps1

### To run this script you must install Windows Management Framework 5.0 before https://www.microsoft.com/en-us/download/details.aspx?id=50395
### Be careful to get the right installation package for your server, 2012R2 isn't 2012

### Run
$clearFiles = $false
$dryRun = $false

### School settings

$asmLogin = "1234@sftp.apple.com"
$asmPassword = "password"
$asmServer = "upload.appleschoolcontent.com"
$asmServerPublicKey = "ssh-ed25519 256 63:8f:97:87:20:42:87:1e:79:23:5f:d7:da:1d:06:07"

$asmStudentScope = "students"
$asmStaffScope = "staff"
$asmLocationScope = "locations"
$asmClassesScope = "classes"
$asmRostersScope = "rosters"
$asmCoursesScope = "courses"

$searchBasesByScope = @{$asmStudentScope = @("OU=ELEVES,OU=Users,OU=Site par défaut,OU=IACA,DC=edu,DC=ecole,DC=org"); $asmStaffScope = @("OU=PROFESSEURS,OU=Users,OU=Site par défaut,OU=IACA,DC=edu,DC=ecole,DC=org", "OU=PERSONNEL,OU=Users,OU=Site par défaut,OU=IACA,DC=edu,DC=ecole,DC=org", "OU=ADJOINTS,OU=Users,OU=Site par défaut,OU=IACA,DC=edu,DC=ecole,DC=org")}

$asmLocations = @(@{"location_id" = "ecole"; "location_name" = "ecole"})

####### Dependencies #######

Import-Module ActiveDirectory

if (-not (Get-Module -Name "WinSCP")) {
    Install-Module -Name WinSCP
}

if (-not (Get-Module -Name "Pscx")) {
    Install-Module -Name Pscx
}

Import-Module WinSCP
Import-Module Pscx

Add-Type -assembly "system.io.compression.filesystem"

### File import management

$importedCSVFromEDT = Import-Csv -Path (Join-Path ([environment]::getfolderpath(“mydocuments”)) "asmEDTExport.csv") -Delimiter ";" | select *,@{Name='instructor_id';Expression={""}}  | select *,@{Name='instructor_id_2';Expression={""}}  | select *,@{Name='instructor_id_3';Expression={""}} | select *,@{Name='clean_group';Expression={""}}
$cleanedCSVFromEDT = New-Object System.Collections.Generic.List[System.Object]

$allClassesID = New-Object System.Collections.Generic.List[System.Object]

ForEach($item in $importedCSVFromEDT) {
        $teacherFirstname = $item.PROF_PRENOM.Trim()
        $teacherLastname = $item.PROF_NOM.Trim()

        Try {
            $adTeacher = Get-ADUser -LDAPFilter "(&(givenName=*$teacherFirstname*)(sn=*$teacherLastname*))" -Properties ObjectGUID
        } Catch {
            echo "Invalid LDAP search string composed with '$teacherFirstname' and '$teacherLastname'"
            continue
        }

        if ( -not ($adTeacher)) {
            echo "Impossible to find teacher ID for '$teacherFirstname' '$teacherLastname'"
            continue
        }

        $item.instructor_id = $adTeacher.ObjectGUID

        if ($item.instructor_id -eq "") {
            echo "Impossible to find teacher ID for '$teacherFirstname' '$teacherLastname'"
            continue
        }

        $cleanClass = $item.CLASSE -replace "<.*>" -replace " " -replace "\."
        $cleanClass = $cleanClass.Trim()

        if ($cleanClass -match "^\[.*") {
            $cleanClass = $cleanClass -replace "\].*\[", "-" -replace "\].*$" -replace "\["
        } else {
            $cleanClass -replace "^T", "0"
            $cleanClass = "0$cleanClass"
        }

        $cleanClass = $cleanClass.Trim()

        
        if ($cleanClass -match ".*-.*") {
            echo "Error with $cleanClass from $item.CLASSE"
        }
            
        $item.clean_group = $cleanClass
        $cleanedCSVFromEDT.Add($item)
}

### CSV mapping

$asmExportedCourses = $cleanedCSVFromEDT
$asmCoursesMapping = @{"course_id" = "MAT_CODE"; "course_number" = "MAT_CODE"; "course_name" = "MAT_LIBELLE"; }
$asmCoursesUniqueKey = "course_id"

$asmExportedClasses = $cleanedCSVFromEDT
$asmClassesMapping = @{"class_id" = @("MAT_CODE", "clean_group"); "class_number" = @("MAT_CODE", "clean_group"); "course_id" = "MAT_CODE"; "instructor_id" = "instructor_id"; "instructor_id_2" = "instructor_id_2"; "instructor_id_3" = "instructor_id_3" }
$asmClassesUniqueKey = "class_id"

### Env

$TimeStamp = Get-Date -Format MM-dd-yyyy_HH_mm_ss
$baseFolder = "$Env:TEMP\AppleSchoolManagerUpdater"
$workingFolder = "$baseFolder\asmUpdate-$TimeStamp"
$finalArchive = "$workingFolder.zip"

########################

####### Configure script env #######

New-Item -ItemType Directory -Path $workingFolder

$asmCredentials = New-Object System.Management.Automation.PSCredential ($asmLogin, (ConvertTo-SecureString $asmPassword -AsPlainText -Force))

$exports = @{}

$exports.Add($asmRostersScope, (New-Object System.Collections.Generic.List[System.Object]))

####### Functions to generate student and staff list #######
function generateASMFileForUsers($searchbases, $asmScope) {

    if (-not ($exports.Contains($asmScope))) {
        $exports.Add($asmScope, (New-Object System.Collections.Generic.List[System.Object]))
    }

    ForEach($base in $searchbases) {

        $users = Get-ADUser -Filter "*" -SearchBase "$base" -SearchScope Subtree
        
        ForEach($userDN in $users) {

            $adUser = Get-ADUser -Identity "$userDN" -Properties ObjectGUID,GivenName,Surname,Department,EmailAddress,UserPrincipalName
            
            $userObject = New-Object System.Object
            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "person_id" -Value $adUser.ObjectGUID
            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "first_name" -Value $adUser.GivenName
            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "last_name" -Value $adUser.Surname

            if ("$asmScope" -eq $asmStudentScope) {
                Add-Member -InputObject $userObject -MemberType NoteProperty -Name "grade_level" -Value $adUser.Department
                Add-Member -InputObject $userObject -MemberType NoteProperty -Name "password_policy" -Value "8"
                
                $relatedClasses = $allClassesID | Where {$_ -match ".*$($adUser.Department)" } 

                ForEach($classID in $relatedClasses) {
                    $rosterObject = New-Object System.Object
                    Add-Member -InputObject $rosterObject -MemberType NoteProperty -Name "roster_id" -Value "$($adUser.ObjectGUID)-$classID"
                    Add-Member -InputObject $rosterObject -MemberType NoteProperty -Name "class_id" -Value $classID
                    Add-Member -InputObject $rosterObject -MemberType NoteProperty -Name "student_id" -Value $adUser.ObjectGUID
                    $exports[$asmRostersScope].Add($rosterObject)
                }
            }

            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "email_address" -Value $adUser.EmailAddress
            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "sis_username" -Value $adUser.UserPrincipalName

            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "location_id" -Value "ecole"
            
            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "person_number" -Value ""
            Add-Member -InputObject $userObject -MemberType NoteProperty -Name "middle_name" -Value ""
            
            $exports[$asmScope].Add($userObject)

        }
          
    }

}


####### Function to convert array of hastables to CSV ready list #######
function convertArrayOfHastables($arrayOfHashtables, $asmScope) {

    if (-not ($exports.Contains($asmScope))) {
        $exports.Add($asmScope, (New-Object System.Collections.Generic.List[System.Object]))
    }

    ForEach($hastable in $arrayOfHashtables) {

            $object = New-Object System.Object

            ForEach($key in $hastable.Keys) {
                Add-Member -InputObject $object -MemberType NoteProperty -Name $key -Value $hastable[$key]
            }

            $exports[$asmScope].Add($object)
        }
}

####### Function to export scope to CSV #######
function exportScopeToCSV($asmScope) {
    $exports[$asmScope] | export-csv -Path "$workingFolder\$asmScope.tmp" -NoTypeInformation -encoding "unicode"

    $Utf8NoBomEncoding = New-Object System.Text.UTF8Encoding($false)
    $content = get-content "$workingFolder\$asmScope.tmp"
    [System.IO.File]::WriteAllLines("$workingFolder\$asmScope.csv", $content, $Utf8NoBomEncoding)

    rm "$workingFolder\$asmScope.tmp"
}

####### Function to prepare export from CSV #######
function convertCSV($importedCSV, $mapping, $asmScope, $uniqueKey) {

    if (-not ($exports.Contains($asmScope))) {
        $exports.Add($asmScope, (New-Object System.Collections.Generic.List[System.Object]))
    }

    $existingKeys = New-Object System.Collections.Generic.List[System.Object]

    ForEach($item in $importedCSV) {
        
        $object = New-Object System.Object
        ForEach($asmKey in $mapping.Keys) {
            $keysToMap = $mapping[$asmKey]
            $finalValue = "ID"

            if ($keysToMap -is [system.array]) {
                ForEach($key in $keysToMap) {
                    $finalValue += "-"
                    $finalValue += $item.$key
                }
            } else {
                $finalValue = $item.$keysToMap
            }

            Add-Member -InputObject $object -MemberType NoteProperty -Name $asmKey -Value $finalValue
        }
        
        Add-Member -InputObject $object -MemberType NoteProperty -Name "location_id" -Value "ecole"

        if ( -not ($existingKeys.Contains($object.$uniqueKey))) {
            $existingKeys.Add($object.$uniqueKey)
            $exports[$asmScope].Add($object)
        }
    }
}

####### Generate CSV for Locations #######
convertArrayOfHastables $asmLocations $asmLocationScope

####### Generate CSV for Courses #######
convertCSV $asmExportedCourses $asmCoursesMapping $asmCoursesScope $asmCoursesUniqueKey

####### Generate CSV for Classes #######
convertCSV $asmExportedClasses $asmClassesMapping $asmClassesScope $asmClassesUniqueKey
ForEach($class in $exports[$asmClassesScope]) {
    $allClassesID.Add($class.$asmClassesUniqueKey)
}

####### Generate CSV for Students #######
generateASMFileForUsers $searchBasesByScope[$asmStudentScope] $asmStudentScope

####### Generate CSV for Staff #######
generateASMFileForUsers $searchBasesByScope[$asmStaffScope] $asmStaffScope


####### Generate all available CSV #######
ForEach($asmScope in $exports.Keys) {
    exportScopeToCSV $asmScope
}

####### Manage dry run if needed #######

if ($dryRun) {
    echo "CSV files created. It's a dry run so no upload to Apple School Manager."
    echo "$workingFolder"
    ii "$workingFolder"
    exit 0
}

####### Generate ZIP archive #######
[io.compression.zipfile]::CreateFromDirectory($workingFolder, $finalArchive) 

####### Configure SFTP session to be dropbox compatible #######
$asmSession = New-WinSCPSession -HostName $asmServer -Credential $asmCredentials -Protocol Sftp -SshHostKeyFingerprint $asmServerPublicKey
$asmTransferResumeSupport = New-Object -TypeName WinSCP.TransferResumeSupport
$asmTransferResumeSupport.State = [WinSCP.TransferResumeSupportState]::Off
$asmTransferOptions = New-Object -TypeName WinSCP.TransferOptions
$asmTransferOptions.ResumeSupport = $asmTransferResumeSupport
$asmTransferOptions.PreserveTimestamp = $false

####### Send archive to Apple #######
Send-WinSCPItem -WinSCPSession $asmSession -Path $finalArchive -TransferOptions $asmTransferOptions -Destination "/dropbox" -Remove

####### Housekeeping #######
Remove-WinSCPSession -WinSCPSession $asmSession

if ($clearFiles) {
    Remove-Item -Path "$workingFolder" -Recurse
}

exit 0

      Nice script. This is exactly what I've been looking to do.

I've almost got it working, but can't work out the significance of the "asmEDTExport.csv" file. Would you mind explaining?

Did you get any reply on this? I'd like to know the formatting of the file.

It looks to be a staff CSV export specifically for their own use case for mapping instructor 1, 2, 3 to the grades I believe.