Posted in GAFE, Uncategorized

Google Forms; Basic Design (Part 1)

(Google Forms – Responses, Flubaroo, and Classroom Part 2 Blog Link)

Google Forms – Basic Design (Part 1)

 

forms2blogo
Google Forms

This year as a school we began implementing “Student Learning Objectives” as part of our curriculum.  In order to do this, we followed the Department of Elementary and Secondary Education of Missouri’s suggested “SLO – Progress – Tracker.”  In order for the tracker to be effective, teachers needed an easy way to collect quantifiable data on student performance and content knowledge.  For many teachers, the easiest way to do this on a consistent basis is Google Forms!  In part 1 of Google Forms, we are going to discuss some ideas of how to design your Google Forms to be effective in the classroom.

 

Google Forms

To access Google Forms, go to forms.google.com or go to your Drive Account (drive.google.com), click “New” – More – Google Forms.  When accessing it from forms.google.com, it will give you quick templates that could be useful to have assessments within minutes.  

The Basics About Google Forms

 

Screen Shot 2016-04-24 at 1.35.53 PM
Title

Just like every other Google Apps product, when the Google Form is first created, they are “untitled.”  Change the title to the appropriate standard or assessment (Tip: In the title include a shorten name for Formative/Summative Assessment for organizational purposes.  For Example: “FA” for Formative Assessment.)  I have attached a Google Document that goes into more detail the specific options you can do with the form, so that I can get into designing the form. (Google Forms – Cheat Sheet)

 

Design

Obviously there are a slew of ways to design your form, but the following has been wonderful for my purposes.  The first questions I always ask are First Name, Last Name, Hour, and Password.  The two names and password are setup as “short answer” and the Hour “Drop Down.”  To get the password question to work appropriately, you go to “Data Validation,” “Text,” “Contains,” enter desired password.  Tip: If “custom error text” is left then the password will be revealed to the students.  Instead, create a standard response like “Incorrect Password.”  For Example, for my American Government test on the Judicial Branch, I could use the password “Chief Justice” that students would use to access the assessment.

Screen Shot 2016-04-24 at 1.14.20 PM
Data Validation

 

 

After the sign-up page is completed, create a new “section” which are essentially pages to the form.  From this point forward, you can incorporate a variety of questions to help you assess your students content knowledge.  

Brian “Lewis” Pier suggested that in lieu of a password, create all the necessary copies of the Google Form and connect their responses to the same spreadsheet.  With the new option in Google Classroom to assign Forms to specific students, it helps prevent students from seeing the test prior to your class period.

Suggestions

  1. If you are a G Suite for Education (GAFE) school, then I would suggest setting up a short answer question that requires them to put in their school email account.  This will allow you to use Flubaroo to send students their results upon completion of their assessment.  (Google Forms – Using Flubaroo)
  2. When doing a “Matching Question” use “Multiple Choice Grid.”  I have found putting the question on the “row” portion and answers on the “column” portion it has been easiest for students to view.  Tip:  Put no more than 5 matching words-definitions per grid so that students can easily use the grid.

    1. Be sure and click “Require one response per row,” “Limit to one response per column” and “Shuffle Rows” to help deter cheating between students.Screen Shot 2016-04-24 at 1.52.15 PM
  3. Provide yourself multiple “Sections” throughout the assessment.  It helps break-up the test for students and provides opportunities for corrections on the part of the teacher.  For example, if a student notices that something is spelled wrong (which happens) students can go to a previous section while you correct the error.  Once corrected, students return to the section and it is corrected on their form.  (I generally do no more than 7 Multiple Choice Questions per page)
  4. When making these assessments, I first create it on a Google Doc.  This is handy when going to create a form, because Google Forms will automatically detect the “Enter” was used and make that into a separate “option.”  Here is a Gif demonstrating how it works:

    Tip: There is an Add-On to do the Google Form but I generally just have two windows open side-by-side between the doc and form.
  5. On a multiple choice question, I always put the correct answer as the bottom answer.  The reason is when students complete the assessment, it makes it easy to identify how the students do collectively.  Under the “Responses” tab, the form will color code each option (Gif demonstrating how it looks).  Since nearly all my multiple choice questions are four options, then the correct answer will be green (option four is green).  Definitely handy when trying to quickly determine collective student comprehension of material.

    2017-07-10_22-04-47.gif
    It is quick and easy to identify content that was not grasped by the students, which allows for re-teaching, analysis of the question/options, etc.

Any other ideas? How do you incorporate Google Forms into your classroom?  For more ideas, check out Alice Keeler’s Google Forms Section.

