Google Sheets to PHP Array

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>";

#