Google Sheets to PHP Array

Posted in PHP on 4 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 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>";

 

#

Related PHP Posts

August 2021

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... Continue reading

March 2021

PHP cURL Requests with JSON or XML

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

February 2021

Global Payments: Strong Customer Authentication (SCA) and 3D Secure 2

The roll out of 3D Secure 2 has been a long drawn-out process not helped by the COVID–19 pandemic. Part of the update has included... Continue reading

More PHP Posts