How can I access a Google Sheets document using PHP?

Other Questions Google drive
1

I’m trying to fetch data from a Google Sheets file using PHP but I’m stuck. I’ve set up the Google Drive API and got my credentials. I’ve also shared the spreadsheet and got its key.

Here’s what I’ve done so far:

  1. Enabled the Drive API in Google Console
  2. Created OAuth 2.0 credentials
  3. Downloaded the Google API PHP Client Library
  4. Set up the PHP code to connect

My code looks something like this:

<?php
require_once 'google_api_client.php';

$client = new GoogleClient();
$client->setAuthConfig('credentials.json');
$client->addScope(Google_Service_Sheets::SPREADSHEETS_READONLY);

$sheetsService = new Google_Service_Sheets($client);
$spreadsheetId = 'my_spreadsheet_id';

try {
    $range = 'Sheet1!A1:D5';
    $response = $sheetsService->spreadsheets_values->get($spreadsheetId, $range);
    $values = $response->getValues();
    print_r($values);
} catch (Exception $e) {
    echo 'Error: ' . $e->getMessage();
}
?>

But when I run this, I get an error about exceeding the daily limit for unauthenticated use. What am I missing? How can I properly authenticate and read the spreadsheet data?

2

hey there, have u tried the sheets api directly? i had similar issues before. try using a service account json for auth instead of oauth and make sure the spreadsheet is shared with the service account email.

lemme know if u need more help!

3

I’ve worked with the Google Sheets API quite a bit, and it sounds like you’re on the right track. The error you’re getting about exceeding the daily limit for unauthenticated use suggests that your authentication isn’t being applied correctly.

Make sure you’re using the correct scope. For reading spreadsheets, you should use Google_Service_Sheets::SPREADSHEETS_READONLY.

Also, don’t forget to authenticate your client before making the API call. Add this line before creating the sheetsService:

$client->authorize();

Lastly, double-check that your credentials.json file is in the correct location and has the right permissions.

If you’re still having issues, try enabling debug mode on the client to get more detailed error messages:

$client->setLogger(new Google_Logger_File(‘php://stderr’));

Hope this helps! Let me know if you need any more clarification.

4

Have you considered using the Google Sheets API v4? It’s more straightforward and doesn’t require the Drive API. You’ll need to enable it in your Google Console and update your credentials.

Here’s a simplified approach:

  1. Install the Google Client Library via Composer
  2. Use service account authentication instead of OAuth
  3. Utilize the spreadsheets.values.get method

Your code might look like this:

require 'vendor/autoload.php';

$client = new Google_Client();
$client->setAuthConfig('path/to/service-account.json');
$client->setScopes([Google_Service_Sheets::SPREADSHEETS_READONLY]);

$service = new Google_Service_Sheets($client);
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

This approach should resolve your authentication issues and simplify the process.