Show Mobile Navigation

Tuesday, March 28, 2017

, , , ,

How to Use Python to Read and Write to Google Sheets

Oyetoke Tobi - Tuesday, March 28, 2017

Python is a broadly used high-level programming language for general-purpose programming, which was created by Guido van Rossum and first released in 1991. Python is an excellent programming language. The syntax may seem strange and unusual, but it is easy to learn and use. It powers Minecraft Pi Edition, Django Web Framework, along with a great deal of websites and academic research projects.

Python can be used for both programming language and scripting language. Therefore python is a very wide language which can be used to build normal software when compiled or used as a normal script with the python compiler.

Google Spreadsheets is a Web-based application that allows users to create, update and modify spreadsheets and share the data live online. The Ajax-based program is compatible with Microsoft Excel and CSV (comma-separated values) files. Spreadsheets can also be saved as HTML.

In this article, I’ll be showing you how to read and write to Google Sheets using Python.

Setting Up Google Sheet API

Before you can start using python to edit and write into Google Sheets or start writing code, you’ll need to set up some settings on Google sheets. You are also going to set up the Google Sheet API we’ll be using.

Firstly, head over to docs.google.com/spreadsheets and create a new sheet. You can just skip this step if you have a spreadsheet set up already. Then input data into the spreadsheet. I will be using a list of car colors for this tutorial.

Next, you need to set up your sharing options. You will also need to generate Signed Credentials, which sounds more difficult than it actually is. To do that, simply head over to the Google Developers Console and create a new project (or use an existing one if you have one). To create a new project, click on the dropdown button beside “Google API”. Then click on “Create new project”. Now enter the name of the project you want to create and simply click on “Create” to create the project.

Then a new page will open showing the list Google APIs available. Underneath Google Apps APIs select Drive API

Now click on the Enable button to enable the project.

Then select the Credentials button on the left side:

Click the little arrow on the Create credentials button

Then select “Service account key” from the dropdown menu.

Afterward, you’ll see a dropdown button below “Service account”. Click on it and select “App

Engine default service account. Then select JSON as the format under “Key type.”

After setting up everything, click on create, and you should get a .json file download.

If you can’t find the App Engine default service account”, click on “Create new service account.” Afterward a form will appear, under “Where will you be calling the app from”, select where the app will be called from. Since we are using python on our PC, simply select on “Windows, CLI Tools”. Of course you can select other options if you like. Then click on any options you would like to use under “What data will you be accessing?” and click on the button below.

Next, a new form will appear asking for details for the service accounts you want to create. Enter a name in the text under “Service account name” and the service account ID will be automatically generated. Then click on the Role dropdown, hover on the App Engine entry and select each of them.

Then under Key type select JSON and click on the continue button. You should get a .json file download.

Now move the .json file into your project directory and rename it creds.json. Then, open the file and search for “client_email.” This should be the name of your project at appspot.gserviceaccount.com.

After that, head back to the Google Sheet and share your Google Sheet with the email address. You can do that by clicking on the share button at the top-right corner, enter the email in the .json file and click on send.

That’s all for the Google Drive side. Now let’s proceed to the Pyhton side.

Python Setup

There are two major Python versions: 2.7 and 3.x. I’ll be using 2.7 in this tutorial, but it does not really matter what you use. However, if you want to know the differences, the Python wiki does break down the differences. You can also install a Virtual Environment which is outside the scope of this article, but it’s a good practice.

Now if you are running Windows or Linux, you may need to download and install Python manually since they don’t come with it like Mac did. Mac OS usually comes with Python already installed. So if you are using a Mac, you don’t need to install it again. You should be able to follow reasonably well regardless of the machine you are using.

The first thing to do now is to open CMD on Windows, Terminal on Mac and Linux. Then you will need to use pip to install some Python packages. This is a very good and useful that makes it very easy to install and manage packages. It comes with Python3.x.Of course, you can use it on 2.7 also by downloading and installing it manually. Head over to the official pip site for more info.

Once the pip is working well, we are ready to go.

