Almost every sport out there has some stats to track against. Tracking sports stats with Google Spreadsheets can be a quick and dirty way of keeping up with how your team (or league) is progressing. What if there was a way to create a connection to a Google Sheets (Google Spreadsheets) and your site?

I did this quick exercise to see if it was possible for me to keep track of my own teams stats while allowing the individual players to input their own stats. I didn’t want to baby sit the team and I wanted to make it easier on everyone. We all had trust with one another, so having wild and varied values were not a problem. Granted, this write-up can definitely go beyond sports stats with Google Spreadsheets to include much more advanced functionality. For now, lets just go through the basics of creating a site of sports stats with Google Spreadsheets.

Overview

1. Create and configure Google Spreadsheet
2. Sign up for Google API Key
3. Use jQuery to call data and display

 

Create Google Sheet

Since we’re using Google Sheets to house the data, we just need to set up the appropriate cells and data access.

• Input your columns and data as you need
• Set the Share option to also access “Get shareable link” (click here for instructions) and so that ‘Anyone with the link can view’.
• Set the Permission to who you’d like to INPUT data to the sheet. Add the people you want to participate in updating their stats.
  • Invite people to Edit the sheet
• Select the cells that are specific for a person then right-click
  • Select the option to Protect range… to limit who from the invited people can edit that specific cell
• Set the permissions for the specific person or group of people as needed

You have now created a Google Sheets that allows its members to ONLY modify their own cells.

 

Sign up for Google API Key

The API Key is basically a way to control the data that is going on between the spreadsheet and your site. You dont want to accidentily allow anyone and everyone to use you Google Sheets maliciously. So head on over to the Google API Console to create your Google Sheets API.

1. Use this wizard to create or select a Google Sheets project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
2. On the Add credentials to your project page, click the Cancel button. We’ll come back to this.
3. Select the Credentials tab, click the Create credentials button and select API Key
4. Close the confirmation window

1. Edit the new API Key you have created
   1. Update the API Key Name
   2. Configure the Credentials to the API Key
   3. Chose an appropriate Key restriction based on where you’d the requests will be coming from
   4. In this example, I selected HTTP referrers (web sites) to limit access from only my personal website
2. Click the Save button.

Once your API Key has credentials around it, don’t forget to Enable the API for Google Sheets and Google Docs from the project Dashboard. The API Key allows access to all of the goodness that you’re going to use.

You have now created a Google Sheets API key. The sports stats with Google Spreadsheets data will have much tighter access control.

 

Using jQuery to Access and Display Data

jQuery is a quick and simple way to grab the data within the spreadsheet and throw it onto a page. We’re going to be using the AJAX method within jQuery to parse the data and add the results to the page.

I used the DataTable jQuery plugin to control the table data. Its a pretty slick tool that lets you throw JSON or HTML data at it and it wil take care of the rest. There are a lot of options with the table as well, such as sorting, search, or responsiveness.

Head over to the example page if you’d like to get more info on DataTables.

We are going to use the Google API Key that we just signed up for and the Google Sheets ID from the Sheets document that we just created. This is going to be our data sources.

//Author: Marion Owen
//Email: info@mowenworks.com
//Description: This is the data connection to the Google Sheets file. 
 
// ID of the Google Spreadsheet
var spreadsheetID = "ID_OF_SHEET";
var api = 'GOOGLE_API_KEY';

// Make sure it is public or set to Anyone with link can view 
var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetID + "/values/Sheet1!A2%3AC14?key="+api

Then we are going to use the jQuery and AJAX to grab the data and pop it into the DataTable function. I configured my properties for the DataTable to include ordering and and responsiveness. Search is a standard feature.

$(document).ready(function() {
 $('#dataTables').DataTable({ 
     responsive: true, 
     ordering: true, 
     paging:false, 
     ajax:{ 
          url: url, 
          dataSrc: 'values' } 
 }); 
});

Your HTML structure is almost pre-canned and needs to follow a basic format for the DataTable to know where to put the information.

<div class="dataTable_wrapper">
 <table class="table table-striped table-bordered table-hover" id="dataTables">
   <thead>
      <tr>
           <th>Name</th>
           <th>Goals</th>
           <th>Assists</th>
      </tr>
   </thead>
   <tfoot>
      <tr>
           <th>Name</th>
           <th>Goals</th>
           <th>Assists</th>
      </tr>
   </tfoot> 
   </table>
</div>
<!-- /.table-responsive -->

After that, you’re pretty much set. The members of your team can easily go to the Google Sheets and input the data that they own. The information on Sports Tracker page will update with a quick page refresh.

 

Full Code

<link href="https://cdn.datatables.net/1.10.12/css/jquery.dataTables.min.css" rel="stylesheet" type="text/css">

<div class="dataTable_wrapper">
 <table class="table table-striped table-bordered table-hover" id="dataTables">
   <thead>
      <tr>
           <th>Name</th>
           <th>Goals</th>
           <th>Assists</th>
      </tr>
   </thead>
   <tfoot>
      <tr>
           <th>Name</th>
           <th>Goals</th>
           <th>Assists</th>
      </tr>
   </tfoot> 
   </table>
</div>
<!-- /.table-responsive -->


<!-- jQuery -->
<script src="./js/jquery.min.js"></script>
<!-- DataTables -->
<script src="https://cdn.datatables.net/1.10.13/js/jquery.dataTables.min.js"></script>

<script>
//Author: Marion Owen
//Email: info@mowenworks.com
//Description: This is the data connection to the Google Sheets file. 
 
// ID of the Google Spreadsheet
var spreadsheetID = "ID_OF_SHEET";
var api = 'GOOGLE_API_KEY';

// Make sure it is public or set to Anyone with link can view 
var url = "https://sheets.googleapis.com/v4/spreadsheets/" + spreadsheetID + "/values/Sheet1!A2%3AC14?key="+api

$(document).ready(function() {
 $('#dataTables').DataTable({ 
     responsive: true, 
     ordering: true, 
     paging:false, 
     ajax:{ 
          url: url, 
          dataSrc: 'values' } 
 }); 
});
</script>

 

References

• Google Sheets API Intro
• Google Sheets with JavaScript
• DataTable jQuery Plugin