Home » Blog » Intro to Awesome Tables 2: Filterable Senator List with Pictures

Intro to Awesome Tables 2: Filterable Senator List with Pictures

In this example we will play with our list of US Senators so that it has a number of filter options and displays their picture in the row. All the data will be managed through our Google Spreadsheet, when the spreadsheet is updated so too will the list, no matter where it is online! You’ll need a spreadsheet with data already setup, which we did in Part 1: Setting up your Awesome Table.

This part 2 of a four part series where we learn about Awesome Tables:

Here’s what we are going to build, try searching for the name of your senator:


Pretty cool right? Lets get started.

Outline

This is the tutorial outline, details for each step are below.

  • Setup your awesome Table (Part 1: Setting up your Awesome Table)
  • Configure filters & parameters
    • Filters & parameters are added to the 2nd row of a column
    • Review available filters
    • Add filters to data
      • State: CategoryFilter
      • Class: CategoryFilter
      • Name: StringFilter
      • Party: CategoryFilter
      • Born: DateFilter
      • Seat Up: CategoryFilter
    • Review available parameters
    • Apply parameters
    • Picture: Hidden
    • Multiple filters and parameters can be added by adding a dash (with a space on either side) between filters/parameters
  • Rearrange columns if desired
  • Display image in row
    • Move “Picture” row all the way to the left
    • Add new column to the right named “Image”
    • Create new sheet named template
    • In A1 add “Image” (be sure it’s exactly the same as A2 on the data sheet
    • In A2 add: <img src="{{Picture}}" class="photo"/>
      • {{Picture}} will be replaced with the content of the cell content in the data spreadsheet under that column name
    • Configure the Awesome Table
      • Under Data Source adjust range to account for new row: A1:H
      • Under Advanced Parameters
        • Set Template range to: template!A1:A2
    • Achieve the same effect by using the parameter “ImageType” on the Picture column

Configure filters & parameters

Filters allow you to manage how columns of data are filtered in your Awesome Tables view. For more information about filters visit Awesome Tables documentation on filters. Parameters allow you to adjust the settings of a particular row. See Awesome Table’s documentation on parameters for more detailed info.

Filters & parameters are specific pieces of text added to the 2nd row of a column. For example, we want to use a StringFilter for our names columns so that we can filter our data by typing in a “string”, which is a fancy way of saying “letters or numbers”. So we add the word “StringFilter” (without quotes and capitalized as show) to the 2nd row under the column titled name on our spreadsheet. This will become more clear later on.

Add Filters

Switch over to your spreadsheet and add the following text to your 2nd row under the corresponding column headers:

  1. State: CategoryFilter
  2. Class: CategoryFilter
  3. Name: StringFilter
  4. Party: CategoryFilter
  5. Born: DateFilter
  6. Seat Up: CategoryFilter

Be sure to use the exact words! They are case sensitive, meaning if you don’t capitalize them as shown they won’t work.

Once the filters are in place on the 2nd row switch over to your Awesome Table view and refresh it (using the refresh icon that looks like a circle arrow)

The Awesome Table view should now display the data from your spreadsheet as expected and there should be filters up at the top!

Your filters might be in a different order, worry not!

Play around with these filters and see the data being displayed change. Can you tell which filter corresponds to the filter name you used? (hint: they are labeled the same as the column headers)

Add Parameters

You’ll probably notice that the picture column is taking up a lot of space and isn’t very helpful just showing a big long URL. We can hide it with parameters!

Don’t forget to checkout the available parameters.

Go back to your spreadsheet and navigate to the 2nd row under picture and add the parameter “Hidden” (without quotes and with the same capital letters). Apply that change and swap back to your Awesome Table view, refresh, and you should see the picture column vanish!

Multiple filters and parameters can be added by adding a dash (with a space on either side) between filters/parameters, like so: “StringFilter – Hidden”

Rearrange columns if desired

On your spreadsheet you can drag the columns by clicking on their lettered label and dragging them around. By rearranging your spreadsheet you will also rearrange the order of your Awesome Table view! Try it out. (Don’t forget to press refresh on your Awesome Tables view when you update your spreadsheet!)

Display image in row

This section covers how to take the URLs from the picture column and get them to show up in your Awesome Table view. It will require a little bit of HTML but I’m fully confident that you’ll be able to do it!

Protip: You can achieve a similar effect by simply using the parameter “ImageType” on the Picture column, skip below to see how.

Let’s start by moving the “Picture” row all the way to the left by clicking on the column’s letter label and dragging all the way to the left.

Then add a new column to the right, just like how we added a row, right click on the column labeled “A” and select Insert 1 right.

Name this new column “Image”.

Next we will create a new sheet and name it “template”.

Click the plus in the bottom right of your spreadsheet window to make a new sheet.

Click the little arrow on the new sheet tab to display an option menu, then select rename and set the title to “template”. Pro tip: you can just double click on the sheet name to edit it.

Now switch to your template sheet.

In cell A1 add the word “Image” (be sure it’s exactly the same name as the column header you just created on your data sheet)

In A2 add:

<img src="{{Picture}}" class="photo"/>

{{Picture}} will be replaced with the content of the cell content in the data spreadsheet under that column name

It should look like this:

Aside: what’s this template stuff all about?!

Awesome Tables has a system for creating templates for data. Each row of data is read from left to right. When Awesome Tables reaches a column with the same column header in the data sheet and the template sheet it looks to the 2nd row of template sheet under the corresponding header. At that point all the data in the row is available to the template as variables. The template is then inserted into the column and the system continues going through the data row.

 

Let’s look at what we’ve just done and take it step by:

  1. Awesome Tables reads the content of the all the columns in a row and turns each cell into an available variable with the same name as the corresponding column header, for example “Picture”.
  2. The column header “Image” is also in the template sheet so Awesome Tables looks at the template cell under that header.
  3. It then replaces the “variable” {{Picture}} with the data from the picture cell. (if there were other “variables” in the template it would replace that data too, as long as those variable names match other column headers in the sheet).
  4. Finally the template sends its content, with the data from “Pictures”, into the “Image” cell on the data sheet, which would look something like this:
    <img src="jane.jpg">

The system continues on as normal and will render the HTML that has been inserted in the “Image” cell! Which you’ll see in just a moment…

Configure the Awesome Table template

The final step to get this all working is to tell your Awesome Table’s view to use the template we just set up. Switch over to your Awesome Table view and checkout the right hand settings column.

Take a note of the last column that has data on your spreadsheet.

Switch to your Awesome Table view under Data Source adjust range to account for new row: A1:H

Under Advanced Parameters set Template range to: template!A1:A2

Note: the template! Part of the range is method to point to a cell or range of cells on another sheet in the same spreadsheet. So the sheet name before the exclamation mark must match exactly the name of the sheet you are targeting.

Easy images with ‘ImageType’

When it comes to images it’s not actually necessary to use a template! With the parameter “ImageType” you can set a column that contains hyperlinks to images to simply display those images. Read this post to learn more.

Just add “ImageType” to the 2nd row under “Picture” and remote the “Image” column.

Automagically images appear in each row! A full size version pops up on hover. I prefer to see the whole picture, so for this example I’m going to set it back to use the template…

Conclusion

Now give the Awesome Tables view a refresh (using the circle arrow button above the table).

Wow! That looks so darn good. Way to go. You’ve created a dynamic data table that is filterable and easy on the eyes. The best part? You can manage it through Google Spreadsheets with collaborators and it will automagically update.

In this tutorial we learned:

  • How to configure an Awesome Tables view.
  • How to create a template to display images in our table from data in a hidden column.

If you have any questions feel free to reach out to drew@goodgoodwork.io or leave a comment below.

You can move onto Part 3: Mapping user submitted data with Google Forms to learn about using maps and user submitted form data or you can skip ahead to Part 4: Sharing and embedding your Awesome Table to learn how to share and display your Awesome Table view.

Leave a Reply

Your email address will not be published. Required fields are marked *