877-GRID-666 24/7 online support 604, Tower A, New Trade Plaza, N.T., Hong Kong

Integrate phpGrid with Google Spreadsheets

You can also use a 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 phpGrid Enterprise 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:

google-spreadsheet-original

 

Share the Google Spreadsheet

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

    google-spreadsheet-sharing

     

Publish on the Web

  1. Click File >Publish on the Web
     


    google-spreadsheet-publish-menu

  2. Click “Advanced”, and make sure “Automatically republish when changes are made” is checked.google-spreadsheet-publish
     
  3. Choose “Comma-separated values (.csv) as output type in Link type drop-down
     

    google-spreadsheet-share-csv-output

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

     

    google-spreadsheet-share-link

     

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
$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!