Using Google Sheets to Create Google Apps Accounts!

Purpose

Last year I took on the task of creating user accounts for the High School and Junior High students/staff.  My goal was to help our upper levels use them and for it to go smoothly.  Goal Achieved.  Sadly, I did it in a very inefficient manner last year.  I had the secretaries email me the student information from SIS (Student Information Services) with their appropriate grade level.  Then I typed every person's name (first and last in separate cells), username with domain and then applied the same password to the accounts (The only thing I did efficiently).  While it was successful at creating the accounts, it took "For.. EV... ER!!!"

Format Received
from Secretaries

This year after attending a google sheets presentation done by +Jay Atwood, I decided there had to be a more efficient way to approach it.  Thus the purpose of this post today is to help you do it more efficiently (Though I know there are still more simple ways to do it, so do not be afraid to comment with your own solutions).

Step 1

As I did last year, I got the student information from the secretaries (this time the Upper Elementary since the majority of the high school and junior high students remained, minus the seniors).  They sent it in the same format of one cell containing the last name, first name.  I then looked for a formula to split names between first and last name into separate cells.  That Formula is =SPLIT(A2:A,",") with "A" being the cell in which the names are located. The second part of the formula "," tells the formula to split the words at the comma.

Split Formula for the Names

Tip:  I tried to use the =Arrayformula to carry down the split to the rest of the data but was unsuccessful.  Ultimately I just triple tapped the small blue box which sent the formula down the sheet.

  Step 2

Formula for the Trim
of the First name with space

Now with the names split, I ran into a minor problem with the first name.  The formula split the names but left the space before the first name (because it was located after the split).  When trying to combine the names (will talk about that here in a second) it left a space, which caused problems.  The next formula I had to use then was to get rid of that space.  The formula for that is =trim(D2:D) which took away the space, leaving the first name the way I needed it.

Tip:  I tried to use the =Arrayformula to carry down the split to the rest of the data but was unsuccessful.  Ultimately I just triple tapped the small blue box which sent the formula down the sheet.

Step 3

With the first name and last name in the format I desired, I began the process of creating the username.  Our district decided to make the username as followed: First initial of the First Name combined with the full Last name.  Here is the formula to do that, =CONCATENATE(LEFT(E2,1),(C2)).  Of course the Columns reference where I have the specific data so you will need to change it to match your specific spreadsheet.

Step 4

Now I created a column with the domain of our school just so I could finish the username with the goal of creating the accounts for the school's Google Apps Domain.  The final column I created was the fully combined username for the Google Apps Domain.  I wanted to combine the first initial+last name with the appropriate domain.  The formula for that was =CONCATENATE(F2,G2) , with F and G being the location of the data. 

Formula for the CSV file sheet

Step 5

Then I created the sheet that would ultimately turn into the CSV used for the Google Apps Domain (based upon the example provided by Google).  Here is how the sheet should be setup: Column A = Email Address; Column B = First Name; Column C = Last Name; Column D = Password.

Formula for the Password
for the CSV File Sheet

Now the first sheet had all the information I needed so there was no need to retype it all.  The basic formula I used was =ARRAYFORMULA('Formulas To Auto-Create Usernames'!H2:H).  From what I understand, the arrayformula portion of the formula pushes the formula down the rest of the rows. Then the last part of the formula (in this case H2:H) shows where to pull the data.

For the Password, which I made standard for different organizations in the domain, I used another formula =arrayformula(IF(REGEXMATCH(A2:A,"@"), "Example24&", " ")). Basically this formula pushes down each row (arrayformula), then finds the "@" (used for the username), then puts in the designed password. If there is no "@" then it puts in a blank space.

How to download the
CSV file

Step 6

The final step was to download the sheet as CSV file (Comma-separated values) on the current sheet.  Then go to the admin page and create "multiple users."  Next upload the CSV file, which will finalize the creation.  It will send you an email when all the users have been properly created, and let you know if there were any errors.

Admin Page for User Upload

Nutshell

In the end, this Google Sheet I created (with the assistance of +Jay Atwood's presentation) helped me to create vast amounts of user accounts in a far shorter amount of time.  Obviously I do not understand all the formulas or how they work or ways I could do even better.  Hopefully this will ultimately help you reduce your work in some form or fashion.  Feel free to add any comments on how this process could be made even easier!  

Here is a link of a copy you can make of my sheet I created:  Example Google Sheet