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.

Go to the Google APIs Console. Create a new project. Click Enable API. Search for and enable the Google Drive API. Create credentials for a Web Server to access Application Data. Name the service account and grant it a Project Role of Editor. Download the JSON file. 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: