Welcome to EdListen; Podcast for educators.
Never Stop Learning
Please Join the EdListen G+ Community

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.

Announcing: Lab Scheduler Add-on

Lab Scheduler is a unique resource scheduler designed for schools, that uses periods or blocks to schedule rather than times. This works out well for schools where each day's meeting times change constantly to accommodate, block scheduling, 1/2 days, or assemblies. Schools can set up the Lab Scheduler sheet with their resources that need to be signed out. Then share it with edit rights to their teachers. Teachers then select the resources from the sheet tabs at the bottom and put their name on the specific day and period that they want to sign up. The lab scheduler will continually take out the previous day and add a new one so that once it is set up, it will always be up-to-date.


Announcing: Connected Groups Add-on

Creates shared groups between different Gmail accounts.

Connected Groups add-on uses a spreadsheet as a master list to create shared contact groups.  Each person who is shared is called a Manager, each manager can add or delete names from the list, as well as invite other users to become managers and use the shared contact list.   Any changes to the lists get updated to all the managers over the course of an hour.  Managers can choose to sync or not sync.     One possible scenario is a secretary needs to manage a contact group for a school principal.    The secretary is in charge of keeping the contact list up-to-date.  They can install Connected Groups and updating the list will be as easy as adding a name to a spreadsheet.

To see more of my scripts check out: https://sites.google.com/site/edlistenscripts