Fetch and Convert Google Sheets Data to JSON with PHP

If you're working with Google Sheets and you need to make its data accessible as JSON for a web application or API, PHP provides a simple way to get, parse, and convert CSV data from Google Sheets. In this post, I'll walk you through a PHP script that gets data from public Google Sheets in CSV format and converts it into a structured JSON response.

Why use Google Sheets as JSON?

Google Sheets are widely used to organize data. Whether for prototyping, content management, or a lightweight database solution, having the ability to convert Google Sheets to JSON opens up many possibilities for dynamic web applications.

Here’s the complete PHP script:

<?php
// Array of sheet URLs with their respective IDs
$sheets = [
    'sheet1' => "https://docs.google.com/spreadsheets/d/e/2PACX-1vQawhdv3OZSq4n3DTEIwY6aID5otU3KTk_BYUUHc8nuCQNerFA0xdWRsd68z4aIpUs3JDFXohjsvJKy/pub?gid=0&single=true&output=csv",
    'sheet2' => "https://docs.google.com/spreadsheets/d/e/2PACX-1vQawhdv3OZSq4n3DTEIwY6aID5otU3KTk_BYUUHc8nuCQNerFA0xdWRsd68z4aIpUs3JDFXohjsvJKy/pub?gid=1073107567&single=true&output=csv",
];

// Set response type as JSON
header('Content-Type: application/json');

try {
    // Get the requested sheet identifier from the query parameter
    $sheet = $_GET['sheet'];

    // Validate the sheet identifier
    if (!isset($sheets[$sheet])) {
        throw new Exception("Invalid sheet identifier.");
    }

    // Fetch CSV data from Google Sheets
    $csvData = file_get_contents($sheets[$sheet]);
    if ($csvData === FALSE) {
        throw new Exception("Failed to fetch data from Google Sheets.");
    }

    // Parse CSV data into an array
    $rows = array_filter(array_map('str_getcsv', explode("\n", $csvData))); // Remove empty rows
    $headers = array_shift($rows); // First row as headers

    if (!$headers || empty($rows)) {
        throw new Exception("Invalid or empty CSV data.");
    }

    // Convert CSV rows to associative array
    $menu = array_map(function($row) use ($headers) {
        $row = array_map('trim', $row); // Trim whitespace
        if (count($row) !== count($headers)) {
            return null; // Skip rows with missing fields
        }
        return array_combine($headers, $row);
    }, $rows);

    // Filter out invalid rows
    $menu = array_filter($menu);

    // Return JSON response
    echo json_encode($menu);

} catch (Exception $e) {
    // Handle errors
    http_response_code(500);
    echo json_encode(['error' => $e->getMessage()]);
}

How It Works

1. Google Sheets Setup:
Ensure your Google Sheet is published as a CSV. Go to File > Share > Publish to Web and copy the CSV link.

2. Mapping Sheet URLs:
The $sheets array maps user-friendly sheet identifiers (e.g., sheet1, sheet2) to their corresponding Google Sheets URLs.

3. Fetching Data:
The script uses PHP’s file_get_contents() to retrieve the CSV content.

4. Parsing CSV:
The data is parsed into an array using str_getcsv() and converted into an associative array with headers as keys.

5. JSON Conversion:
The processed data is encoded as JSON and sent back as the response.

6. Error Handling:
Errors such as invalid sheet identifiers, failed fetches, or malformed data are handled gracefully, returning appropriate error messages.

Example Usage

1. Request Format:
Call the script via URL, passing the sheet identifier as a query parameter:

http://yourdomain.com/sheet-fetcher.php?sheet=sheet1

2. Expected JSON Response:
For a sheet with the following content:

Name,Age,City
Alice,30,New York
Bob,25,San Francisco

The JSON output will be:

[
    { "Name": "Alice", "Age": "30", "City": "New York" },
    { "Name": "Bob", "Age": "25", "City": "San Francisco" }
]

Error Responses

The script includes robust error handling. For example:

Invalid Sheet Identifier:

{ "error": "Invalid sheet identifier." }

Fetch Error:

{ "error": "Failed to fetch data from Google Sheets." }

Advantages of This Approach

Dynamic Data: Updates in Google Sheets are reflected in real-time.
Simple Integration: No external libraries required; works with plain PHP.
Flexible: Can handle multiple sheets using a single script.

This script is a simple yet powerful way to make Google Sheets data accessible via a JSON API. Whether you’re building a frontend app, creating dashboards, or exposing APIs, this technique will save you time and effort.

Mahesh Prajapati @maheshprajapati