How do I use the People Directory Feature of Awesome Table?

In this tutorial, we will be heavily customizing the People Directory template in Awesome Table, and will dive in to the following techiques:

Populating the template spreadsheet
Customizing the HTML Template
Hosting and Adding Photos from Google Drive
Further Customizing the Awesome Table

If you would like to follow along using the same sample data to populate the template, make a copy of this sheet.

Create a copy Template

Go to Awesome-table.com and Click the people directory. Follow the prompts to make your own copy.

You will now have a copy of the Awesome Table view, and the spreadsheet powering the table. Take a moment to interact with both the table and the spreadsheet to see how they are structured and interact.

Populate the Template with your data

We'll need to delete the sample data. Be sure to not edit the first two rows of the sheet.

Enter in your data. Here, we populate the Columns A, F and G.

Note that in columns F and G, we are storing different data than the template. While column F had Country Data, we are now storing the Voiced By data, and in G rather than Department, we are storing Quote.

You will need to be very careful when editing the first two rows, including column labels, as these drive the template and the Awesome Table view.

Continuing following the instructions to edit the template to account for these changes.

Edit the HTML Template

We now need to edit the template. Switch to the Template sheet. Any time you see text surrounded by double curly brackets, {{like this}}, that data is being merged into the card view in the Awesome Table from the sheet. The {{double curly bracket tags}} refer to column titles in row 1.

Let's update Country to now be Voiced By, and Department now to be Quote.

If you know some basic HTML, you can further edit the template. Be careful, as your HTML needs to be formatted correctly (always close your tags with the corresponding closing </> tag.

For basic information on HTML, refer to the W3Schools HTML Reference Site.

Adding the Photos

Now we will tackle a trickier issue. We want to provide photos to display in the Awesome Table.

These photos need to be hosted in Google Drive and linked to in the sheet in a particular way.

First, start with a folder of images where each image has the same file name as one of the columns in your sheet. In this case, each photo uses the same exact First Name as in the Sheet (it must be exact to the character - watch extra spaces, punctuation, etc).

If you are following along, make a copy of this folder to have the sample directory photos.

We now need a way to easily create working links to these photos. The links are not typical Google Drive sharing links, but direct links to the photos themselves.

We will use another Awesome Table tool, the Photo Gallery, as a "hack" to easily create these links.

Go to the template list at awesome table and select Photo Gallery

Install the add-on

Grant the necessary permissions.

In the new blank spreadsheet, click use the existing album as we have a folder of photos to use.

Grant the necessary permissions.

Close the tab.

Click use existing album again.

Select the folder that contains your photos. In this case we are using the Lego Movie Directory Photos folder

Awesome table creates a photo gallery. We won't be using this, and can close the tab.

However, Awesome Table creates a helpful spreadsheet which contains the Photo Title, ID and URL.

Copy this Data...

Navigate to your directory sheet and create a new sheet

Paste the data into the sheet.

Insert a new column to the right of the photo file name, and use the following formula to only have the name, not the file name. This formula drops the last 4 characters from the text string in the cell.

=Left(A1,LEN(A1)-4)

The left formula will display the characters in a string starting from the left. So Left(A1,3) will show the first 3 letters of a string.

We want to drop the last 4 characters off of the string. To do this, we will find the length of the string using LEN(), and subtract 4, and use that in our left formula.

Fill this formula down the column you created.

Here we see an example of the column with the title minus the file extension.

Now for the trickiest and funest part! In the main sheet, in the Picture column, we will be creating a formula that will look up (match) the name to the ID, and then create the URL will need to display the photo.

First, let's look at the URL that we need to create. Here's an example:

http://drive.google.com/uc?export=view&id=1KXQC_M9Xx2_3axp8rwdte77dCSy-0Jkv

This URL is a special one, in that it links directly to the photo as a file and can be referenced using the HTML <img>.

Note that while the URL is long, it follows a pattern. http://drive.google.com/uc?export=view&id= comes first, followed by the ID, in this case 1KXQC_M9Xx2_3axp8rwdte77dCSy-0Jkv.

So we can create these URLs combining the ID with the first, consistent part of the URL (http://drive.google.com/uc?export=view&id=).

To do this, let's start by matching, or looking up, the ID for a Name

To do so, we will use the Lookup Function. This function takes a cell, looks at a range, finds that match and then displays the content of cells in another column. Here we want to find the name and then result in the ID.

=vlookup(A3,Sheet1!$B$1:$D$9,3,False) is the formula we will use. Let's break that down.

A3 has the name we want to find in our ID table (the one we made with the Photo Album feature and pasted in)

Sheet1!$B$1:$D$9 is the range where the table is. We've added the $ in the cell references as we don't want the reference to change when we fill down - the range stays the same no matter what.

2 refers to the column in the range that we want to return. we want to return the ID, which is found in the second column over in the range.

False is a keyword that looks for an exact match. We want the ID that exactly matches the name.

You should see this results in the Matching ID for the photo.

Now, let's take that ID and combine it with the URL pattern to make the URL we need.

Remember, our URLs should look like: http://drive.google.com/uc?export=view&id=1KXQC_M9Xx2_3axp8rwdte77dCSy-0Jkv

We will use the concatenate to combine two strings - "http://drive.google.com/uc?export=view&id=" and the ID (which is the vlookup formula we made).

We then wrap this in a IFERROR function, which will hide errors if any occur. This will make the Awesome Table View stay "pretty" even if data we give it is in error - it won't display anything rather than an error message.

Here's the final formula we created: =IFERROR(concatenate("http://drive.google.com/uc?export=view&id=",vlookup(A3,Sheet1!$B$1:$D$9,2,false),""))

Fill this down the column.

Check the Awesome Table view - you should now see the photos displaying!

Further Customizing the Data and Template

Let's further customize the template. We won't be using Weight or Birth Date, so let's remove those columns.

Now our data only spans from A to K, so we can edit the Data Source to A1:K in Awesome Table.

Error! When we do this, an error occurs. We need to adjust the Query settings as well.

Under Advanced Parameters, Remove the column letters from the Query. In this case we need to delete K and L wherever they occur.

Now the Awesome Table should display again.

Continue using these techniques!

You can continue using these techniques to further customize your Awesome Table, by adjusting the HTML templates, columns and column types and adjusting the query. Take your time, move slow and be ready to back track if anything goes wrong!