Tuesday, September 19, 2017

Tutorial: Proper Filtering and Sorting using Filter Views

Data often gets corrupted when people improperly sort information so they can group items together. Discover how Filters and Filtered Views can temporarily hide and sort your data without destroying its integrity .

Wednesday, August 16, 2017

Power Searching Google Drive

Google's superpower is search and to really narrow down on what you're looking for, it's important to be familiar with the advance search function.

Google indexes any file stored in Google Drive. Not only can you search for words in the title, but within the document itself. Using the advance search, you can pinpoint your results to a specific file type, owner or date.

By combining the URL Google Drive uses to display your search results with Google Chrome's search engine function, you can start a search from any page. In other words, you don't have to go to Google Drive first to enter your query.

Discover how easy it is to create custom search engines to search Google Drive.

Tuesday, May 23, 2017

Tutorial - Create reports using the Query Function

Having Google Forms enter your information directly into a spreadsheet is a great time saver. And once it's there, you can easily filter and sort your data. However, too many times people will mess up their data when they only sort some of their columns - making the data invalid and useless.

Instead of manually sorting, you can use the QUERY function to do all the work for you, while automatically updating your data as new information comes in. Check out the video below on the details:

Monday, May 22, 2017

Import Data from One Spreadsheet to Another

With every new intake of trainers, the question always comes up about how to move data from one spreadsheet to another. For example, I have a sheet with information on grade 7, 8, 9 students in a master list, but I only want the Grade 7 student list to be available to the Grade 7 teachers.

ImportRange is one of the amazing functions available to us in Google Sheets. Think of it as a live import - always updating itself as the master sheet gets updated. You can't make any changes to the data (i.e., read-only), but you can still apply filters to the data.

Here's a video on how to use the ImportRange function to setup this process:

Monday, December 12, 2016

Hate the formatting in Google Groups?

One thing in desperate need of an update is the user interface in Google Groups for composing messages. However, a great solution is using Google Docs! Just draft your message and then copy/paste to Google Groups. Just be sure to use a standard font supported by Google Groups.

You can do the same with Gmail or use this handy Chrome extension:

Friday, June 3, 2016

No Projector in Your Meeting Room? Use Google Hangouts Screenshare!

Recently our Web Services department met to review web pages and some of the development work that was going on. This usually involves one of our designers sharing their screen on the large monitor or project. Unfortunately, this meeting room didn't have anything and people started spinning their laptop screens back and forth to show their work.

As I was on the other side of the table and didn't want to be walking back and forth, I realized we could do this just as easy with a Hangout Video Call! I quickly got everyone setup (Tip: Use Google Chrome to avoid needing to downloading a plugin), gave them the name of the Hangout "room" (feature only available to Google Apps for Education/Work) and had everyone start screen sharing. Be sure to mute microphones and cameras.

Although everyone is screen sharing, it won't necessarily show up on everyone's screen until someone selects "Present to Everyone".

Here's the trick: once you're done, just stop presenting (but not screen sharing)! It does 2 important things

  • allows the next person to present to the group
  • still keeps you screen sharing. This means you don't have to go back in and choose which screen/window to share, and you can quickly start presenting again

A couple of caveats:

  • if someone pins a user (i.e. clicks on the icon of a user in the filmstrip) that will override the presenter window
  • when you're still sharing, someone can pin your screen and still see what you're doing
Remember, with Hangouts you can have up to 25 people participating - and don't forget to take advantage of the built-in chat in your Hangout to pass URLs back and forth.

Monday, May 9, 2016

Fort McMurray Wildfire Evacuees Housing - using Google Apps.

As one of the many universities in Edmonton, MacEwan University was tasked with using available space in our student residence to house displaced families from Fort McMurray (at one point it was estimated that 60,000 would be making their way to/through Edmonton). To help organize some of our efforts, we developed 3 forms and a spreadsheet. Although we are not the largest institution, our staff took in over 1000 people in a single night. The staff was more concerned with getting families who had been on the road for over 48 hours into rooms as fast as possible, so the process of collecting information was not as exact as normal. 

Census Forms

The next day it was important to get an accurate number of the people and age groups in the building. Many people who initially registered with us had either invited others in or had given up our rooms. Rooms intended for a maximum of 6 people were housing 8 or 10. The plan was to go door to door to collect numbers and names of people. Understanding the evacuees were still tired and stressed, we decided to create 2 Google Forms to keep our interactions at a minimum.

The Short Census form would allow us to get an idea of the number of adults and kids. This information could be passed on to the different support services (e.g., food and clothing). The Long Census form gave us more detailed information about names and contact information which could be entered into a residence management system. Because typing on an iPad is rarely fast, and spelling of names/emails would be a challenge, we decided to let the evacuees fill this out themselves (most people had a smart phone). After collecting the Short Census information, volunteers gave evacuees a piece of paper with a link to the Long Census along with Red Cross information. 

Short Census Data

  • Room number (text field)
  • Name on file confirmed (Yes/No)
  • Name if not person on file (text field)
  • Number of Adults (1 - 10 Multiple choice with Other)
  • Number of kids 11 years and older (1 - 10 Multiple choice with Other)
  • Number of kids 10 years and under (1 - 10 Multiple choice with Other)
  • Registred at Red Cross (Yes/No) 

Long Census Data

  • Room Number (text field)
  • Names of all guests (paragraph)
  • Email address (text field with validation)
  • Cell phone (text field with validation)

Volunteer Form

I saw a lot of emails messages asking for volunteers before I was invited in on the project. Once we got the Google Form up and running, it was a lot easier to collect the information. As people indicated the shifts they would be available, the coordinator review and assign them to shifts in the spreadsheet, and use formMule to send our confirmation messages. Restaurant Lists

Edmonton has been known as the City of Champions for a number of years and one of the big reasons is the support that comes out during times of crises. Restaurants throughout the city are providing free meals for displaced families. To assist us in keeping our board up to date, we created a spreadsheet of the local (i.e. walking distance) restaurants. This provides us with a single list that we can use everyday to call and confirm the offers are still available and ensure we only call them once (multiple people were using the list). 

Take Aways/Tips

In web design there’s a saying: “Don’t make them think.” Although we could create all sorts of beautifully designed forms and spreadsheets, it’s important to remember that “Done is better than perfect or pretty” and everyone is already under enough stress.. So here are a few things you should consider: 

  • With Google Forms, use Multiple Choice fields instead of Lists. Yes, all the choices are displayed on the page and take up a lot of room, but when filling out on a mobile device the user just has to move down the page (i.e., push up) to see the choices. 
  • Use a larger font. Not pretty, but easier to read on the screen and easier to choose items from lists
  • Don’t expect add-ons for Google Apps to always work properly. Your users should be prepared to process things manually should something go wrong. This is extremely important with non-technical users.
  • Don’t force Google Apps solutions on people. Yes, organizing spreadsheets is much easier than using paper and pen for managing lists, but these people are under enough pressure. If it’s working and getting the job done, let it go. You can always circle back to it during the debriefing sessions. 

Last Note

Many of these people have not only lost their homes, but potentially their source of employment. If you’d like to help out, please donate to the Red Cross by visiting