Also check out Google Forms – Responses, Flubaroo, and Classroom Part 2 Blog Link

Posted in Professional Deveoplment

Twitter – Just A Nudge

Twitter.com
As the school year rolls along, I thought it was a good time to encourage you to be involved in Twitter.com for professional development purposes.  For those who have never tried it before, feel free to go to https://twitter.com/search-home and start seeing what is available to learn today!

Some of the basics include:

1.  Twitter handle is your identifying name (similar to email address).  For example, my Twitter handle is @coachhoup24 .  If someone wants to “tweet at me” (again similar to sending a message on email), they just compose a message with “@coachhoup24 …” and I will receive it.
2.  There is nothing wrong with being a fly on the wall with Twitter and just following people.  Nothing says that you need to ever personally tweet.
3.  Hashtags (#) on Twitter can be used in one of two ways.  First to demonstrate your nonverbal communication.  For example, a tweet could say, “Today’s lesson plan involved puppets and singing! #TheKidsLovedIt #ProudTeacher”
4.  Tweets can use no more than 140 characters, including anyone that you mentioned.
5.  If you don’t like what people are tweeting, then simply unfollow them (They will never know!).

Join Us!

If you are considering joining Twitter, here is a resource to help guide you in developing a professional Twitter account:  New 2 Twitter Resource .  This resource provides ideas on how to set your own account up as a educator and what to do once you are active.  When we as a district did a survey a couple months ago, some teachers already involved with Twitter provided some of their “top people to follow.”
They suggested:
This is obviously not an exhaustive list, but none-the-less a good place to start.
The final benefit one gets from Twitter is the opportunity to participate in events called, “Hashtag Chats.”  One great one to follow is #MoEdChat, which takes place every Thursday at 9 PM CST.  It’s a great event to just jump on and see what other educators throughout Missouri doing.  One final chat that is great to follow is #GAFESummit, which is used by +EdTechTeam for their Google Summits held throughout the world.  It is a “slow chat,” meaning it is happening throughout the day, and most active when they have summits on various weekends.

Other Resources:

Continue reading “Twitter – Just A Nudge”

Posted in GAFE

Google Forms Do More Than Just Surveys (S. Gadient)

By: Sharon Gadient – AGHS Math Teacher

Intro

It’s that time of year again — the “third quarter slump”: that time when I am desperately searching for some way to gain and hold my students’ attention long enough to stuff some knowledge in their heads. Switching up instruction is one of my tried-and-true strategies, and since I just got a working set of iPads in my classroom a few weeks ago, I was looking for a free tech tool I could use to get more interaction from my Algebra 2 students, allow for “think time” and help students self-assess. After playing with Socrative and Nearpod, I found it in a surprising place when I turned to a couple of features in Google Forms that give students real-time feedback on their guided practice problems.


What kinds of interaction does forms allow?

There are two tools that we can use for this depending on the type of question:
  1. “Go to Page Based on Answer” is an option in Multiple Choice questions that gives students different feedback depending on the answer they select, and can redirect them to try again.

  1. “Data Validation” is an option in Text responses that tells students immediately if they are wrong, will not let them proceed until they get it right, and can give students hints.
Click the link below to see a silly example quiz that demos some of the interactive things you can do with forms.

Make your own

In the video below, I walk through the process of creating interactive questions in forms.
For a text version of the directions, go to this link:

How I use it

I create a few “Guided Practice” problems that students complete as we talk through our note-taking guide, and I sometimes add in some or all of their homework assignments to be completed in forms. Students get feedback on the guided practice, right/wrong feedback on the first couple of homework problems, then they are on their own and I can have forms grade their responses automatically with the Flubaroo add-on. I also leave these forms posted in Google Classroom after the lesson for students to access any time for review.
I have found that Students are more engaged and ask more questions during the lesson than they did without using the forms. This is especially popular with my weaker math students who gain more think time and build confidence with the guided practice.

As always, playing with it gives you a better feel for the process, so tinker away — I’m sure you’ll discover things I never thought to try.

Bio:

Sharon Gadient is a Math Teacher at Ash Grove High School. This is her eighth year of teaching mathematics, and she still loves it. Sharon enjoys applied mathematics, and has a special fascination with fractals (both the pretty and the useful). Her hobbies include playing with technology, writing, and curling up on the couch with her German Shepherd to watch Dr. Who.

Twitter Account: @library_ghost

Posted in Uncategorized

Spice It Up With EdPuzzle! (B.Watts)

https://edpuzzle.com/
This is a blog post written by one of the students at Ash Grove High School.  

Spice It Up With EdPuzzle!


Are you tired of boring presentations? Do you need a way to ‘spice up’ the classroom to engage your students? If you’re ready for an overhaul of awesome, prepare for the incorporation of EdPuzzle, the latest and greatest in class video presentation technology. (Think Billy Mays here)

With EdPuzzle, making an interactive video for students is simple! For easy presentation and conciseness (and less work on my end), I have developed a very easy-to-follow, step-by-step guide!


Step numero uno:

Get that ‘thang’! Get to their website here or get the Chrome app here (this will make a link on your Google Chrome, which will just take you to the website.)


Step 2:

Create a free account.

Did I mention it was FREE? Oh yeah, I did.
(Google App teachers, feel free to use that account to seamlessly connect to your classroom. “Seamlessly”, or at least those overlapping seam-things that don’t make that awkward bump on the shoulder of your shirt. We’ve all been there.)

Step Tres:

Make your first video! Either upload a video from YouTube or anywhere else (Khan Academy, Learn Zillion, and more), or your own video. I’m sure you won’t encounter any copyright law infringements.

Step 4:

After uploading, you can crop the video, add voice over commentation, and even add quiz questions that pop up throughout the video. It’s like an interactive YouTube for schools.

Step Cinco:

Share or “assign” the video to your class on EdPuzzle, or your Google Classroom class, if you used your Apps account.


See? It’s as simple as that. Now you must take action. Go, take what I have given you, and teach those kids. Let us know how it goes and any other suggestions you might have for using EdPuzzle!


  • Post videos or use from YouTube, Khan Academy, Learn Zillion, and more
  • Crop video, add comments, insert voice-overs, and create a quiz for video
  • See data of student quizzes taken
  • Engaging, Effective, and FREE



Brandon Watts Bio: 

 Brandon Watts is a Senior at Ash Grove High School. He is excited to continue his education in 

human health and science at Missouri State University (Fall of 2016). He enjoys doing everything, whether it’s swimming, biking, running, all three of those combined, futbol-ing, hiking and enjoying the day, or even reading a good book on differential calculus or exercise physiology. Brandon is a lifeguard, lifeguard instructor, and pool supervisor, meaning he has some level of responsibility. Having fun is his main concern, and he is ready to take on life!

Posted in GAFE

Lucidcharts To The Rescue!

Web Diagram

In my American Government class, we began the discussion of the legislative branch.  One of the important aspects of this branch is the process of how a bill becomes a law.  This semester I decided that we would do it with a tech tool, which could allow for collaboration, correction, etc.  
Initially, I had students use “Mindmaps by mindmapmaker.org” to diagram how a bill becomes a law.  After the first day of students working with this tool, it became apparent that it was a frustrating tool for the students.  (Mindmaps may be a wonderful tool, but for what I was asking my students to do, it was not helpful.)

Doesn’t Work… Fix it

As I saw the frustration on my students’ facez, I decided it was time to find a new tool.  So I went back to drive and looked for another web diagraming tool.  That’s when I found “Lucidcharts for Education,” and immediately I knew it was going to be a better tool for our project.  It allowed students to make any adjustments seamlessly, while also saving instantly (which mindmap failed to do).  

Lucidcharts

Initially when you sign up for Lucidcharts, it places you in the “free-version” which has various restrictions such as:  create only 5 documents and the use of 60 “complexities” (or as I told my students “symbols”).  For the project we were doing, the “complexities” limitation was frustrating, but as a program, the students absolutely loved it!  It allowed for quick adjustments, additions, and reformatting that was excruciatingly tough to do on Mindmaps.

How to use Lucidcharts

To create a web diagram using Lucidcharts, just go to your Drive and click the following: New–>More–>Lucidcharts.  Then create a document (either blank or from one of their templates) in order to get started.  From there, you can drag various symbols over from the left toolbar such as Text, Shapes, etc.  With the boxes or text on the document, you can draw lines to help create a flow from one step to the next.  (After Lucidchart approved our school for the free education upgrade, you will have unlimited complexities!  That definitely made all the difference).  To help make the web diagram stand out, students can change the fill color of the boxes, lines, text, etc.  

For our project, I suggested to students that they color code the steps to help them quickly identify things like vocabulary or the house/senate differences.  Finally, students can share their projects with each other as a point of reference.  It was interesting how different students found steps/information that other students had missed, so sharing it with each other helped them to add them to their own project.  Also, (unlike Mindmaps) Lucidcharts allows individuals to work simultaneously with one another (just like the other Google Apps Tools). 

Google Classroom – Turn It In

  

I provided the assignment through Google Classroom with the expectations.  As students found valuable resources, they would share them via the “class comment” so that other students could use them.  When students finished, they would add it to Google Classroom the same way they would turn in other Google Apps products (docs, sheets, slides, etc.).  

Student Examples



Suggestions

Any other suggestions for fellow teachers to use this (or similar) tools in their classroom?  Give us your thoughts!

Posted in Uncategorized

MyMaps: Student Created Maps!

Earlier in the school year I introduced the idea of using Google MyMaps in the classroom.  Over the last week couple of weeks, I began using it in a couple of my classes.  The result of the students using MyMaps was stunning.  While these maps are imperfect (missing details periodically due to student error), they demonstrate so many possibilities for student creation.

European History – European Explorers (Freshmen through Seniors)

European History – Absolutism Research (Seniors)

World Geography – Distribution of Natural Resources (Sophomores)


World Geography – North American Research (Sophomores)

Lesson Plan Design

First, determine what your goal/objective is for the lesson.

For both World Geography and European History, it was an easy and natural fit to use MyMaps.  In European History, I have always had students do some type of presentation (Powerpoint, Keynote, Slides) to detail the trips and explorations of various explorers.  But as I prepared for that annual lesson, I decided to give MyMaps a go around in order to have a completely student created product.
For World Geography, I have always wanted them to get a greater understand of the world’s natural resources and the disparity of their use/production throughout the world.

Tip: Create a generic map for students to look at, in order to understand how they can manipulate their own maps.

Second, provide the guiding questions and objectives for students to quickly access and research. 

New this year, I have been using Google Classroom for my classes.  To get students the questions and topics, I posted an “assignment” on Google Classroom with the following:

Topics/Questions
Types of resources to use
How to cite sources
Link to MyMap (Created by me, but will explain how later)
Initial Due Date (To help critique student work)

Tip:  When creating the MyMap link as a teacher, go to mymaps.google.com.  Then click share and change access to “Anyone at *School Domain* with the link” and “Can Edit.”  Next, copy the URL and post it on the Classroom Assignment.

Front Page of
mymaps.google.com 


Third, students begin researching and posting their information to the linked MyMap.

I had each student (or in my case pairs) create a “layer” on their MyMap for a couple different reasons.  First, I wanted to be able to easily assess the students without having to search throughout the map for each students work.  Second, it prevented students from accidentally deleting or changing other students work.

Tip:  There is a limit to the number of layers (10 Total) you can create, so be cognizant of the amount.

Fourth, review students work and provide immediate feedback on their design and information.

 
Example of Student work

As students begin posting their information to the map, give them insight on how best to improve their layer.  For example, having students use different colors/symbols in order to differentiate their work from other students (Ex. Christopher Columbus being the yellow line and markers).  Some other ways to improve their map would be to include the following:  Pictures/videos on markers, journal writings or data information for each marker, proper structure/organization on the side information bar.

Tip:  Unlike the other Google Apps tools, Google MyMaps does not automatically update as students work on it.  However, if you reload the map, all the other work will be updated to your map.

Fifth, share the student work with the rest of the world!

After students have completed the assignment, change the share settings to “On – Public Web” and “Can View.”  Then copy the URL and share it out to the rest of the world!

Tip: Post it to Google+ in order to demonstrate your students work, as well as inspire other teachers to have students create their own resources!

What other ideas do you have for student created maps?  Ways to make the process even better?

A video below to explain how I use this in the classroom!

 
Posted in Alice Keeler, Kyle Pace, Patrick Dempsey

Google Educator Presentation

This Friday I get the joy of presenting to various teachers within our Conference.  My presentation will be focused on the Google Educator Certification Process and everything needed to do well on the exam.  I have included the Presentation on this Blog, so give me any other suggestions you have concerning the exam and it’s process!

Thanks to various individuals who helped me along the way: 
+Patrick Dempsey 
+Alice Keeler 
+Jay Atwood 
+Kyle Pace 



Posted in GAFE

Google My Maps – Student Created Maps

Maps

As a high school social studies teacher, I constantly use different types of maps for all of my classes.  More times than not, these are maps created by other individuals with various amounts of information that may/or may not be pertinent to my classroom.  


After attending the GAFE (Google Apps For Education) KC Summit this summer, the idea of map creation (as opposed to map consumption) became a real reality.  The presentation was done by Stafford Marquardt (Product Manager for Google My Maps) on how to use mymaps.google.com to create interactive maps.  Simply put, this presentation rocked my world in all the right ways!
Title the Map

So how does it work?

Outline an area
First go to mymaps.google.com and start creating maps!  One of the cool features of My Maps is that the file is automatically saved in your Drive so that you can access it like your other Google Docs.  Which means you can also share them just like Google Docs, Sheets, Slides, etc.  I realize that right now I need to provide more details on what to do, but really just messing with My Maps will amaze you. 

Tips/Tricks

Title the shape
Create various different “layers” in order to keep it nice, neat and organized.  If you want students to work on the same project, then have a different layer for each student.  

Use the drawing mechanism to outline states, cities, etc. and then apply a color over the top.  Once the color is applied, you can change the transparency to the desired level.

Provide a color to the shape

When you use the “marker” option, you can include pictures, videos, descriptions of that marker or location.

Insert a marker with various Details
If you do not want to create maps, or have students create maps, Google has provided some pre-built maps.  These maps can be found at both of the following:

MyMaps Gallery

Google Maps Gallery

I have created a short Youtube video (60 seconds) that demonstrates how to download one of the template maps as a KMZ file and then import it into My Maps:
Also provided is the Presentation done by Marquardt that explains and provides examples of other ideas using My Maps:

MyMaps Presentation

Classroom Example:  

Have students draw the path of Lewis and Clark from St. Louis to the Pacific and back.  Break up the students with the job of a specific city on the route to another city (St. Louis to Omaha).  Then have students trace the path, provide images of those location (Past and Present), and include words/images from the Lewis and Clark Journal (Link to Journal) to the spot where it was likely written.

Conclusion

I want to end with the following statement I have used constantly in the last two months. Start by messing with it and do not be afraid to break it.  It normally is easy to fix and you will be shocked how capable you are at creating cool/interactive things!
Posted in GAFE

1st Day Activity

Welcome Back!

Every new year brings with it a lot of new and exciting opportunities for our students.  In the fall of 2015, one of those opportunities for our school included the influx of chromebooks in a few classrooms.  I was blessed with these new devices after working with iPads for the last 4 years.

Thought-Provoking Idea

 

New Ideas

As I began to prepare for this first day, I was challenged by a tweet from +Alice Keeler.  Her tweet challenged the following idea: ‘Who says we need to go over the syllabus the way we have for the last 50 years?  Who says that we need to “talk” over everything the first day?’

With this thought provoking idea, I developed a new idea.  Why not have students create something that was shared out to the world that describes the syllabus, the class, and them personally?  After talking to my principal, +Chris Thompson, and a fellow teacher (My wife), +Amanda Houp, I created an assignment on Google Drawing for the first day.  (Copy of assignment Link)

My Example

Procedure

As students came into my class, I had them grab a chromebook and log into their School Issued Google Apps Account.  Of course a few students had forgotten their passwords, but with me being the Google Apps Administrator it was easy to change their passwords.  Then I directed the students to the Google Classroom site in order to enroll in my “class” (classroom.google.com).  To do this, I displayed the enrollment code on the TV.  Some students logged on, others struggled.  To help alleviate this and get students on the same page, I had the logged in students (experts) help those struggling.  This helped everyone get on the same page much sooner.  Also, I created a basic example that I would turn in if I were a student which gave visual learners an idea of how to approach the assignment.  (Copy of Example)

Google Drawing Template (Copy)

 

Details of Assignment

Google+: Student Work Shared

At this point in the class period, students began to work on the Google Drawing by “making a copy.”  I would use this time period to periodically teach students different tips/tricks with the Chromebook/Google Apps Account.  Overall, students gained the following skills:  Location of my website (full of resources); Use Google Apps/Drive/Drawing; Find Images/Text; definition between public domain; A few details of my class syllabus; Class Direction/Content; etc.

Tips/Tricks:

Here are a few of the shortcuts I learned and passed on to my students:

Ctrl + L-C-V
Created using Google Drawing


Ctrl+L= Highlight URL

Ctrl+C= Copy

Ctrl+V= Paste

On a chromebook, use three fingers on the trackpad to switch quickly between various open tabs within Chrome.

Final Reflection

Are there other ways to do this?  Absolutely!  But overall I was very happy with the amount of preliminary skills students developed.  If you have other ideas or tips, please share for others to learn!

Google Classroom Assignment

 Resources:

My website for all my classes:  Coach Houp’s Website
Alice Keeler’s Website:  Teacher Tech

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