Automated Account Creation
Automated Account Creation - Workflow
This piece describes the 5 steps involved in automating the creation of new gSuite accounts from data held in a school system - in my case SIMS. There is also a sixth step listed detailing how the data can be used to sync Classroom data.
The steps are:
1 - Generate the data as CSV files using the SIMS report download to your PC.
2 - eMail the CVS files to your Gmail account
3 - When the email lands transfer the attached files to a designated folder.
4 - Import the data from the CSV files into separate sheets of a Google spreadsheet and delete the CSV files, using SheetsIE
5 - Run a timed trigger in your Accounts Manager spreadsheet to identify any new accounts and create them.
6 - The Classroom Manager add-on runs on a timed trigger to import the data from the spreadsheet into my Classrooms spreadsheet - compares the data to the data taken from GSFE and adds/removes teachers and students from classroom as necessary.
1 - Generate the CSV data from SIMS
This is a Scheduled task run from my PC which runs a batch file to run the reports in SIMS
The scheduled task is set to run at 9am and repeat every hour for 8 hours
The text in the batch file is shown on the right.
To maintain the user accounts I only need the students file. I use the other files to maintain my school's Google Classroom courses.
The script calls 4 reports and outputs the resulting CSV files to my Network drive
The 4 files are
A - Classes - lists which students are in which classes
B - Students - list of students currently in SIMS
C - Periods - gives a list of teachers taking which classes
D - Parentemails - (list of Parent email addresses - not necessary for this process used for others)
2 - Email the CSV files to my school GMAIL account
I use the SENDEMAIL command line utility to send email through a Gmail account there are details of various alternative tools you can use for this here
I have set up this process as another Scheduled Task on my PC. Details are shown on the right.
3 - Transfer the attached files to my Google Drive
This uses the sheets Add-on -- Save Emails and Attachments
It takes the attached CSV files and saves them in the designated folder. The add-on allows me to run 1 job for free. If I wanted to more jobs with it the cost would be $29 a year.
The video on the right explains how the add-on works.
4 - Import the CSV files into a Google Spreadsheet
I use a sheets add-on I have created called - SheetsIE - you can find out more about it - and install it from this link
The add-on creates a new master spreadsheet which I then use to specify which CSV files should be imported into which sheets files and then what should happen to those csv files
I have used the add-on to set up a timed trigger which runs every hour. When it runs, if there are no files to upload, it stops. If there are files it copies the data from the CSV file into the spreadsheet. Each CSV file is copied to a separate sheet in the spreadsheet. Then it deletes the CSV file.
This video explains what the SheetsIE add-on does and how to use it
5 - Timed Trigger in Accounts Manager
The spreadsheet which you use to maintain your user accounts must have a new sheet called "newUsers". That sheet must contain the user's email address, First Name and Family Name, but may also include any or all of the fields listed on the right (it may also include other fields if you need those for other purposes. Those fields will be ignored by the add-on and will not interfere with the upload process).
Please note the field headings are the column headings from the main accounts manager sheet. You can use as many or as few as you wish but the headings must be spelled and capitalised the same way.
The column sequence should match the sequence in the originating sheet.
You can also set up a form to allow staff to create new accounts without having to give them Admin rights on your domain.
To do that create the form. The question titles must correspond to the field names. Direct the form submissions to be saved to the Accounts spreadsheet - The add on assumes they will be saved to a tab named Form Responses 1
Once you have setup the import rules using the SheetsIE add on you can set the trigger in Accounts Manager to run every hour. That will check the New Users sheet and the Form Responses 1 sheet for any email addresses which do not already appear in the usersV2 sheet. It will add any new email addresses to that sheet together with the the details. Then it will run the process to create the new accounts.
Force Password Change Y/N
Include in global contact directory
Scheduled Start Date