Combining multiple Google Forms assessments using Google Sheets

A fellow Google Certified Trainer asked the following:

“A teacher I am working with has created module tests in Google Forms. The forms are all linked to one Google Sheet which creates a different tab for each module test. She wants to be able to track progress for everyone on one tab. Ideally, she would like to be able to see how many module tests each student has completed and what their scores were to see a summary of each student’s progress without having to look in each tab (i.e. Johnny: module 1 75%, module 2 90%, etc). Is there a way to do this?”

Formulas you will need

For this solution (get your copy of the Spreadsheet here) I utilized the formulas ARRAYFORMULA(), if(), Query(), Sort() and Unique(). The use of {;} was used to combine the data from the multiple module tabs.

Steps to creating this solution

First of all I made two fake forms that feed into two module tabs. Each uses an if() statement wrapped by an arrayformula() [ =ARRAYFORMULA(if(A2:A<>””,1,””) for module 1 and =ARRAYFORMULA(if(A2:A<>””,2,””) for module 2 ] to automatically check if column A has a value, and if it does, place the module number in that column (I figured the assessment may not automatically say what module it is from).

Module 1 Tab
Module 2 Tab

The on a new Tab I use a combining statement { array1;array2} wrapped by a unique() statement and sort() statement to create a sorted list of the auto-collected email addresses which will allow you to sort by student in a later step. The formula used is =sort(unique({Module1!B2:B;Module2!B2:B})).

Combined student email list

In the 4th Tab I combined the two queries together using the statement {query(Module1!A2:G,”Select A,B,F,G”,0);query(Module2!A2:G,”Select A,B, E, F “,0)}. Additional modules would be added by insert insert ” ;array3″. You must be careful to make sure that the queries pull in the same values. I have pulled Timestamp, Email, Score and Module, and these are not necessarily in the same columns on the different assessment tabs. I also did not include the headers in the queries, they were manually inserted.

Combining Modules into one list

Suggestion: At this point I would suggest using my Google Sheet add-on “Student Name Import” which uses your Google Classroom People and inserts a new column with the students name. Great for schools that use the “fl1234@school.org” style email address. Click here to learn more.

The last step is to create a query page with a drop down menu. First, use Data Validation to make a drop down list of the students emails (or you could use the names if you utilize Student Name Import). Next, create your header row that will be above your query. Finally, use a query to pull the information from the combined report, but restrict the list to the selected student. The formula use in this solution is =query(‘Combined Modules’!A4:D, “Select A, B, C, D where B = ‘”&B1&”‘ “,0) which pulls Columns A, B, C and D only where B is equal to the value in the drop-down list. Typically you might use a query with “where B = ‘tom@abc.com’ “, the trick is to replace ‘tom@abc.com’ with ‘”&B1&”‘ (B1 represent the location for the drop-down list) allowing you to create a single query that isolates based on the student.

Google Sheets is amazingly powerful. I use these techniques for many of the resources on my website. Takes a little time to learn them, but you will truly benefit by learning these skills.

Side-by-Side Translate – Newsletter

Teachers often need to communicate home with parents, but do you communicate in a way that parents can read? In this post I am sharing how to use Side-by-Side Translate to make your Classroom Newsletter more accessible to parents.

Creating a newsletter in Google Docs is easy. The example below started with on of the newsletter templates that are built in. I did change the margins to 0.3 in order to give lots of space for the side-by-sdie translations

Using Side-by-Side Translate you can easily transform any newsletter. The example below in translated into Arabic. We have many families in our district that do not have mastery of English, so this would be better suited to reach out to them. Once you have completed the translation, feel free to make any edits to the format that would improve the look of your newsletter.

If you are interested in Side-by-Side Translate or want to learn more click here.

2018 CAASPP Predominance Maps

As another one of my pet projects I like to learn how to use ArcGIS to visualize data. These maps I created tell the story of the predominance of the achievement levels on the 2018 CAASPP test for grade 3-8 and 11 across Fresno County schools and Statewide. A predominance map uses colors and transparency to represent the predominant group (more intense colors indicate a greater distance from the next most populous group), and size to represents the total population. For this map “Standard Exceeded” is represented by blue, “Standard Met” by green, “Standard Nearly Met” by orange, and “Standard Not Met” by red. Click on any location to see the school name, address and achievement data for that grade level and subject.

The 3rd grade math layer is on by default and shows the wide diversity of the predominant achievement level for this grade and subject. Take a moment and look at your region and turn on and off the various layers. I recommend only having one layer on at a time. The percent based data has been extracted from http://www3.cde.ca.gov/caasppresearchfiles/2018/sb/sb_ca2018_1_csv_v3.zip and for these maps I have programmatically re-calculated them to student counts in lieu of percentages. Data therefor might not be a perfect match, but should be accurate enough to make you think of some questions.

Have a great time checking out these resources. Make sure to scroll down to access the statewide version as well.
Jeran



Fundraiser Tracking Sheet/Display

This is an example of a fundraiser chart that is embedded into a webpage.  You can modify the amounts in the yellow donations section in the Google Sheet below.  This in turn will change the total in the green section, which will be reflected in the Embedded Chart below.  The general directions for modifying the Display tab URL and embedding the iFrame can be found in the orange section.  Feel free to make a copy and check out how it all works.

Have fun, Jeran

2018 Custom CA School Dashboard

Custom County View 2018 CA School Dashboard

One of my pet coding projects has been to create a dashboard that is useful at the county level.  Over the past few years I have developed my coding skills allowing me the ability to quickly and accurately pull the data points for any CDS code, and to display those results in a different ways.  Over winter break I have recoded my tool to use the new www.caschooldashboard.org and pulled together this custom Fresno County dashboard that I feel will be of benefit to many viewers. 

Please note: The same dashboard is used by all, so your setting choices will change as others also use the tool.  Be kind and share this new resource.  If necessary, come back at a different time if you can see someone actively in use of the tool.  The tool will occasionally reset (once an hour) to show all data points again.  Enjoy exploring the data for Fresno County school districts, you might see something new!!!  Instructions are located below the tool.

  District Level / Special Dashboards 

Instructions:

There are 3 tabs with editable sections (identified by a cream colored cell).  Below are the instructions for each tab.

School_IDs tab

Allows the user to choose a customized list of  districts.  Simply place an “X” in the districts you would like to be included in the District_Data tab where you will choose the “Custom List” option.

District_Data tab

Allows the user to explore the counties district(s) data with a variety of data filters.  At the top are 4 drop down menus listing the options.  By default they are as follows:

  • Cell A1 allow the user to choose “All Districts”, “Custom List” (created in the Schools_IDs tab), or any individual district.
  • Cell B1 allows the user to filter into view “All Indicators”, or one indicator at a time (“Chron Abs”, “Susp Rate”, “ELP”, “Grad Rate”, “CCI”, “ELA” and “Math”) .  This list has two additional indicators, “ELA-sub” and “Math-sub”.  These indicators includes the ELO (English Learners Only students), RFP (Reclassified Fluent English Proficient students) and EO (English Only students).  Note: The Performance Level is not typically included for these sub-groups, by the have been calculated and programmatically added using the Status and Change levels and the standard ELA/Math 5×5 grid placement.
  • Cell C1 allows the user to filter into view “All Sub-Groups”, or on sub-group at a time using acronyms (ie. HI = Hispanic sub0group).  This list additionally has the “All EL data” which allows the user to view the ELO and RFO subgroups (EO is not included in this option, but can be view separately).
  • Cell D1 should be set to “Significant Only” so that only significant sub-group data is shown.  The user can select to view “Non-Sig. Only” allowing the user to view sub-group data that does not have enough students to be counted as significant.

Below is an example selecting “All Districts”, “ELA”, “All EL Data”, and “Significant Only”.  Columns J, M, N shows 3yrs of Performance Level data with indicator arrows showing increase (➚), same (➙), and decrease (➘). Colors also mirror the standard dashboard gauge (1=red, 2=orange, 3-yellow, 4=green, 5=blue).

Additionally there are 3 regions with additional information on the currently filtered dataset:

  • The blue “Indicator Movement” section shows counts and percentages for current your performance level changes (also includes gain, losing and no significance)
  • The purple “Indicator Counts”  section shows counts and percentages for current your performance level values
  • The green “Filtered Summary 2018” when used at selected indicator and sub-group levels will count the number of filtered student, provide and average indicator level, and calculated a weighted average indicator level based on student enrollment in each district.  In the example, there are 30,053 EL students, average performance level of 2.6, with a weighted average of 2.8 (likely due to high population of EL students in 1 district that earned a performance level of 3).

5×5 Grid tab

Allows the user to see the results of the District_Data tabs filtering in the states 5×5 grid with customized data.

  • Cell B2 allows the user to select and available Indicator based on the filter from the District_Data tab.

Below is an example of the 5×5 grid showing “All EL data” filtered students and their Suspension Rate data across the county.  Each data point shows the status, 17-18 performance level change (the arrows: ), name of school district, sub-group, and number of included students in the sub-group.

CalFire Daily Maps

Last summer I was at Google’s GEO Institute and there were a lot of fires going on in California.  I was a firefighter for 5+ years and remember looking at the fire maps and the growth of a fire over time.  With CalFires use of Google MyMaps the history of fires is now missing.  So, as a solution I decided to automate a daily backup (10am and 10pm) of the current CalFire fire map to my Google Drive.  The daily backup script has been goin on for almost a full year, so I think it is reliable enough to share them.  Plus, it is fire season again and you can check out the growth of a fire somewhere across the state.

When you click on any of the URLs you will be taken to the Google Drive view of the file (see below).

Whats Next

You will have more control if you download the file and load the kmz file into either Google MyMaps or Google Earth for Chrome.  Check back in the coming weeks for more information on how to import kml/kmz files into Google MyMaps and Google Earth.

Example of a fire perimeter in My Maps

Example of a fire perimeter in Google Earth

MyMaps Combiner

Backstory

Back in March 2017 I was working with a math teacher using the Very Large Array (see map)in New Mexico to study Area using maps.  The students explored the VLA to discover things like the size of each satellite dish, the distance between each dish, and to measure the farthest distance that a dish could be send down each of the 3 sets of tracks.  The students had lots of fun exploring the VLA and using the various tools in Google MyMaps.

Problem

We worked with 3 classes that day, creating nearly 100 student MyMaps.  That was when I realized there was a problem, the teacher would have need to look through each of these maps to measure students understanding of the tools and underlying mathematical principals.  Each MyMap to around 30 seconds to load and then some additional time to explore.  If the teacher took a combined total of 1 minute for each student they would need over 1 1-/2 hours to look at these maps.  Reality is, no teacher has that time.

Solution

To save teacher the time necessary to explore a classroom worth of MyMaps, I have developed the MyMaps Combiner (available at jeranott.com/mymapscombiner).  This tool assumes that you are collecting MyMaps into a single folder (for example using a Google Classroom assignment to collect the students MyMaps).  After choosing to make a copy of the file

You will be greeted by the OTT-o-matic MyMaps Combiner tool!

Which also added the “OTT-o-matic Functions” menu to the menubar

Step 1

From the OTT-o-matic Functions menu choose “Step 1) Select Drive Folder with MyMaps”.  You will select the folder from your Google Drive that contains the MyMaps you would like to combine.  The first time you run this you will be asked for permission to run this script.

  and then  

You will then be prompted to select the folder

  and then  

Select the folder that contains your MyMaps

The script will collect the MyMaps information contained within that folder and will add them to the Spreadsheet.  You will get a summary of that was done.

Step 2)

