Yasir Khan In this article I’ll guide you that how to link and retrieve data from Google Spreadsheets into webpage using PHP and DataTables with BootStrap styles in step-by-step in just few mins.
Table of Contents
What is DataTables?
DataTables plug-in is one of the popular jQuery/JavaScript library tool, which is highly flexible, free and easy to use. It adds advanced features to HTML table, such as Instant Search, Sorting, Multi-column, Pagination and many more.
Google Spreadsheet Requirements:
1. You’ll need to publish the google spreadsheet by clicking “Publish to web” under File > Share menu.
2. Then select “Comma-separated values(.csv)” to export the file into csv
3. You’ll then see the generated link something like this
https://docs.google.com/spreadsheets/d/e/<somecode>/pub?output=csvIntegration With PHP:
Here is the magic code which will convert your Google Spreadsheet data into html DataTables.
<?php
//This function will read the Google file as csv
function readCSV($csvFile){
$file_handle = fopen($csvFile, 'r');
while (!feof($file_handle) ) {
$line_of_text[] = fgetcsv($file_handle, 1024);
}
fclose($file_handle);
return $line_of_text;
}
//Put your Google file path here
$csvFile = '<your google spreadsheet path/link here>';
$csv = readCSV($csvFile);
//Start table with some default styles
echo '<table id="myTable" class="table table-striped">';
//Header of the table
echo '<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Show</th>
</tr>
</thead>';
//For loop to get the content from each sheet cell
foreach ($csv as $item) {
echo '<tr><td>' . $item[0] . '</td><td>' .$item[1] . '</td><td>' . $item[2] . '</td></tr>';
}
//End table
echo '</table>';
?>Integration DataTables with BootStrap:
Import the css files in <head> tag
- BootStrap CSS – CDN
<link rel=”stylesheet” href=”https://cdnjs.cloudflare.com/ajax/libs/twitter-bootstrap/5.2.0/css/bootstrap.min.css”>- DataTables CSS – CDN
<link rel="stylesheet" href="https://cdn.datatables.net/1.12.1/css/dataTables.bootstrap5.min.css">- jQuery Library – CDN
<script src="https://code.jquery.com/jquery-3.5.1.js"></script>- BootStrap + DataTables JS – CDN
<script src="https://cdn.datatables.net/1.12.1/js/jquery.dataTables.min.js"></script>
<script src="https://cdn.datatables.net/1.12.1/js/dataTables.bootstrap5.min.js"></script>- Initialize your DataTable in <script> tag with in the same file or you can create separate file as well.
<script>
$(document).ready( function () {
$('#myTable').DataTable({
"pageLength": 20
});
});
</script>Checkout the demo click here.
The article has been written especially for the beginners. If you get stuck or have questions at any point, simply send me a message and I will do my best to help you out.
Thanks for the post!