Do you create Google Sheets? In our office, many use Google Sheets for a multitude of projects. At a recent Google Educator Level 1 training, participants were unaware of my top five hacks for Google Sheets. Let's take a look at five Google Sheets hacks you may find useful. Depending on your needs, these can make your Sheets experience more productive.
Hack #1 - Flippity.net"Have you explored Flippity.net?" I asked session participants. Almost all shook their heads in the negative. Flippity takes data you have in your Google Sheet and then does cool stuff with it.
Note: With each Sheet, you will must go to File->Publish the sheet. This make its available on the web for viewing. When you get it setup, you will copy the Flippity.net website address (URL) (shown in blue in the image below) then share that with students or staff.
Here are some of the problems that the free Flippity website helps you solve as an educator:
- Flashcards - Take content in a Google Sheet and turn it into online flashcards. You create two columns in a Sheet, putting side one (the prompt) of the flashcard in column one. In column two, you put side two content (the response). With this option, you can also generate more resources. Those include a simple list, practice, matching, and word cloud. Flippity makes it easy to send your list of data to create Bingo cards, Crossword, and even Hangman. It can generate a Memory Game, Word Search, and Print Quiz.
- Quiz Show - Want to create your own trivia game a la a Jeopardy show? Flippity makes that easy.
- Random Name Picker - Need a random name picker for selecting students for projects? This tool will use your class roster to make it happen.
- Scavenger Hunt - Turn any Sheet into a scavenger hunt activity.
More creations that Flippity makes possible include Timeline maker tool. You can use its Badge Tracker, Spelling Word Manager, and Word Search tools, too. MadLibs, Mix and Match, and Certificate Quiz appear, too. Ready to get started? Go to Flippity's website and follow the instructions that go with each tool.
Hack #2 - RosterSyncThis free add-on to Google Sheets makes it easy to create and update Google Classroom. With RosterSync Teacher Edition, you can sync a copy of a Google Classroom course roster. Student names and email addresses get saved to Sheets. Why is this important? It makes getting at data saved in Classrooms easier to use elsewhere. Once you have that class roster of students, you can use other add-ons (like Flippity) to do more.
Hack #3 - Save Email and AttachmentsGet a lot of emails with attached files? Use this add-on to Google Sheets to create a custom filter. This add-on will save file attachments from selected incoming email as PDF files. You can create a "rule" that has two options. The options include save the email body (the contents) and file attachments. Here is what one query looks like:
label:All from:SOMEONE@tcea.org has:attachmentMore options are available in the premium version ($29 per year). Those options include:
- Unlimited rules for saving emails and attachments to:
- Google Drive
- Google Team Drive folders
- Faster save rate (every 15 minutes), which is great if you have tons of incoming email
- Custom file names
- Permit or deny file attachments that have specific extensions (e.g. EXEs to avoid potential virus carriers)
- Remove watermark in generated PDF files
Hack #4 - Power ToolsFeature-rich, this add-on offers rich variety of tools you can use to manage your data in a spreadsheet. If you have three minutes, you may want to watch this video overview. Some of the tools in this must-have tool for Sheets include:
- Change case (e.g. capital, lowercase)
- Sum colored cells
- Remove duplicates and compare two or more columns
- Split, merge, compare, find data
- Unpivot tables, unmerge cells
- Autosum numbers in every row or column
- Add or remove text by position
- Transform formulas
- Change formula reference types
- Delete unwanted characters
- Merge sheets
- Randomize data
Hack #5 - Bulk Email ToolsHave you collected students' parent names and emails via a Google Form? Make it easy to stay in contact with custom updates to parents. Send a clickable email (instead of paper) that parents will be able to interact with. Need to send a standard email to workshop participants? You can do this with one or both of these two bulk email tools:
- Yet Another Mail Merge (YAMM) (Watch video) - YAMM comes with 50 free emails a day that you can send out and a variety of video tutorials. Create a draft email in Gmail and then send it to everyone listed in a Google Sheet.
- FormMule (Watch video) - A powerful bulk email tool, you can set up ten different email templates.
- Parents with student-specific information that is not FERPA related
- Students (in older grades) with specific non-FERPA related information, including assignment reminders
- Colleagues and staff about upcoming or past events that include links to more information