Wavesdream

Fetch data from Google Sheet and show in HTML using PHP

Written by

Sanjay Bhowmick

in

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

1. Get Your Google Sheet ID

  1. Open your Google Sheet in a web browser
  2. 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)

2. Create a Google API Key

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

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

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 } ?>

More posts