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);
       // 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);
    // 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'];
    return $array;



To use simply call the google_sheet() function. It requires a Google Sheet URL which should be in the standard format:


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("");
echo "<table>";

foreach ($table as $row):
    echo "<tr>";
    foreach ($row as $cell):
        echo "<td>" . $cell . "</td>";
    echo "</tr>";

echo "</table>";