Using Google Forms + Sheets for easy, portable data comparison

Yay blog.

When comparison-shopping, the devil is in the details... and when comparison-shopping for something like a house, you will find yourself drowning in information. Unfortunately, a missed detail about a house can cost you thousands in unexpected fees or repair bills (Oh, I didn't realize the furnace was THAT old!) When touring houses on the market, I needed a "home walk-through wizard" that could let me fill in a lot of data quickly, and compare it later.

Turns out all you need is a Google Form for input, and a Google Sheet with a single formula!

Google Forms is one of the lesser-known Drive apps, which you can use to create simple online surveys for data collection. You can view the results in Forms, or you can auto-export them into a Google sheet... and that's where things get fun.

There are several advantages to using an online form, the biggest being speed and consistency: Instead of furiously scribbling the same notes over and over for each item, you can just thumb through a survey on your phone and tick off check-boxes... and compiled in a way that is easy to compare later. 

Step 1: Create a Google Form for the data you want to compare.

In Google Drive, select New > More > Google Forms to create a new form. The interface is dirt simple- create some "questions" with the features you want to compare, and pick the answer format and the answers that best fit. Keep adding questions until you are satisfied; arrange them in the order you would want to see later in your comparison chart. If you intend to use this on a phone, I would recommend breaking your survey into several pages to avoid excessive scrolling.

NOTE: Since Google Forms is intended for public surveys, your form will always have a "live link" or URL used for filling out the questions- so take the usual privacy precautions when wording your questions and answers.  You can shut off public access by toggling whether you are accepting responses or not... or you can avoid sharing the link to keep the survey semi-private.

Step 2: Link your form to a Google sheet

In the Forms menu, select Responses > Choose Response Destination. This lets you pick a Google sheet to dump your responses into, as you fill out your surveys. Once you set a destination sheet, click the View live form button and fill out a test survey. With a survey filled out, go into Google Drive and open up your responses sheet to see the results. Yes, the format looks ugly- we will fix this later.

Step 3: Turn the dumped data into a vertical comparison chart

By default, the form dumps the data into a sheet called "Form Responses 1", in a horizontal format that's great for robots, but less useful for us. We can fix this with a simple Transpose function. Create another sheet/tab called "Comparison" and paste the following formula into cell A1, with the name of the responses sheet and a range of columns that cover the data you want to see:

=Transpose('Form Responses 1'!A:Z)

The result will be something like this:

Bonus: Conditional formatting for color-coded awesome

The vertical list looks pretty cool, but comparing is even easier if you color-code your answers based on how desirable they are.

In your Comparison sheet, select Format > Conditional Formatting... and enter the keywords or number ranges that you want to color-code on. 

To keep the rules broad and simple, I recommend using the same answer words in your form questions when possible. For example, in my case:

• Low/Great/Good/Yes is always positive in the survey, so green
• Mild/Medium/Fair/Partial is always "meh" in the survey, so yellow
• High/Poor/Bad/No is always a negative answer, so color it red

Having consistent answers also lets us apply the formatting rules to all rows, so we don't have to fuss about which rules apply where.

It may take some work depending on your sheet, but the results are impressive:



A couple of important caveats:

• The form responses sheet is NOT a live "database" for editing your survey responses! Google Forms saves all of your surveys internally, in a read-only format. When you set a response destination, Forms will "auto-export" survey data to a spreadsheet as it is submitted... but it is a one-way copy. If you edit the data in the spreadsheet, Forms will not be aware of it, and the saved surveys will contain the survey results as originally written. Likewise, deleting the responses saved in the form will not change what is already dumped to the sheet.
• Another quirk about Google Forms: it will dump responses to the sheet in the order you first arranged your survey questions. If you go back and change the question order in your form, your spreadsheet will not reflect that change- and any questions you add later will be tacked onto the end of the sheet. So, try to get the questions in the order you like, before you start filling in responses. Or, if you haven't cleared your responses in the form, you can always re-export all of the results by choosing a new response destination.