How do I access the Google Spreadsheets API in PHP?


Answers

Thanks for the hints and links on this page, I wanted to share what I ended up doing to read a google spreadsheet in php. You can access the spreadsheet in json format, and don't need to use api or zend framework or gdata library. PhP can handle json very easily and it was the most neat solution I could reach to because it has no dependency to any third party libraries.

Here is a sample link for getting a spreadsheet in json format.

https://spreadsheets.google.com/feeds/list/key/1/public/full?alt=json

Please note if spreadsheet is private you still need to follow the authentication steps to get token to access to the page. In my case the spreadsheet was public.

Also you can access the spreadsheet via feed list or cell based (replace the list in the url with cell)

You can try the link in a browser and see the json result.

Question

Starting with the Google Developer documentation on the Google Spreadsheets API, I found that "A number of client libraries are provided in various languages.", but Google only provides client libraries for Java and .NET. For a PHP library, they recommend using Zend GData. So, I headed over the the Zend GData repo, and Zend says that their GData component is discontinued, and to use Google APIs Client Library for PHP. That library doesn't work with the Spreadsheets API, for that you have to go back to the original page that I started with.

So, it seems like everyone's passing the buck. I just need to write some PHP code that requests and processes data from a Google Spreadsheet. How do I do that? Please don't link me to similar posts or pages without checking to be sure that they are 100% up to date, I've been sifting through a huge mess of posts for hours that are all full of outdated and deprecated dependencies.




Using google sheet as database in only google drive

There is a GitHub library for PHP, if your site happens to use PHP

github.com php-google-spreadsheet-client

There are a few options:

  • Google Sites - With a Google Stand Alone HTML Apps Script in a Apps Script Gadget
  • Google Stand Alone HTML Apps Script
  • Website that Uses the Sheets API

If you try to access a Google sheet from a website with a different domain name, you will get a CORS error. CORS is a Cross Origin Resource Sharing. Each option for connecting to a spreadsheet has it's own issues. If you use a Stand Alone HTML App with Apps Script, you can't have a domain name. A free Google Site, doesn't have a domain name, but it's a nicer URL, and a Google Site can be mapped to a domain name. A website with a regular domain name, will require an API to do something with Sheets. The documentation for the Sheets API show examples written in Protocol, Java, and .Net. But, the documentation states:

A number of client libraries are provided in various languages. These client libraries make it easier to interact with the Sheets API.

But I don't see any reference to where all these various languages are.

I don't know of any easy to understand documentation for using a Google spreadsheet as a data source from a website. So, unfortunately, I don't know of an easy answer. The answer depends on what you want/need, and how much time and skill you have to set it up.

So, do you need a domain name or not? You can map a Google Site to a domain name:

https://support.google.com/sites/answer/99448?hl=en

But the documentation also states:

You can't map your domain to what is known as a naked domain, such as http://example.com

If you need to use a website and connect to the Google spreadsheet with an API, you'll need to run an authorization. Otherwise, any website could read/write to your spreadsheet.

Obviously there are websites that write data to Google spreadsheets, so it's possible.

You can also look at posts like this:

google-spreadsheet-api-with-php

how-do-i-access-the-google-spreadsheets-api-in-php




How to get list of sheets from google spread sheet in php

You can get a list of sheets by using the get method on spreadsheets:

sheet_metadata = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
sheets = sheet_metadata.get('sheets', '')
title = sheets[0].get("properties", {}).get("title", "Sheet1")
sheet_id = sheets[0].get("properties", {}).get("sheetId", 0)

You can also check the PHP Quickstart for a PHP command-line application that makes requests to the Google Sheets API.

References:





Tags