Google Spreadsheets and PHP

Have you ever needed to pull some data from a Google Spreadsheet? My default in the past would be to export the data and upload it to the app directly, but it turns out it’s not very difficult to read directly from Google Spreadsheets using the Google Drive API.

In this tutorial, we’ll read, write, update, and delete data from a Google Spreadsheet with just a few lines of PHP.

I’ve spent a lot of time recently working with tech activism groups, and one piece of data folks often want to work with is legislators’ contact information. Let’s take this spreadsheet with the contact information for all United States legislators. Make a copy of this spreadsheet in your own account and we’re ready to go.

Getting Your Spreadsheet Ready for Programmatic Access

By default, a new spreadsheet cannot be accessed via Google’s API. We’ll need to go to your Google APIs console and create a new project and set it up to expose your Spreadsheets’ data.

  1. Go to the Google APIs Console.
  2. Create a new project.
  3. Click Enable API. Search for and enable the Google Drive API.
  4. Create credentials for a Web Server to access Application Data.
  5. Name the service account and grant it a Project Role of Editor.
  6. Download the JSON file.
  7. Copy the JSON file to your app directory and rename it to client_secret.json 

We now have a big chunk of authentication information, including what Google calls a client_email , which uniquely represents this OAuth service account. Grab the value of client_email from your client_secret.json , and head back to your spreadsheet. Click the Share button in the top right, and paste the client_email value into the field to give it edit rights. Hit send. That’s it!

Read Data from a Spreadsheet with PHP

Let’s start building!

We won’t be writing any framework-specific code here; as long as you have Composer installed and loaded, you’re ready to follow along.

Want to try this out with vanilla PHP? Create a PHP file in your project directory to hold this code and at the head of the file, right after
<?php put this line:

Now create a file in your project named composer.json  and place an empty JSON object in it:

When you want to test your code, just run php yourfilename.php. You’re ready to start coding!

Bootstrapping Google API Authentication

First, require our packages; we’ll be using google/apiclient library for authentication and the asimlqt/php-google-spreadsheet-client library for accessing Spreadsheets:

Next, somewhere in your boot process (in a framework, I would put it in a boot file or a service provider, but for now you can just put it at the top of a file) you’ll want to initialize the client with a token that we’ll generate using the google/apiclient library:

I know this is a bit hairy, but you only have to do it once, and then all of your requests in your app will just authenticate correctly. You’ll know the app is connecting correctly if the steps in the next section work without errors.

Getting Real Data

Next, let’s create a route that you can visit in your browser and start pulling real data. If you’re testing this out in vanilla PHP, just add this at the bottom of the file you’re working on.

All of our calls will start by getting the spreadsheet, and, from there, the worksheet that contains our data.

To show that this worksheet works, let’s iterate over every entry and echo it out:

We’re working with a ListFeed object here, which represents a “list” of rows, and each row inside that list is returned as a ListEntry object.

When we print these out, we’ll see our output with the value of each cell attached to the header row for each column. The array will be shaped a bit like this:

Note that this SDK is using Google Spreadsheet API v3, which is XML-based; one of the shortcomings of the format is that it removes all underscores from column names, turning  last_name into lastname and first_name into firstname. If you want to get around this, you can just deal with it as it is, use one of the few relatively-untested Google Spreadsheets v4 libraries, or map your records manually using the CellFeed object as defined below.

We could instead, get a CellFeed object, which allows us to get each row as its raw entries, without mapping to the title:

… which returns:

We can also use the CellFeed object to get a single cell by column/row indexes:

Finally, we can also just export the entire sheet as a CSV:

Insert, Update, and Delete from a Spreadsheet with PHP

So far we’ve only read from the spreadsheet. But we can also update and delete the rows or cells we retrieve, or even insert our own cells or worksheets.

Updating Cells

First, let’s take one of those cells we retrieved and update it:

Updating Rows

We can also update an entire row, keyed by the column names:

Deleting Rows

Let’s delete a full row:

The Spreadsheets package we’re using is focused on getting us full lists, not pulling individual items out of the list, so we’ll need to use and break a foreach. If we were using a collection pipeline like Laravel’s collections, this would be a bit cleaner:

Inserting Rows

