How to get data from Google Spreadsheet using PHP

This article guides you on how to work with data stored in Google Sheets using the SheetDB API in PHP.

Getting data from Google Sheets can be handy, especially if you want to quickly manage or analyze your data. Let’s explore how you can do this using plain PHP and SheetDB API. The process is straightforward, and you don’t need any framework to get it done.

Setting Up Your PHP Script:

Create a new PHP file: Begin by creating a new PHP file for your project. For the purpose of this tutorial, let’s call it sheetdb.php

Initialize a GET function: To retrieve data from your Google Sheet, we will use the GET endpoint of the SheetDB API. The base URL for this would look something like https://sheetdb.io/api/v1/{API_ID} where {API_ID} is the ID of your API, which can be found on the dashboard at sheetdb.io after creating an API for your spreadsheet.

function getDataFromSheet($apiId, $parameters = []) {
    $url = "https://sheetdb.io/api/v1/" . $apiId;

    // Add parameters if available
    if(!empty($parameters)) {
        $url .= '?' . http_build_query($parameters);
    }

    $response = file_get_contents($url);
    return json_decode($response, true);
}

Initialize a CREATE function: To add new rows to your Google Sheet, you can use the POST endpoint of the SheetDB API. For this, we will make use of the file_get_contents function with a context stream for the POST data:

function createRowInSheet($apiId, $data) {
    $url = "https://sheetdb.io/api/v1/" . $apiId;
    $options = [
        'http' => [
            'method'  => 'POST',
            'header'  => "Content-type: application/x-www-form-urlencoded\r\n",
            'content' => http_build_query(['data' => $data]),
        ],
    ];
    $context = stream_context_create($options);
    $response = file_get_contents($url, false, $context);

    return json_decode($response, true);
}

Usage: To use these functions in your script, call them as follows:

// Fetch data
$sheetData = getDataFromSheet('YOUR_API_ID', ['limit' => 10, 'offset' => 5]);
print_r($sheetData);

// Create data
$newData = [
    ['id' => 4,'name' => "Mark"],
    ['id' => 5,'name' => "Susan"]
];
$creationResponse = createRowInSheet('YOUR_API_ID', $newData);
print_r($creationResponse);

Note: Always handle exceptions and errors appropriately in a live environment. The above examples are to get you started, and there might be some additional configurations or error handling needed based on your requirements.

Using SheetDB API provides a simple and effective way to interact with your Google Sheets data. No need for any complex setups or libraries, just plain PHP. If you are interested in more advanced functionalities or other methods, you can always refer to the official SheetDB documentation.

I hope you find this guide useful for your projects. If you have any questions or need further assistance, don’t hesitate to ask. Happy coding!