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 .

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:

Using Google Forms to Populate Users in a Domain

Quickly bulk load users to for created Google accounts on your domain, this tutorial show you how to use Google Forms to collect the names and automatically create emails and passwords.

Similar to the SPLIT function, you can quickly split your data into different columns right after you paste in your text or do it to existing data by going Data > Split text to columns.

Read more about it: http://goo.gl/PelVU0

Get New Spreadsheet Functionality from Your Old Google Sheet Data

In the old version of Google Sheets, if you filtered your data, it would change the view for everyone who was accessing the document. Confusing and frustrating, as your content kept switching on you!

One of the advantages of using the new version of Google Sheets is Filter Views. This tool allows you to

• filter your information by field content
• create pre-set views that you can share with others.

Best part of all, it would not affect the view of others!

Video credit: +Google Gooru (http://googlegooru.com)

Using new sheets with your old data

If you have already existing data in the old version of Google Sheets, you don't have to re-create your spreadsheets in the new version (I'm running some Google Apps Scripts that only work in the old version). If all I want to do is manipulate the date for different reports, I can use the ImportRange function to bring the existing data from the old sheets into the new sheets:

=IMPORTRANGE("spreadsheet_key", "range_string")

I just enter the spreadsheet ID number from the old sheet's URL, as well as the sheet name and range of data I want to bring in.

=IMPORTRANGE("1R0nUpLMVmOkygrdo0Hdz", "StudentQueries!A1:J10000")

The content from the old sheet now appears in my new sheet. As this is a form that is constantly being added to, I made sure the row number is quite large. With the document open the data may take some time to refresh if the original sheet is being used for a form. Just refresh to get the most up to date information.

How do you use ImportRange?

Video credit to +Google Gooru

Import Data from a Web Site Into Your Spreadsheet and Filter the Results

Quite often people will paste data from a site into their spreadsheet (think of game standings, lottery numbers, etc). The importHTML function in Google Sheets can automatically get the latest data from a page and insert it into your spreadsheet, where you can treat it as if it was text you pasted in

Once your content is in your spreadsheet, you can treat it as if it was text you pasted in. You can use it in formulas, sort it, and filter it.

Create a Merge Newsletter with Google Docs (Just in time for Christmas)

Many people enjoy sending out a year in review Christmas letter. One of the things I've always hated about them is that because they're so generic, they feel impersonal.

There are many App Scripts available that can add features and tools to Google Docs. Autocrat allows you to merge data in a spreadsheet with a Google Doc and save the merged files as a Google Document or a PDF. It will even email a copy of the document to individuals.

Now let's take the concept of the newsletter and expand it. In addition to adding the person's name at the top, let's customize the first and last paragraph (the middle will be the generic newsletter content). If I'm writing to my brother Bob, my first paragraph will be a personal message to him (eg: Great seeing you this summer - thanks for inviting us to your cabin). The last paragraph is also customized for him (eg: Looking forward to seeing you at Christmas - your room is ready for you!).

After you've gone through and created all this content in your spreadsheet (or if it's easier for you, use a form to enter your info), you can use Autocrat to create the merged newsletters. You can print them off or you can let Autocrat email them out.

Set background colour of spreadsheet cells using conditional formatting

I'll often use Google's spreadsheets as a sort of checklist - the collaboration function is great when you're working with others. However, looking at a long list of items and trying to see what has been completed vs what needs to done can become a task in itself. That's why I like to use conditional formatting to change the background colour of cells - helps me determine where to focus my energies.

Let's pretend I'm building a house and I have the framing all in place. I'm trying to co-ordinate 2 trades: electrical and drywall. Obviously, the electrical needs to be in before the drywall goes up. Once those two tasks are done, I can go in and paint.

As each trade is finished a room, I ask them to place a Y (Yes) in their column. Once both columns show green, I know the room's ready for me to go in and paint. This spreadsheet will also let the drywaller know when he can go into a room and do his work. Here are the steps you need to do this:

1. Select the cells you want the conditional formatting to run on.
2. Select Conditional formatting from the Format menu.
3. Select the condition you want met (in this case, text contains Y).
4. Select the colour of the text/background you want when this condition is met.
5. Save the rules.

As I like to visualize where the holdups are, I add an additional rule that makes the cell red if empty.

Check out the spreadsheet in action!