Welcome to EdListen:
Never Stop Learning

Announcing the Simply Send add-on.

I would like to announce the Simply Send add-on. This form add-on will simply send/notify the form results to specified email addresses.

1:1 With Chromebooks ~ VT-Fest 2015

The school I work for is now on year 2 of their take-home 1:1 program.  We have over 500 Chromebooks in the hands of all the students and staff.   Here are some things I learned along the way.
Add your comments to a Google Doc version this post:


The year before:

Purchasing and replacement schedule

  • I suggest: Take-home ~ 2 years, In-School ~ 3-4 years
  • Leasing
    • Buyout lease (Total Cost X .53)
    • Fair Market Value (FMV) lease (Total Cost X .45)
      • Pay a portion of the entire cost of the device.  
      • Estimate 15-20% for the ones you want to keep or unable to return.
      • We have no need to keep devices after the new ones arrive so why pay more.
  • What else is needed ~ Bag, management, Spares, Classroom management Software


  • Worth Ave Group (or similar)  vs. Self-insure
    • After 1 year we found that the Worth Ave Group was not worth the cost, and chose to self-insure.
  • What to charge students
    • Pre-charge everyone
      • Found that students did not feel as accountable for the care of the device.
    • Per-incident charge
      • Cost should be a deterrent, not actual cost.   (We did a $25 incident fee)
      • Sense of ownership and need for responsibility.
      • Rewards responsible students, and holds non-responsible students accountable.
      • Lost Chromebooks, or purposely damaged chromebooks, are handled by the administration, and the family could be responsible for the full initial cost of the device.
  • Expect about 15-20% screen breakage


Year 1 & 2


  • Challenge: Get them in the hands of the student in the morning of Day 1.
  • Year 1: Gave them out to 8th period class
  • Year 2: Gave them out during TA
  • Stafford students, were optional
  • Paperwork vs getting them to students is still an issue.

Classroom Management

  • Digital tools
    • Google Classroom (previously Hapara), Omnito, GoGuardian, GAT,
  • Physical change
    • Move desks around so that teachers can see the screens
    • Common terminology: Screens down, Screens up
  • Unprepared students
    • 15 Spares in the library that can be signed out.


Classroom Tools

All School Workflow

  • Administration needs to lead the pack.
    • No paper Faculty meetings
    • Admins need to be using a Chromebook as a primary portable device (Chromebook Flip);
  • We have been replacing aging projectors with TV's and Chromecast
  • Become familiar with add-ons and scripts
    • I personally have written many add-ons that are focused on school workflow: http://scripts.edlisten.com/, Lab Scheduler, CheckItOut, Transfer Ownership, Copy Folder
    • Auto-send form data: Homework-Club, parent notification (AutoCrat,  Simply Send)
    • Custom scripts for distributing Budget sheets, and IEP's to teachers.
  • Chromebooks don't print.  
    • Students can use a designated desktop in the library if they need to print.


  • About 20% screen breakage
  • Incident-fee instead of prepaid insurance
  • We fix and hand back to student.   We complete a google form, which auto-generates an invoice which we mail home.   Unpaid bills get given to the administration to deal with each month.
  • We spend about 1/3rd the cost to fix in-house and just take the loss on dead ones, then when if we went with an insurance of some type.


  • Challenge:  Let students have them through their last exam.
  • We chose to allow students to take Chromebooks home over the summer if they got them pre-inventoried.   About 1/3rd took us up on the offer.
  • Take-home: They had to bring their CB to the IT office sometime during the month of June and get the device inventoried.
  • Leave at school: They had to drop it off at the IT office after the last exam that they needed it for.

Other Links

Digital Signage TPC15 Presentation

Presented at: http://www.tpcvt.com/

We see those signs all over the place, and they seem easy to do, but how much do they cost?  What is the software like?

There are quite a few applications out there, but after playing around with many of them, there are only 2 that I think are worth mentioning.

The software

What I looked for when choosing a display software

  • Free
  • Integrated with Google Apps
  • Easy to use
  • Webbased (can be built while on a Chromebook)

