Airtable API Example & Tutorial - Generating Charts

January 31, 2017

Airtable is a user-friendly spreadsheet but can be used as a lightweight database. In this tutorial I'll show you how to fetch data from your spreadsheet using the Airtable API. Next, we'll take the data and draw a chart using chart.js.

Here's a live example.

Requirements

  • An Airtable account (note your API key, spreadsheet ID, and table name).
  • A server with PHP installed.
  • cURL enabled

Need help finding your Airtable API key? Go to http://www.airtable.com/api when logged in, select the the base you'll be working with and check "show API key."

Using Airtable's API

cURL HTTP Requests

Here's how to make a request using Airtable's API.

$api_key = 'YOUR_API_KEY';
$base = 'YOUR_SPREADSHEET_ID';
$table = 'YOUR_TABLE_NAME';
$airtable_url = 'https://api.airtable.com/v0/' . $base . '/' . $table;
$url = 'https://api.airtable.com/v0/' . $base . '/' . $table . '?maxRecords=10&view=Main%20View';
$headers = array(
    'Authorization: Bearer ' . $api_key
);
$ch = curl_init();
curl_setopt($ch, CURLOPT_HTTPGET, 1);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
curl_setopt($ch, CURLOPT_TIMEOUT, 10);
curl_setopt($ch, CURLOPT_HTTPHEADER, $headers);
curl_setopt($ch, CURLOPT_URL, $url);
$entries = curl_exec($ch);
curl_close($ch);
$airtable_response = json_decode($entries, TRUE);

This cURL request fetches the data in your Airtable and converts it to a PHP array with json_decode(). If your spreadsheet was just a list of groceries, the JSON output would look something like:

"records": [
  {
    "id": "recMGvRShuBoKakfY",
    "fields": {
      "grocery": "bread"
    },
    "createdTime": "2016-10-25T12:18:53.000Z"
  }
],
"offset": "recMGvRShuBoKakfY"

So we convert the JSON to a PHP array, and create a foreach loop to echo the output as follows:

foreach($airtable_response['records']['fields'] as $key => $value) {
  $string.= $value['Data'] . ', ';
  $labels.= '"' . $value['Label'] . '", ';
}
$data = trim($string, ",");
$labels = trim($labels, ",");
?>

$record['fields']['Data'] is the header of one column in your Airtable, $record['fields']['Label'] is the head of another column.

Displaying The Data With Chart.js

The next step is to use the javascript library chart.js to display the data.

Include the script in your head tag as follows. Note that chartjs requires jquery.

‚Äč

Chatjs Example

var ctx = document.getElementById("myChart");
var myChart = new Chart(ctx, {
    type: 'bar',
    data: {
        labels: ["Red", "Blue"],
        datasets: [{
            label: '# of Votes',
            data: [12, 19],
            backgroundColor: [
                'rgba(255, 99, 132, 0.2)',
                'rgba(54, 162, 235, 0.2)'
            ],
            borderColor: [
                'rgba(255,99,132,1)',
                'rgba(54, 162, 235, 1)'
            ],
            borderWidth: 1
        }]
    },
    options: {
        scales: {
            yAxes: [{
                ticks: {
                    beginAtZero:true
                }
            }]
        }
    }
});

Here's what the chart looks like (jsfiddle).

To display our Airtable data, we just need to substitute for the data and the labels. There are more elegant ways to accomplish the same thing but this is the quick-and-dirty version.

See the entire source code on github here.