How do I read a Google Drive Spreadsheet in PHP?

Question

All I'm trying to do is read a Google Spreadsheet from a web site. I've read and re-read the Google Drive API docs and everything Google Drive PHP on Stack Overflow and I still can't get to the end zone.

Here's what I've done :

Been to the Google APIs Console and :
1. Enabled "Drive API" and "Drive SDK" under 'Services';
2. Created an OAuth 2.0 client ID under 'API Access'. Under "Client ID for web applications", the console gave me "Client ID", "Email address", "Client secret", "Redirect URIs" and "JavaScript origins";
Downloaded the "Google API PHP Client Library";
Opened the Google Drive document (spreadsheet) and clicked on "Share" to get the document's 'key';
Set up the following code :

<?php 
session_start(); 
require_once 'lib/gapi/Google_Client.php'; 
require_once 'lib/gapi/contrib/Google_DriveService.php'; 

define( 'GDRIVE_CLIENT_ID', '<API Console - API Access - Client ID>' ); 
define( 'GDRIVE_CLIENT_SECRET', '<API Console - API Access - Client secret>' ); 
define( 'GDRIVE_REDIRECT_URIS', '<API Console - API Access - Redirect URIs>' ); 

define( 'GDRIVE_SCOPE_01', 'h t t p s://www.googleapis.com/auth/drive' ); 
define( 'GDRIVE_SCOPE_02', 'h t t p s://www.googleapis.com/auth/drive.apps.readonly' ); 
define( 'GDRIVE_SCOPE_03', 'h t t p s://www.googleapis.com/auth/drive.file' ); 
define( 'GDRIVE_SCOPE_04', 'h t t p s://www.googleapis.com/auth/drive.metadata.readonly' ); 
define( 'GDRIVE_SCOPE_05', 'h t t p s://www.googleapis.com/auth/drive.readonly' ); 
define( 'GDRIVE_FILE_KEY', '<'key' given from 'sharing' document>' ); 

$client = new Google_Client(); 
$client->setClientId( GDRIVE_CLIENT_ID ); 
$client->setClientSecret( GDRIVE_CLIENT_SECRET ); 
$client->setRedirectUri( GDRIVE_REDIRECT_URIS ); 
$client->setScopes( array( GDRIVE_SCOPE_01, GDRIVE_SCOPE_02, GDRIVE_SCOPE_03, GDRIVE_SCOPE_04, GDRIVE_SCOPE_05 ) ); 

try { 
  $file = $service->files->get( GDRIVE_FILE_KEY ); 
  echo "Title: ", $file->getTitle(); 
  echo "Description: ", $file->getDescription(); 
  echo "MIME type: ", $file->getMimeType(); 
} catch (Exception $e) { 
  echo "An error occurred: ", $e->getMessage(); 
} 
?>

All runs fine (no errors anyway) until the $service->files->get( GDRIVE_FILE_KEY ) call which triggers the exception:

An error occurred: Error calling GET https://www.googleapis.com/drive/v2/files: (403) Daily Limit for Unauthenticated Use Exceeded. Continued use requires signup.

What am I doing wrong? I've pulled my hair out (well, what was left).

Of course, references to 'h t t p s://' are really 'https://' but StackOverflow complained about posting links. — Kiser, Feb 08 '13 at 20:29
Please use the **"{}"** toolbar button to format code as code. — Álvaro González, Feb 08 '13 at 20:30
Instead of doing crazy HTML inside your answer, you should better use 4 spaces to indend your code. The coloration & highlight will be automatically apply. — j0k, Feb 08 '13 at 20:31

score 80 · Answer 1 · edited Aug 10 '21 at 09:20

80

There is also a much easier, but less clean solution, if you don't want to bother with the API or Google Authentication.

Go to your Spreadsheet in Google Drive.
Go to File -> Publish to the Web
In the Publish to the web dialog you can get a .csv Link to your spreadsheet.

You can now access the contents like any other csv File on the Web. Here is some sample code:

$spreadsheet_url="https://docs.google.com/spreadsheet/pub?key=<somecode>&single=true&gid=0&output=csv";

if(!ini_set('default_socket_timeout', 15)) echo "<!-- unable to change socket timeout -->";

if (($handle = fopen($spreadsheet_url, "r")) !== FALSE) {
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $spreadsheet_data[] = $data;
    }
    fclose($handle);
}
else
    die("Problem reading csv");

edited Aug 10 '21 at 09:20

Matthew Lock

13,144
12
92
130

answered Aug 07 '13 at 14:54

