Creating the "BAT-AddStudentsToClasses.csv" File USING MS Excel

This process will help you create the "BAT-AddStudentsToClasses.csv" file for use in the Assign Students to Google Classroom Classes task.

Data Mining

Firstly you will require a data export from SIMS (or other MIS that your school uses) with the following information.

UPN - Unique Pupil Number (I use these as a unique ID on my G Suite users table)
Classes that the pupil is enrolled on

Please see the example below:

UPN to G Suite ID

You'll now need to convert your UPN IDs to your G Suite IDs, edit the sheet 'GSuiteIDfromUPN' on the Excel file.

Vlookup

Make a copy of the 'ImportFromSIMS' sheet and call it 'SheetToConvert'

Now that you have a sheet that lists your G Suite users and their Unique Pupil Number (UPN) you can go ahead and write the vlookup on the 'SheetToConvert' sheet.

Use the formula below in cell 'A2' and then fill down to the end of your data:

=VLOOKUP(ImportFromSIMS!A2,GSuiteIDfromUPN!A:B,2,0)

Run the Macro

If you don't already have the Developer tab available in Excel, go to:

File > Options > Customize Ribbon

Tick the 'Developer' tick box under 'Customize the Ribbon' and click 'Ok'.

Now you can run the Macro called 'Convert'.

Data Output

Once the Macro has finished running you will be presented with a new sheet called 'KidsClasses'. You can now copy these columns of data into your "BAT-AddStudentsToClasses.csv"

*(making sure to place the UserIDs and Class Codes in the correct columns!)*

BAT-Students-Classes-Maker-Tool.xlsm

VBA Code for MS Excel

Option Explicit

Public Sub Convert()

    Const FC As Long = 1    'first col (ID)

    Const FR As Long = 2    'first row

    Const NEXT_COL As Long = FC + 1

    Const DB_WS_NAME As String = "KidsClasses"

    Dim ws As Worksheet, db As Worksheet, lr As Long, lc As Long, maxRow As Long

    Dim arr1 As Variant, arr2 As Variant, i As Long, j As Long, k As Long

    Set ws = Worksheets("SheetToConvert")   'main sheet -----------------------------------------

    lr = ws.Cells(ws.UsedRange.Rows.Count + 1, FC).End(xlUp).Row

    lc = ws.UsedRange.Columns.Count

    If lr >= FR Then

        maxRow = (lr - (FR - 1)) * (lc - FC) + FR   'set result area

        Application.ScreenUpdating = False: Application.DisplayAlerts = False

        For Each db In Worksheets

            If db.Name = DB_WS_NAME Then

                db.Delete: Exit For

            End If

        Next

        Set db = Worksheets.Add(After:=ws): db.Name = DB_WS_NAME

        arr1 = ws.Range(ws.Cells(FR, FC), ws.Cells(lr, lc)).Value2

        arr2 = db.Range(db.Cells(FR, FC), db.Cells(maxRow, NEXT_COL)).Value2

        k = FR - 1

        For i = FR - 1 To lr - (FR - 1) 'all rows

            For j = NEXT_COL To lc      'all cols

                If Len(arr1(i, j)) = 0 Then Exit For  'exit inner For (this row is done)

                arr2(k, FC) = arr1(i, FC)

                arr2(k, NEXT_COL) = arr1(i, j)

                k = k + 1

            Next

        Next

        db.Range(db.Cells(FR, FC), db.Cells(maxRow, NEXT_COL)).Value2 = arr2

        Application.DisplayAlerts = True: Application.ScreenUpdating = True

    End If

End Sub