Home » Blog » Intro to Awesome Tables: Display Google Spreadsheet Data on Your Webpage

Intro to Awesome Tables: Display Google Spreadsheet Data on Your Webpage

Have you ever needed to share a body of dynamic data with the world? Maybe a list of local representatives and how they pledge to vote on an upcoming piece of legislation. Perhaps you are trying to organize distributed activities across the city and need a way to display them on a map. When it comes down to it, most data can be expressed in a spreadsheet, and you’ve probably worked on plenty of spreadsheets. Have you ever wished you could just share the data on a spreadsheet with your collaborators or the whole world? Have you ever wished you could share all the updates you make as they happen?

Well you can. With Google Spreadsheets and Awesome Tables you have the power to create rich dynamic displays based off spreadsheet which can be added to directly or via online forms. All the data in one place, displayed anywhere on the web, updated in real time!

First let’s take a look at an Awesome Table. Below is a list of sitting US Senators (which you’ll create in the next tutorial). Notice the filter at the top that dynamically changes the data displayed underneath.



The above embed is drawing live data from a Google Spreadsheet. Let’s get started!

Objective

You will gain a basic understanding of Awesomes Tables through this four part series of examples based tutorials. You will learn the following:

  1. Part 1: How to create and link a Google Spreadsheet to an Awesome Tables view.
  2. Part 2: Display spreadsheet data in a filterable manner through Awesome Tables.
  3. Part 3: Collect user data through a Google Form and display that data automatically onto a map using Awesome Tables.
  4. Part 4: How to share and publish anywhere on the web.

PDF version of this tutorial is available for download here.

Part 1: Creating your first Awesome Table

In this tutorial we’ll create a Google Spreadsheet, add some data, and attach it to an awesome table.

You will need:

  • Google account with access to Google Drive (it’s free, but they use your data to target ads to you).
  • A web browser!
  • Some data to work with (here’s a CSV of some example data)

Glossary

  • Spreadsheet – A document in which data is arranged in the rows and columns of a grid and can be manipulated and used in calculations, if you’ve ever used Microsoft Excel you’ve used a spreadsheet.
  • Sheet – A spreadsheet is a collection of individual sheets, which are typically listed as tabs along the bottom of your spreadsheet window.
  • Cell – A single box in the spreadsheet, all cells have a coordinate consisting of the column letter and row number, example: A1 or C12
  • Column – The vertical stack of cells, these are labeled with letters starting with “A”
  • Row – The horizontal line of cells, labeled with numbers starting with “1”
  • Range – Multiple cells that touch form a range. A range is written out as the top left and bottom right cell coordinate with a colon between them, example: A1:C4
  • Column Header – This is the first cell in a column in row number 1
  • Awesome Table view – This is the document or “view” generated by Awesome Tables that is attached to your spreadsheet.

Outline

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

Gather data

Figure out what data you wish to use, perhaps you already have something in mind, if not you can download an example set of data for this project:

Example data CSV

Create new spreadsheet

Log into your Google account and head over to Google Drive.

There is a shortcut URL that will take you to a new spreadsheet automatically: sheets.google.com/create

The whole project will be managed through a single Google Spreadsheet. This sheet will exist on Google’s servers and can be made available to any collaborators with Google Accounts (e.g. anyone with a gmail address). Keeping your data on the “cloud” means that you don’t need to worry about multiple versions or things getting out-of-sync. Furthermore, this sheet will directly control the data that will ultimately display on our website. Any changes to the sheet will automagically update to your website!

From inside Google Drive click the new button and select Google Sheets.

Once the spreadsheet loads make sure you name it by clicking the title at the top left.

Import (or add) data

Example data: awesome-tables-example-1-data

If you’d rather set up your own data you can skip the import and simply start building your spreadsheet. We will be importing a CSV (Comma Separated Value) of US Senators, however Google will allow you to import many formats such as Excel or Libreoffice.

On your spreadsheet select File > Import…

Select the file off your Google Drive (My Drive) or switch to the Upload tab and drag your file into the big box.

You will be presented with the Import file dialog box, be sure to Replace current sheet (assuming you have a blank sheet, otherwise select a better option).

You should now have a bunch of data on your spreadsheet with “column names” at the top row 1. These labels are your column headers.

Attach to Awesome Table

Open up a new browser tab and visit https://awesome-table.com/

You’ll be presented with a simple page urging you to log-in with Google. What we are going to do is allow Awesome Tables to access our Google Docs data. To be clear, the people who make Awesome Tables won’t have access to your data, it’s basically connecting your Google account with their application with limited permissions.

The next box that pops up will outline what permissions you are giving to Awesome Tables, always review these dialog boxes carefully! You should trust the applications that ask for any permissions. Always think about what an application might need to do what it says it will do, if it asks for unrelated permissions it might be wise to not accept… We can trust Awesome Tables though, so click Allow.

You should now see a blank area where views would go if you had any. Let’s Create a new view from the Blank template.

Select a Spreadsheet from your Google Drive. You should be able to find the one you just created. If you can’t find it make sure that your spreadsheet has a name (click the title in the top right when viewing your spreadsheet).

Configure your new view, the default settings should just work, we can adjust this later if you need to. Click create.

Configure the spreadsheet

You should now see the data of your spreadsheet displayed on the Awesome Table view. You might notice that it’s a bit messed up! Let’s switch back to your spreadsheet and set it up.

First you’ll want to add a blank row under your column headers in row 1.

Right click on the row label with the number 1 and select Insert 1 below. This will push all the data down one row and you should now have a blank row 2. This second row is very important for Awesome Tables, we’ll get to it in the next example.

For now you can switch back to your Awesome Tables view and click the refresh button (it’s a circle with an arrow pictured below)

Your table should reflect the data from the spreadsheet now.

Sharing

Your Awesome Table view will inherit whatever sharing setting you spreadsheet is set to. If you want anyone on the internet to view the Awesome Table then set the spreadsheet to Anyone with the link can view. Details on how this is done are in Part 4: Sharing and Embedded your Awesome Table.

Conclusion

Now that you’ve got your spreadsheet set up with data and have created an Awesome Table view that is reading that data you are ready to configure how the data is displayed.

Move on to Part 2: Filterable Senator List with Pictures!

2 comments

Leave a Reply

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