Ben

1,631
13
15

2

I was just about to dive into writing HTML scraping code when I found this answer. Thank you! – KennethJ Apr 17 '14 at 05:46
I'm 99% certain this doesn't work anymore, unless I'm missing something very obvious. It looks like they've removed the CSV functionality from the share dialog. – A.B. Carroll Jul 10 '14 at 21:36
11

True, this option has been removed by Google. But there is still a workaround here: [link]http://stackoverflow.com/questions/21189665/new-google-spreadsheets-publish-limitation[/link] CSV file : `https://docs.google.com/spreadsheets/d//export?gid=0&format=csv` – Ben Jul 13 '14 at 14:58
Dose this still work, dose "Publish on the web" mean that to make the spreadsheet public? How does the auth 2 authentication work here? – zhihong Aug 22 '14 at 14:19
@zhihong: Yup, it is then published as a csv file. No authentication. – Ben Aug 25 '14 at 15:59
@Ben, thanks, as my file is not public, then this dose not work for me. I am using google drive API now as in this link https://developers.google.com/drive/v2/reference/ – zhihong Aug 26 '14 at 09:38
3

This answer is still valid today, you just get a different link then in the presented code, like @Ben already said in the comments. +1 – dev_masta Mar 07 '16 at 23:47
2

This works! Brilliant! Since the spreadsheet I had to use did not contain any confidential data (it was to be displayed on a site anyway), this saved a LOT of time. – finitenessofinfinity Mar 05 '17 at 11:29
1

This just works if the data is meant to be publicly accessible. – Juampy NR Mar 29 '17 at 17:46
This is awesome, but if I publish the csv file with password protection, do you know if there is a way to access this file through my php script? – LucyTurtle Dec 26 '17 at 20:37
4

Now it's 2019.. It's possible to share a csv file from google drive by publishing it to the web with csv as option, but if the sourcefile is uploaded with new content, this link will change. Is it possible to use same csv-file all the time? – bestprogrammerintheworld Mar 03 '19 at 21:08

score 7 · Answer 2 · edited Nov 14 '16 at 21:54

7

Please check the Google Drive PHP Quickstart. You have not actually authorized your client. Starting from $authUrl = $client->createAuthUrl();

All Google Drive requests need authorization of some kind.

edited Nov 14 '16 at 21:54

nu everest

9,589
12
71
90

answered Feb 09 '13 at 16:01

Ali Afshar

40,967
12
95
109

If the user runs this script, he/she has already gone through local Apache authentication. Can I re_use the same GDRIVE_CLIENT_ID and GDRIVE_CLIENT_SECRET? When the script runs, I want to unconditionaly read the Google Drive Spreadsheet. I don't want the visitor to have to re-authenticate. – Kiser Feb 09 '13 at 22:26
API authorization is not the same as authentication. The user must grant access to your app to read the file. – Ali Afshar Feb 11 '13 at 13:52
3

But, the Google file is **MY** file. I maintain it. I just want **MY** PHP script to read it. It is as if I maintain an *included* file on Google Drive. I want my script to unconditionally read this document with my Google Drive access codes. The visitor does not need to log in. He/she still can't modify my document; they can only see it. – Kiser Feb 11 '13 at 14:40

score 5 · Answer 3 · answered Mar 31 '17 at 10:23

5

I created a sample project that uses a service account to authenticate against Google Spreadsheets in order to access to the contents of a spreadsheet.

Have a look at the README at https://github.com/juampynr/google-spreadsheet-reader.

answered Mar 31 '17 at 10:23

Juampy NR

2,618
1
25
21

score 3 · Answer 4 · answered Aug 17 '13 at 10:29

You need to use oauth, if you don't google will only allow you to make a few requests.

If all you want to do is read data out of a google spreadsheet or write data into it then you can just use the spreadsheet api. Check out php-google-spreadsheet-client.

score 1 · Answer 5 · answered Apr 22 '14 at 02:11

1

If you want you own file to be read you need a service account instead of a "Client ID for web applications". I've been battling this problem myself for way to long and this brought me the sollution: https://developers.google.com/drive/web/service-accounts

answered Apr 22 '14 at 02:11

Daan Luttik

2,781
2
24
37

The link is dead. – nu everest Nov 14 '16 at 21:16
It is indeed dead, but I found an article which might be related using the service accounts info. https://developers.google.com/identity/protocols/OAuth2ServiceAccount – Graph Jan 11 '17 at 19:20

How do I read a Google Drive Spreadsheet in PHP?

5 Answers5

Linked