Chrome Si gn Builder App:
Used best when: You have a single TV, and want to just play a Google Slide Show

  • This is a very simple app, that allows you to schedule a webpage (which could be a calendar, or google slideshow), to show up at certain times.  Then you can use the built in Kiosk mode to display that site.
  • Once you are finished, you can export the settings, then upload them to the Google Dashboard.
  • Each TV would be managed individually.
  • Schedule changes require a new config file to be uploaded.

Used best when: You might have more than one TV eventually, and you want to display multiple pieces of content (Calendar, weather, bulletins, YouTube, slideshows), on the screen at once.

  • RiseVision is a full featured digital signage solution.  Their platform is free, but will charge for some Gadgets or file storage. 
  •  This can handle multiple TV's, schedules, and presentations under one interface.   You can have multiple users with different levels of rights.
  • It can be set up to pull data from Google Documents, and calendars so that the end-users don't even need to log in.
  • Gadgets can be added on to show weather, or highly customized displays that pull data directly from a Google Spreadsheet.
  • Fair Haven Union High School chose to go with RiseVision, after testing several other solutions. 

The Hardware
In both of the apps, the signage is a Chrome App, which can be set to run as a kiosk in ChromeOS.

What hardware you need:

  • TV display (with hdmi)
  • ChromeOS device, (Chromebox, or ChromeBit)

What you need to do:

  • Set up Kiosk mode on a Chromebook: https://support.google.com/chromebook/answer/3134673?hl=en

How To: See New Google Form Results At The Top

Google Forms is great at adding data to a spreadsheet, but often all you want to see is the new results and that could be several hundred rows down.   Instead of scrolling down all the way down here is quick formula you can use to create a sheet that always shows the data at the top.

  1. In your form results create a new Sheet.
  2. In cell A1 enter this formula:
    1. =query('Form Responses 1'!1:100002, "Select * order by A desc",1)


  • =query() is a function built into google: more info
  • 'Form Responses 1'!1:100002 is the sheet name and range of the form data
  • Select * tells the query to select all the columns, This can be changed to Select A,B,C format also
  • order by A desc tells the query to reverse the order based on column A
  • ,1 tells the query to include the headings.

Also check out:
  • Also check out the EZ Query add-on.   This gives you a graphical interface to create simple queries.  At the time of this blog post "order by A desc" is not an option but it is on my road map to include in the future. 

Edu on Air: Creating a culture: Google Apps add-on slam

Edu On Air 2015 event page: https://plus.google.com/u/0/events/c7cndlfsplecdcjfqgf5as1rj3c

My school went 1:1 this year which meant a whirlwind of change. Creating this new culture meant creating or adapting school workflows. I'll showcase the add-ons that have helped me like autoCrat for automated Mail Merges, Choice Eliminator for parent teacher conferences, CheckItOut and Lab Scheduler.  Also my newest add-on EZ Query.

This session is part of Education on Air, the free online conference from Google. Register for free at http://goo.gl/Gkww6N.

The journey of creating a culture.
  1. Get everybody on board, starting with the office staff and administration
  2. Change common workflows
  3. Give teachers time to start using it in the classroom. 

Workflows that I have changed and the tools I used to help me do it.

Author Links:

Announcing EZ Query Add-on

EZ Query will help make spreadsheets and form data easily readable.  The Add-on utilizes the built-in query function, so data is always up to date.  With this add-on you can easily select the columns to show and add filters so you only see want.

Get unique values from a column in Google Sheets, without spaces

Example spreadsheet.

Column B is: =UNIQUE(A:A)
Column C is: =ARRAYFORMULA(if(B:B="","",countif(A:A,B:B)))

Unique ~ pulls the unique values form the submissions. You can combine this inside a sort and at least variations of languages will show up close to each other.

ARRAYFORMULA ~ is a magic function that allows you to apply a formulat that is normally set for a single line to an entire dataset (aka row).