We can also add our own rows to a worksheet. We need to grab a ListFeed object and run insert on that.

Troubleshooting

If you see this error while inserting or updating:

It’s likely because you didn’t define one of the columns. Even if you’re setting it to a blank value, you still need to provide it.

Now that you know how to read, insert, update, and delete records from your Google Spreadsheet, you have a fully functional database layer that’s easy to spin up and has a built-in UI layer for non-tech folks to view and administer your data.

Thanks to Greg Baugues for the idea and the original post on using Google Spreadsheets and Python

—————

Matt Stauffer is the author of O’Reilly’s Laravel: Up and Running and Partner & Technical Director at Tighten Co. You can find him on Twitter at @stauffermatt.

 

  • Shahroze Nawaz

    Thanks Matt! I’ve read the Blog thoroughly and surely i am going to implement it with one of my application :)

  • That is just Great..!! Very nice topic, Thank you for such a wonderful tutorial. I’ll be definitely implementing it in my application.

  • Montes Montes

    I have a problem

    Fatal error: Uncaught Error: Class ‘ServiceRequestFactory’ not found in C:xampphtdocsgoogleindex.php:19 Stack trace: #0 {main} thrown in C:xampphtdocsgoogleindex.php on line 19

    ServiceRequestFactory::setInstance(new DefaultServiceRequest($accessToken));

    • Aaron

      You’re probably trying to get a spreadsheet that’s not associated or does not exist in the account used to make your client_secret.json

      I suggest chaing
      ->getByTitle(‘{{Title of your sheet here}}’)

  • Michael Keough

    One of the best descriptions I have seen on connecting Google API!

  • Krishna Shah

    Hello, Please can you help me to get rid out of this issue. I am having an exception:
    PHP Fatal error: Class ‘ServiceRequestFactory’ not found

    • Jon Orlin

      I am also getting this error.

    • Alexey Shkurpela

      I’m too :(

      • Aaron

        You probably need to add

        “use GoogleSpreadsheetServiceRequestFactory;”

        • Jon Orlin

          Thanks Aaron. I got it to work adding these lines to the top of the script:

          use GoogleSpreadsheetDefaultServiceRequest;
          use GoogleSpreadsheetServiceRequestFactory;

          the original poster, while providing useful info, left this out and also if you want to see the results, you need to add:
          print_r($representative);
          inside the foreach $listFeed loop above.

  • Rishi Kushwaha

    awesome tutorial.Thanks it’s save my time.but for my case it is work whenever i run from command prompt.not from web browser.

  • Kostya Tkachuk

    Thanks, this topic is great and can I sort rows by some field (formatted as datetime)?

  • Daniel

    Doesn’t work.. even by adding
    use GoogleSpreadsheetDefaultServiceRequest;
    use GoogleSpreadsheetServiceRequestFactory;

    What can I do ?

    • Marjun

      Same here, got this error too

  • Marjun

    Got this error. Can someone help me?

    Fatal error: Uncaught exception ‘GuzzleHttpExceptionRequestException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate (see http://curl.haxx.se/libcurl/c/libcurl-errors.html)’ in D:wampwwwemailverifiervendorguzzlehttpguzzlesrcHandlerCurlFactory.php:187
    Stack trace:
    #0 D:wampwwwemailverifiervendorguzzlehttpguzzlesrcHandlerCurlFactory.php(150): GuzzleHttpHandlerCurlFactory::createRejection(Object(GuzzleHttpHandlerEasyHandle), Array)
    #1 D:wampwwwemailverifiervendorguzzlehttpguzzlesrcHandlerCurlFactory.php(103): GuzzleHttpHandlerCurlFactory::finishError(Object(GuzzleHttpHandlerCurlHandler), Object(GuzzleHttpHandlerEasyHandle), Object(GuzzleHttpHandlerCurlFactory))
    #2 D:wampwwwemailverifiervendorguzzlehttpguzzlesrcHandlerCurlHandler.php(43): GuzzleHttpHandlerCurlFactory::finish(Object(GuzzleHttpHandlerCurlHandler), Object(GuzzleHttpHandlerEasyHandle), Object(GuzzleHttpHandlerCurlFactory))
    #3 D:wampwwwemailverifiervendorgu in D:wampwwwemailverifiervendorguzzlehttpguzzlesrcHandlerCurlFactory.php on line 187

    • Hugo Leon

      If you are on Windows as I think you are, the solution is probably this https://github.com/asimlqt/php-google-spreadsheet-client/issues/24#issuecomment-59742298

      • Leo van der Hoek

        Did not change anything for me! Do I need to get a certificate file and how do I activate that?

        • Hugo Leon

          Hi Leo!

          I’m not sure, in my case it was that the firewall was blocking the requests from the server. But took me some time to figure that out.

  • katisuhara

    Thanks man! Was easy with your explanation!

  • This is a difficult read. 1) What about those of us withOUT command line access?

  • 2) Your first reference to “Composer” is: “as long as you have Composer installed and loaded,” Please explain in context with the sequence of the rest of your tutorial.

  • 3) What is /vendor/autoload.php’; ?? Is this a file we’re supposed to download from somewhere, and upload to our webserver? What’s the reference?

  • 4) Is “yourfilename.php. You’re ready to start coding!” the name of the php script that I will run every time I want to read my Google sheets, or is this a one-time file?

  • Remy

    thanks! working fine, just had to had the uses (use GoogleSpreadsheetDefaultServiceRequest;
    use GoogleSpreadsheetServiceRequestFactory;)

  • Oscar Montes

    I get this error

    Fatal error: Cannot access protected property GoogleSpreadsheetCellEntry::$content

    $topLeftCornerCell = $cellFeed->getCell(1, 1);
    echo $topLeftCornerCell->content;

    var_dump($topLeftCornerCell);

    object(GoogleSpreadsheetCellEntry)#13 (6) {
    [“xml”:protected]=>
    object(SimpleXMLElement)#15 (6) {
    [“id”]=>
    string(110)
    “https://spreadsheets.google.com/feeds/cells/1_TyJG-YNfOuYtr8vZsa8QQD4dnI7RusSZ029QWhUIoo/od6/private/full/R1C1”

    [“updated”]=>
    string(24) “2017-07-24T20:57:43.956Z”
    [“category”]=>
    object(SimpleXMLElement)#28 (1) {
    [“@attributes”]=>
    array(2) {
    [“scheme”]=>
    string(43) “http://schemas.google.com/spreadsheets/2006”
    [“term”]=>
    string(48) “http://schemas.google.com/spreadsheets/2006#cell”
    }
    }
    [“title”]=>
    string(2) “A1”
    [“content”]=>
    string(9) “last_name”
    [“link”]=>
    array(2) {
    [0]=>
    object(SimpleXMLElement)#26 (1) {
    [“@attributes”]=>
    array(3) {
    [“rel”]=>
    string(4) “self”
    [“type”]=>
    string(20) “application/atom+xml”
    [“href”]=>
    string(110)
    “https://spreadsheets.google.com/feeds/cells/1_TyJG-YNfOuYtr8vZsa8QQD4dnI7RusSZ029QWhUIoo/od6/private/full/R1C1”

    }
    }
    [1]=>
    object(SimpleXMLElement)#49 (1) {
    [“@attributes”]=>
    array(3) {
    [“rel”]=>
    string(4) “edit”
    [“type”]=>
    string(20) “application/atom+xml”
    [“href”]=>
    string(117)
    “https://spreadsheets.google.com/feeds/cells/1_TyJG-YNfOuYtr8vZsa8QQD4dnI7RusSZ029QWhUIoo/od6/private/full/R1C1/xak710”

    }
    }
    }
    }
    [“postUrl”:protected]=>
    string(105)
    “https://spreadsheets.google.com/feeds/cells/1_TyJG-YNfOuYtr8vZsa8QQD4dnI7RusSZ029QWhUIoo/od6/private/full”

    [“row”:protected]=>
    int(1)
    [“column”:protected]=>
    int(1)
    [“content”:protected]=>
    string(9) “last_name”
    [“inputValue”:protected]=>
    string(9) “last_name”
    }

  • Ketav Chotaliya

    How can i fix this issue “Blank rows cannot be written; use delete instead.”

    I have created new Sheet, and Create New Service account. But Still getting same error.