Integrate with .CSV or Google Spreadsheets

You can use a local .csv or Google Spreadsheet as a data source to populate your datagrid. In this tutorial, you will learn how to create a shared Google Spreadsheet and share it in comma-separated values (CSV) format. Note that the array data source feature is only available with a commercial license.

Loading from Google Spreadsheet is straight forward and much the same as loading from a local array data source. In order to download the Google Spreadsheet in CSV format, you need to follow this guide to generate a download link for the CSV file of Google Drive Spreadsheet.

Open a Google Drive Spreadsheet

Open an existing Google Spreadsheet such as the one shown here:

Share the Google Spreadsheet

Click Change… to change access settings,
Click “Public on the Web” or “Anyone with the link”,
Click Save

Publish on the Web

Click File >Publish on the Web
Click “Advanced”, and make sure “Automatically republish when changes are made” is checked.
Choose “Comma-separated values (.csv) as output type in Link type drop-down
Finally, copy the document link created in the previous step. You should have a link similar to the following with “output=csv” in the URL parameter:
https://docs.google.com/spreadsheets/d/1IvbMsUZTCdY7duciT3lWSXHPP_qPDG8FrJl8dq1ZbI/pub?output=csv

Start Coding

First of all, we need to massage our data into a format that phpGrid can recognize. You can read more about this in the local array data data example. In our Google Spreadsheet sample file, the first row contains the header information. We will extract that row and use the contents of each cell in the row as the name for each column in the datagrid. Once the data has been formatted, it becomes accessible as though it were a local file.

1
2
3
4
5
6
7
8
9
10
11
12

$spreadsheet_url = 'https://docs.google.com/spreadsheets/d/1IvbMsUZTCdYb5z34jT3lWSXHPP_qPDG8FrJl8dq1ZbI/pub?output=csv';
$csv = file_get_contents($spreadsheet_url);
$rows = explode("\n",$csv);
$data = array();
$names = array();
for($i=0; $i<count($rows); $i++) {
if($i==0){
$names = str_getcsv($rows[$i]);
}else{
$data[] = str_getcsv($rows[$i]);
}
}

The final step is to add the phpGrid code. We add the title to each datagrid column. We then enable auto filter in integrated search, and give the datagrid a new look using our premium theme “Aristo”.

Please note that “search auto filter” is a new feature. It generates a filter drop-down in integrated toolbar search based on a column’s unique values.

Since Google Spreadsheet does not put in a separate column header when a file is saved as a .csv, we compensate for this by using a column index array, e.g. array(1,2,3,5), as our search auto filter.

1
2
3
4
5
6
7
8
9
10

// Always include namespace and conf.php on TOP of the script.
use phpCtrl\C_DataGrid;
require_once("/file/path/to/conf.php");

$dg = new C_DataGrid($data, "id", "Google_Spreadsheet");
for($i=0; $i<count($names); $i++) { $dg->set_col_title($i, $names[$i]);
}
$dg->enable_search(true, array(1,2,3,5));
$dg->set_theme('aristo');
$dg->display();

That’s how you populate datagrid from a Google Spreadsheet. Enjoy!

Demo

Framework Integration Google Spreadsheet