if(B:B="","", ~ is something I found on the internet that allows ArrayFormula to not process blank spaces.

If you are looking for unique matches in a data set that might contain variations of terms then I suggest the Sheet Add-on: Fuzzy Matches

=sort(UNIQUE(C2:C)) also works for grabbing a column and pulling just the unique values without spaces.

Google App Script Lesson for Students: Change Background based on Word Count.

Inspired by a request from +Mark Phillips.   Feel free to use this or a variation of this with your students.   Please do not republish a public version that includes my "Working Example" without my permission.


  • Have the class work together to create a Google Document App Script that will automatically change the background color of the document if it reaches a certain number of words. 

Working Example: Word Limit Notifier

  • If you would like to see the full code, then create a copy, and go to Tools => Script Editor.
  • There is a Group Tasks file that includes completed examples of each groups scripts. 
  • Learning how to code is best done by looking at other examples.  However there is always a thin line between looking at other's code to learn and taking it all-to-gather.   I suggest leaving it up on the teachers machine for students to reference.   Just extrapolating the information that is pertinent to them is an important skill in coding. 


  • As a Group create a function.
    • function getCharacterLimit(){
      var labelTxt = "The length: ";
      var ad = DocumentApp.getActiveDocument();
        var text = ad.getBody().getText();
        var lenghth = text.length;
  • Split the class into groups; each group is responsible for their section of the script.
  • Sections / Groups
    • User Interface: This group is responsible for creating the menu, dialogues and prompts.  The information they capture will need to be saved as a variable.
      • Prompt: "Set Count"  ~ log the result
      • Prompt: "Set bgColor" ~ log the result
      • Run: "Force Check"
      • Alert: "Current Count" ~ Multiline alert that will eventually include "Max Allowed Words:",  "Background Color:", "Word Count:".
      • Take it further: Use HtmlService instead of getUi to create the dialogue.
      • Documentation
    • Save hidden information: This group will be charged with learning how to save variable information using the PropertiesService so that it can be used the next time the document is opened.
      • Save the Set Count
      • Save the Set bgColor
      • Retrieve the Set Count
      • Retrieve the bgColor
      • Take it further: Learn to save information as an object using JSON.stringify() and JSON.parse().
      • Properties Service Documentation
    • Triggers:  Triggers tells the script to do something at a particular time or interval. In the scripts case it will be checking the word count and if it is above the set count then it will change the background.
      • Set a trigger to run a function that adds to the log every minute: (1 minute is the minimum amount of time a trigger can launch consecutively. 
      • Check to see if the trigger has already been installed.
      • Find out at what point the trigger should be installed in the completed script.
      • Take it further: Set date triggers, and learn about simple vs programmed triggers.
      • Trigger Documentation
    • Get the Word Count & Change the Background Color:  There is no program key that gives you the word count, so to complete this you will need to think of ways to accomplish getting at least a rough estimate of how many words.  Secondly you will want to be able to change the background color of the document.
      • Get the current word count and write it to the log
      • Take it further: Capture other information like character count or how many times the word "The" appears and see if you can log that information also.
      • Write a function that will change the background color.
      • Take it further: Find other attributes that you can change.
      • Documentation
  • Bring it together: 
    • In a class discussion format, or individually as homework have the students take all the different parts and put them together into a single script.
    • Take it further: Add in checks to prevent user error.
    • Take it further: Improve the user interface to make it simpler for the end user.

Glossary Of Terms:
  • Function: A section of code that can be run by itself.  
  • Variable: A word or letter that information gets stored to so that it can be accessed within a function.
  • Trigger: An event that fires at a particular time or interval.
  • String: Information stored as plain text. (var aString = "";)
  • Object: Used for storing information in a complex format. (var someObject = {};)
  • Array: This is a group of information that can be accessed one after another. (var anArray = [];)
  • API: The different commands that can be run to build your script.
  • Comment: A section of the code that does not run.
  • Log: A place to store information while you are developing. 

Import things to know:
  • Each link of code ends with a semi-colon.  A line of code may take up severs lines on the document, but when it is read by the application, it looks for the semi-colon to know when it actually ends.
  • Functions have a format of:   function functionName(){  code goes here }
    • Variables can be passed between functions, by placing them inside the ( )'s.
  • Periods are used to add a sub-API event to the root API.  DocumentApp.getUi();
  • Save and test often.
  • Most of App Scripting is based off of Javascript.

Important Links:
  • API reference ~ Primary documentation that lists of all the commands that can be run (Also found in the Help menu of the script)
  • StackOverFlow.com ~ Scripting forum used to get or provide help with scripts. 
  • Issue Tracker ~ Used to list any bugs found in the API's.