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>";
#
March 2024
As PHP continues to evolve, so do the threats that target its vulnerabilities. Ensuring robust PHP security practices is paramount to safeguarding sensitive data and... Continue reading "PHP Security in 2024: navigating the evolving landscape"
August 2021
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... Continue reading "Google Sheets to PHP Array"
March 2021
The following post will explain how to use PHP/cURL to retrieve data in JSON or XML and process it for using in your PHP application... Continue reading "PHP cURL Requests with JSON or XML"