Google Spreadsheet Integration

You can also use 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 array data source is a feature only available in Enterprise license.

Loading from Google Spreadsheet is straight forward as if loading from a local array data source. first of all, in order for our system 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 like the following:

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

   

Publish on the Web

1. Click File >Publish on the Web

   

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

   

4. Finally, copy document link. You should have a link similar to the following with output=csv in 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 format that phpGrid can recognize. You can read more about it in local array data data example. In our Google Spreadsheet sample file, the first row contains the header information. We will also extract that row as the name for each column in datagrid. After formatting, the data becomes accessible as if it is a local file.

  1  $spreadsheet_url = 
  2  'https://docs.google.com/spreadsheets/d/1IvbMsUZTCdYb5z34jT3lWSXHPP_qPDG8FrJl8dq1ZbI/pub?output=csv';
  3  $csv = file_get_contents($spreadsheet_url);
  4  $rows = explode("\n",$csv);
  5  $data = array();
  6  $names = array();
  7  for($i=0; $i<count($rows); $i++) {
  8  if($i==0){
  9  $names = str_getcsv($rows[$i]);
 10  }else{
 11  $data[] = str_getcsv($rows[$i]);
 12  }
 13  }

Finally, we add our phpGrid code. We first add title to each datagrid column. We also enable auto filter in integrated search, and lastly give it a new look using our premium theme “aristo”.

Please note that search auto filter is a new feature that dynamically generates filter drop-down in integrated toolbar search based on column’s unique values. Since Google Spreadsheet does not send separate column header in its .csv format, here we use column index array, e.g. array(1,2,3,5), as our search auto filter.

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

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

The post Google Spreadsheet Integration appeared first on phpGrid.