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 <code>json_decode()</code>. 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 <code>head</code> tag as follows. Note that chartjs requires jquery.

Chatjs Example

<canvas id="myChart" width="400" height="400"></canvas>
<script>
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
                }
            }]
        }
    }
});
</script>

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

To display our Airtable data, we just need to substitute <?php echo $data; ?> for the data and <?php echo $labels ?> 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.