• 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.