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