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.
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; }
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.
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>";
#