Friday, January 3, 2014

GAFE Account Management Tool


If you are the person responsible for managing or creating your school’s Google Apps accounts, the following may be helpful. Our school district provides both staff and students with Google App accounts. We use Google Apps for everything from email and calendaring to (web) Sites and docs. Though we are not a huge district, 8000 students, managing the thousands of accounts can be a challenge if you have not paid the premium for some of the third party Google Apps management apps. Like many of you, we have limited funding for technology, and one of the appeals of moving from our old system to Google Apps was the cost, zero. However, like all things, there are hidden costs and when it comes to Google Apps one of those costs is the time in managing and creating accounts.





In the past we have been creating our accounts by downloading data from our student information system and then massaging that data into the appropriate Google Apps bulk upload format, ie. username, first name, last name, password. This is all done using a spreadsheet (Docs, Excel, etc.), and though not difficult, it takes a little time. Once student accounts are bulk uploaded, we then have the challenge of moving them from our root OU account level to their specific student OU (Organizational Unit.) Moving students to specific OUs is important because our root level OU is reserved for staff and it has different permissions than our Student OUs. (Bulk uploads of user accounts is only possible from the root level of your Google Apps domain...for now.)

Our student OUs have a specific subdomain, student.sylvan.k12.ca.us, and as such have different permissions. Those accounts are all part of the top level OU, student. Nested within student are our school site OUs, and below school sites are our teacher OUs. We move our student accounts, at least at the elementary level, directly into their teacher OUs. This gives us the ability to delegate student account management to our teachers if desired. We normally use GAM, https://code.google.com/p/google-apps-manager/, Google Apps Management to move our students into specific OUs. GAM is a command line application built with the Python scripting language. It is not difficult to use but because it is a command line tool it can be a little challenging for the uninitiated. To simplify the work related to creating Google Apps accounts and then moving those accounts into the proper organizational unit, I created an application using the app development tool LiveCode (http://livecode.com.) There is a full functioning OS (free) version of LiveCode available on their web site. You can download the uncompiled version of my G Account Manager app and use the Community version of LiveCode to see “under the hood” of the application, modify it, add to it, and make changes. This application expedites the process of creating both your necessary bulk upload files for generating Google Apps accounts and also generating the Python script to move those accounts into the targeted teacher organizational unit.

When it comes to using technology, and examples for why it’s important to understand coding and programming, I can’t think of a better example than this one. This little application takes a repetitive process and reduces it to a couple of clicks, copies and pastes.




In order to achieve a magnitude of simplicity, the application has to adhere to some standard configurations. These standards are ones we have developed in regards to our student accounts. As mentioned earlier, we have a student subdomain within our Google Apps domain. It is students.sylvan.k12.ca.us. At the top of the screen shot of our G Account Manager you will see a field labeled: Student Email Domain. This is where you would add your student email domain, whether it be a subdomain, or your main root level domain. One field above the Student Email Domain is the field for Organizational Unit. This needs to be your full path to your targeted Organizational unit minus the teacher OU. Ours is: Students/”Orchard Elementary School”. Notice that if your OUs are multiple words, as in Orchard Elementary School, the OU label has to be surrounded with quotes. This is a requirement of GAM and needs to be in the Python script. Finally, the last field that is required in our tool is the current, two digit, school year. The current school year is needed to calculate the students exit year in eight grade. This number is appended to their user account with a dash. So a seventh grade student’s user account would look like: jsmith-15@student.sylvan.k12.ca.us.

The remaining buttons and fields are fairly self explanatory. Import Student List with import a list of students saved as a csv file in the format first name, last name, student ID, grade, teacher. This format address elementary school students. Our middle school student accounts are generated under the school OU and at this time we do not move middle school students into specific teacher OUs as a standard practice.

The Create Accounts button will generate a massive list of student accounts in the format needed by the Google User Admin panel for bulk uploads. Due to the limit of bulk uploads consisting of 100 accounts or less, the +100 Accounts Export button will divide up the massive list into multiple files, 100 students or less each, ready for import into the bulk account creation process within your Google Apps User Administration panel. So essentially, the Export List button can be ignored unless you have a list of less than 100 students, or you just want one giant list of all your student accounts for a specific school.

The last couple fields and buttons are used to generate the Python script necessary for moving the student accounts, once they have been created in the Google Admin panel, to specific organizational units. Once you have clicked on the Create GAM Script and Copy GAM Script, you can paste the script directly from your computer’s clipboard into the Terminal window where you have installed GAM. I should note that there is a little installation process that is required when you install GAM. A working copy of GAM and Python obviously needs to be installed on your computer for this to work.

The application can be downloaded from the following links:

Apple Macintosh Version - G_AccountManager_Macintosh.zip

Windows Version - G_Account_Manager_Windows.zip

Linux Version - G_Account_Manager_Linux.zip

LiveCode Uncompiled Application (Can be opened in the free Community Edition of LiveCode and modified for your environment.) - Account_Generator_Final_livecode.zip (See Update Below)

As always, I welcome questions and comments. I hope this is useful for some folks.

Cheers!

UPDATE - Google Apps For Education changed their bulk upload template. The template is now: First Name, Last Name, Email Address, Password. When you download your student out of your student information system, be sure to download the csv file in this format.
There also has been some changes to the most recent release of GAM. As a result I have uploaded a new version of my tool that is compatible with GAM version 3.21 - Download It Here (This is an uncompiled version so you may need to download the free community version of LiveCode here: http://www.livecode.com.