Posted in Uncategorized

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

Posted in Uncategorized

Ready, Setup, Slowly

Ready, Setup, Slowly

Map of School District (Made by using mymaps.google.com)

District DESE Information

For those wondering what our school district is like, I have provided both a map and the Missouri Department of Elementary and Secondary Education data.  You will notice pretty quickly that we are indeed a small school district, at least student population wise.  We currently serve roughly 711 students K-12th grade, with 72 certified staff members.

Chromebooks Arrived!

Thanks to students Chase White and Briley King for helping me
get all the Chromebooks to my classroom!

Earlier this week the Chromebooks we order for the school district arrived!  Due to it being summer, I had to scrounge around the school to see if anyone was around to help me move them to my classroom.  Luckily the first session of weight lifting had just ended so I was able to pick up two students, Chase and Briley.  These two gentlemen used their ingenuity and found carts in the Cafeteria that helped us to move a greater number of chromebooks in a shorter time period.

With all the Chromebooks in my room, I began the setup process.  I took one of the power supplies from a box and used it for all the Chromebooks (for efficiency purposes).  Sadly, before I could begin I had to figure out why the outlet in my room did not work.  After getting a hold of our maintenance crew, we realized they had tripped the breaker while cleaning the floors. 
 
After the start-up screen

Setup

Initially I would open one box at a time, and begin the setup process.  I later learned to open a larger number of boxes, and then do the setup process of multiple Chromebooks at one time.

Then I started the Chromebook unboxing, and began setting them up.  These Chromebooks are designed to not turn on (even if opened) until they have been plugged into a power source (thus the one power supply to rule them all).  Once plugged in, it would begin the load-up process.  The first question it asks you is for your network ID and Password. *Side Note: Once the power has been turned on, you can unplug it and finish the process as most Chromebooks battery is at 70+% power.

A few Chromebooks said this when trying to enroll them.
After it accesses the Wifi, it will bring you to the standard sign-in.  Note For Chromebook Management: Do not sign-in without the “enterprise enrollment” screen, otherwise you will have to reset the Chromebook. To access the “Enterprise Enrollment” screen, press CTRL + ALT + E, which should immediately bring you to the enrollment screen.  If for whatever reason it says “opps…” instead of the enrollment screen, simply access as a guest.  Then sign-out of guest and press CTRL + ALT + E again, which should work like a charm now.

My setup for the afternoon.

Suggestion

As I mentioned earlier, I have three different schools that these Chromebooks are being sent to, each with their own “Organization” I created on the Google Apps Admin Dashboard.  So what I did was create a generic user (ex. Deploy@domain) with the administered role of “Services Admin” (which allows enrolling Chromebooks into management).  Then go to Device Management > Chrome Management Sidebar Link > User Settings > Enrollment Controls (Picture down below).  Then have the settings as followed: “Place Chrome device in user organization during manual enrollment,” and set it to “Place Chrome device in user ogranization.”  Finally, move the generic user to the desired organization of the Chromebook.  

Overview:  What this does is put the Chromebooks into whatever organization you plan on distributing them too.  Keeps you from worrying about locating the correct Serial Numbered Chromebooks in the future (For settings purposes, etc).

Progress

At this point, I am half-way through enrolling all the devices into the management console.  Hopefully I am setting it up properly in order to avoid frustration later down the road.  I’m sure there are even more tricks/tips that I could be using to help expedite this process, but for now all seems to be going well.  If you have any ideas or tips, feel free to comment!

Needed a system to help keep the Chrromebooks
separate by building.
Easy way to keep track of the Power Supply

Start-up Screen
A lot of boxes…
Progress being made but slowly…