Warning: Your browser is very out-of-date. You should upgrade to a better browser.

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

 

#

Hello, I'm Keith, a website developer in Belfast, Northern Ireland working with PHP, Magento, Shopify and WordPress.

I've been building websites for over 10 years, from custom website development to bespoke web applications, Shopify and Magento ecommerce and Online Leaning Environments. I've worked on a range of projects and am always looking out for the next interesting project.

Related PHP Posts

PHP 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...
PHP 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...
PHP 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...

More PHP Posts...