Update Google Spreadsheets with 3rd party APIs

I’ve been using Google Docs and Spreadsheets more and more over the past number of months and I wanted to see if I could easily add or update information in a Google Spreadsheet, using Google’s Spreadsheet API and also some 3rd party APIs.

I found a handy PHP library which does most of the work on Github:

https://github.com/asimlqt/php-google-spreadsheet-client

This library makes makes it easy to list any spreadsheets, workbooks and rows of data to view, add and update them.

Before using this particular library though, you need to create an Application on Google first to get an Access token for your application to have the permission to manage the documents.

This project also on Github walks you through creating the Application and getting the Access token, its easy to get up and running in a short few minutes:

https://github.com/asimlqt/php-google-oauth

Once you have generated an Access token from using the ‘php-google-oauth’ library, you can then use the ‘php-google-spreadsheet-client’ library to access your Spreadsheets using PHP and manipulate the data.

I created 2 quick examples to try out updating existing data in a Spreadsheet. The first was to look up the Gender of names in a spreadsheet using https://genderize.io/, a free Gender lookup API.

Gender source code in a Gist

Here’s a little animation of the result:

Automatically add a Gender

Automatically add a Gender

 

Another example was to use http://fixer.io/ to look up the current exchange rate of a lit of Euro values and return the current USD value

Currency source code in a Gist

Here’s a little animation of the result:

Auto look up Currency conversion

Auto look up Currency conversion

 

 

 

 

3 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *