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.