Fetch data from Google Sheet and show in HTML using PHP

May 6, 2025

This documentation explains how to use the provided PHP code to fetch data from Google Sheets and display it in a custom layout using HTML/CSS.

Setup Instructions

Step 1: Get Your Google Sheet ID Open your Google Sheet in a web browser
Look at the URL in the address bar - it will look like:
https://docs.google.com/spreadsheets/d/BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ/edit#gid=0
The long string between /d/ and /edit is your Sheet ID (in this example: BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ)

Step 2: Create a Google API Key

Go to the Google Cloud Console
Create a new project or select an existing one
Navigate to “APIs & Services” > “Library”
Search for “Google Sheets API” and enable it
Go to “APIs & Services” > “Credentials”
Click “Create Credentials” and select “API key”
Copy your new API key
(Optional) Restrict the API key to only work with the Sheets API for security

Step 3: Configure the PHP Code
Replace these values in the code:

$sheetID = “BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ”; // Your Sheet ID
$apiKey = “xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx”; // Your API Key
$rows_count = 8; // Number of rows to display

**Step 4: Sheet Configuration
**Your Google Sheet must have a named range or sheet tab that matches what you put in the API URL (in this example: Sheet1)

Make sure your Sheet is either: Publicly accessible (set to “Anyone with the link can view”) Or shared with the email address associated with your API key
Here is the entire code.

<?php
// Google Sheets API configuration
$sheetID = "BmzaSyBzQ0cRTrbf_vxrB75nh8AoV3BtawPiiCQ"; // Replace with your actual Sheet ID
$apiKey = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"; // Replace with your API Key
$apiURL = "https://sheets.googleapis.com/v4/spreadsheets/$sheetID/values/Sheet1?key=$apiKey";

$rows_count = 8; // Number of rows to be displayed

// Fetch the Google Sheets data
$response = file_get_contents($apiURL);
$data = json_decode($response, true);
if (isset($data['values']) && count($data['values']) > 1) {
for ($i = 1; $i <= min($rows_count, count($data['values']) - 1); $i++) {    

// Get the column wise data from Google Sheet, if no value then show N/A
$column_1 = isset($data['values'][$i][0]) ? htmlspecialchars($data['values'][$i][0]) : "N/A";
$column_2 = isset($data['values'][$i][1]) ? htmlspecialchars($data['values'][$i][1]) : "N/A";
$column_3 = isset($data['values'][$i][2]) ? htmlspecialchars($data['values'][$i][2]) : "N/A";
$column_4 = isset($data['values'][$i][3]) ? htmlspecialchars($data['values'][$i][3]) : "N/A";
$column_5 = isset($data['values'][$i][4]) ? htmlspecialchars($data['values'][$i][4]) : "N/A";
?>

<?php } ?>
<div class="row">
<div><?php echo $column_1;?></div>
<div><?php echo $column_2;?></div>
<div><?php echo $column_3;?></div>
<div><?php echo $column_4;?></div>
<div><?php echo $column_5;?></div>
</div>
<?php } ?>