This PHP function accepts a public Google Sheets URL and converts it into a PHP array. You can use the array to either display the sheet live, or save it to a database to display later.
This code doesn't use the Google API so it'll not work on protected or hidden sheets, only those with a public/shareable URL.
PHP Function
function google_sheet($url = NULL) {
$array = array();
if ($url):
// initialize curl
$curl = curl_init();
curl_setopt($curl, CURLOPT_URL, $url);
curl_setopt($curl, CURLOPT_HEADER, 0);
curl_setopt($curl, CURLOPT_RETURNTRANSFER, TRUE);
// get the spreadsheet data using curl
$sheet = curl_exec($curl);
curl_close($curl);
// find the table pattern and return the mark-up
preg_match('/(<table[^>]+>)(.+)(<\/table>)/', $sheet, $matches);
$data = $matches['0'];
// convert the HTML (XML) mark-up to JSON
$cells_xml = new SimpleXMLElement($data);
$cells_json = json_encode($cells_xml);
$cells = json_decode($cells_json, TRUE);
endif;
// Convert the JSON array to an array of just the table data
// This will strip out any Google Sheets formatting and identifiers if they exist
if ( is_array($cells) ):
foreach ($cells['tbody']['tr'] as $row => $row_data):
$column = 'A';
foreach ($row_data['td'] as $column_index => $cell):
// Check that the cell is populated and get the value.
if (!is_array($cell)):
$array[($row + 1)][$column++] = $cell;
elseif ($cell['div']):
$array[($row + 1)][$column++] = $cell['div'];
endif;
endforeach;
endforeach;
endif;
return $array;
}
Usage
To use simply call the google_sheet() function. It requires a Google
Sheet URL which should be in the standard format:
google_sheet('https://docs.google.com/spreadsheets/d/$unique_sheets_id/edit')
Where $unique_sheets_id is the unique ID of the Google Sheets document.
Rebuild the table
The array can then be used to rebuild the sheet as a HTML table.
$table = google_sheet("https://docs.google.com/spreadsheets/d/abc123/edit");
echo "<table>";
foreach ($table as $row):
echo "<tr>";
foreach ($row as $cell):
echo "<td>" . $cell . "</td>";
endforeach;
echo "</tr>";
endforeach;
echo "</table>";