Now you need to install an oauth2client. Oauth is simply a web authorization framework. OAuth (Open Authorization) is an open standard for token-based authentication and authorization on the Internet. OAuth allows an end user's account information to be used by third-party services, such as Facebook, without exposing the user's password. I’ll not be discussing the details of it, but it’s necessary for everything to work correctly and securely. It’s easy to install using pip:


pip install oauth2client

You may also need to install PyOpenSLL depending on your system setting.

pip install pyOpenSSL

Now you need to install Gspread created by Anton Burnashev on GitHub. This is great library written to make it easy to access Google Sheets in Python. And, you can easily install it using pip:

pip install gspread

Then open any text editor you’d like to use, I’m using Sublime Text, since it is one of the most powerful and easy to use text editor. Now create a new Python file and save it as any name you want (pysheets.py) in your python directory. Then enter the test code below and save it:

print “Hello, World!”

Then head back to your cmd or terminal and navigate to the python directory. On windows, if you’ve the path environment set up for python, you can easily start python from anywhere without the need of navigating to the python directory. On terminal, you can do this through the cd command. You can use ls to list files, and pwd to show your working directory.

Once the cmd or terminal is in your python directory, you can enter the below command:

python pysheets.py

You should now be able to see Hello, World! in your command line.

Since Python is now working properly, lets go ahead and setup the libraries. Remove the hello world code. Now we are going to import gspread, oauth2client and execute the code again:

import gspread

import oauth2client

If things are working accurately, nothing will happen. But if you get an error, possibly saying no module named Y where Y is the name of the module you typed (gspread). You can go and double check if you have pip installed the module properly, and that you have not made any typographical error.

Once everything is settled, you can start coding your pysheets with the code below:

import gspread

import json

from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']

credentials = ServiceAccountCredentials.from_json_keyfile_name(“creds.json” scope) # get email and key from creds

file = gspread.authorize(credentials) # authenticate with Google

sheet = file.open("Scholars_Globe").sheet1 # open sheet

The above code simply retrieves your details from the .json file, and then uses them to authenticate with Google. It then opens a sheet called Sholars_Globe. You may perhaps need to change this to the name of your sheet (provided that you have shared it properly). Python is case sensitive, so make sure you enter the name correctly.

Reading Google Sheets

Now that everything is setup, it’s trivial to read or write data. Here’s how you select a range of cells (in this case, all of the car cells):

all_cells = sheet.range('A1:C6')

print all_cells

With the above code, Python will dumped the real contents of the object, with no regard for formatting. Because this is stored in the all_cells variable, it can be accessed like any other Python object. Here’s how you print all the cell values in a nicer format:

for cell in all_cells:

    print cell.value

Of course, iIt’s possible to access cells individually even though this is slow if you do it lots of times:

A1 = sheet.acell('A2').value # this cell contains "GZ-T20"

Or you can use the cell coordinates:

coord = sheet.cell(3, 0).value

It’s also very easy to get all the values for a row:

row = sheet.row_values(1) # first row

Or you can get a whole column. This basically gets the Model row:

col = sheet.col_values(2) # models

Have these in mind that these two methods do not know how much data you have. So if you only have three rows, multiple extra empty cells will be returned with it. It’s practically always better to access a predefined block of cells.

Writing Into Google Sheets

Writing into the Google Sheet is as easy as reading Google Sheet. It’s basically just as easy to write back into the sheet, and you can use cell names or coordinates to write into them, just like when reading:

sheet.update_acell('C2', 'Blue')
sheet.update_cell(2, 3, 'Blue')

The project page on GitHub has many more examples.

If you are writing to an important sheet, you may wish to consider using a safety cell. You can simply do that by storing a value in a certain cell and then read that cell first. If the contents have changed, then columns have been added or removed in your sheet, so don’t proceed writing and if it doesn’t continue writing! You can achieve that by the code below:

if sheet.acell('B3') != 'SAFETY':
    # something has changed in the sheet, DO NOT PROCEED
    raise Exception("Error File Changed")
else:
    # continue with your writing
    sheet.update_acell('C2','Blue')

This is a very good practice. It makes sure your script won’t accidentally write into the wrong column. It’s not a substitute for proper backups since you have backups, right?

Now that you know the basics of using python with Google sheets, you can go ahead and create something cool!



0 comments:

Post a Comment