Caution: You must own the Google MyMaps for this part of the process.
Have your students submit the assignment, or individually set you as the owner of the file.

From the OTT-o-matic Functions menu choose “Step 2) Create MyMaps combine KML file”. This will take a few moments to combine all of the MyMap information into one KML (Keyhole Markup Language) file that can saved and opened into Google Earth.  The file will be named after the folder you selected and will end in kml.  This file will be located in your Google Drive and should automatically be placed in the selected MyMaps folder (based on permissions).

Step 3)

Using Google Earth Pro or Google Earth For Chrome open the KML file.  The combined KML will load into Google Earth as a single map, with each MyMap being contained in a subfolder.  From here you can turn on and off layers and explore your students MyMaps.

Step 4)

When you want to refresh your view in Google Earth of the MyMaps, you can simply reload the combined KML file.  This file uses network linking to access each MyMaps, so when you reload the combined KML file, new data is pulled from each map.  If you need additional MyMaps to be included, re-run steps 1 and 2 to create a new combine KML file.

÷ ​SPECIAL CHARACTERS ON CHROMEBOOKS

We have had a question about entering special characters like the division symbol on a Chromebook.  Of course you can use “/”, but what about a traditional division symbol ( ÷ ).  Here are the steps necessary to use Unicode to add special characters like “​÷”.
  1. To start please have a document or text box selected with the cursor indicating that you are ready to enter text
  2. 0) Place your cursor in the location you want the special character
  3. 1) Press ctrl-shift-u all together (typically hold control and shift keys down and then press the “u” key)
  4. 2) You should now see and underlined “u” (ie. u)
  5. 3) After you have identified your Unicode for the special character, type that following the u.
  6.      division symbol unicode is “u00f7” or “uf7”
  7. 4) Once you hit the space or enter key, the text will be replaced by the special character

​5) Repeat as needed for other special characters​


Image provided by Students of Mr. Rupe’s 5th grade class
​Note: If a character does not load, then the particular program you are using may not like this character.  Try again and verify using steps 1-4.

Unicode Resources: