Reading and Writing a Google Spreadsheet Using Python [GUIDE]

This post will be flowing you through how to use python with Google Spreadsheet. Reading, writing, deleting, updating and even finding a text in a Google Spreadsheet using the python programming language.

At the end of the post, We will build a sample application of this.

I may not have mention these before, but i’m a Fiverr seller. So even the sample application is from a past project done on the platform.

If you have any question, shoot it in the comment section .

So,

Without further ado, Let’s get to it.

Why Use Google Spreadsheet ?

Starting with the question – Why would i use google spreadsheet to store information, why can’t i just store it in a local spreadsheet.

The thing is any one who has used google spreadsheet before would probably never ask the above question.

Because:

There’s a lot of additional benefit in using Google spreadsheet.

Now doing it programatically, That’s usage on steroid.

The advantage is almost infinite.

Nevertheless, I will still give the newbies reasons why you should write your data to the Google cloud spreadsheet rather than your old junkie machine.

  1. Your data always stays up to date
  2. This will help keep your data secure as it’s all on the cloud
  3. Google’s artificial intelligence is available to speed up analysis.
  4. You can automate processes
  5. You can access your data from anywhere as it’s stored in the cloud

There are 2 Python modules required for this to work

  • oauth2client – To authenticate a Google user with a Google account.
  • gspread – To communicate, read and write to the google spreadsheet.

The Oauth2Client will be use to authorize Google and gspread will use this authorization instance to carry out the spreadsheet operation.

Both these module can be install with :

pip install oauth2client gspread

Setting up Authorization

Firstly, we need to setup authorization to the Google Spread sheet but how do we do this ?

we do that by authorizing our application, You just follow me as it’s pretty straight forward.

Create a New Project

Create a new project in the Google API Console by clicking Create Project and select “NEW PROJECT” from the modal

python google spreadsheet create project

Enter project name and click CREATE – in my case, project name is GeekySpread

python google spreadsheet name project

Activate the Required API

There are two API required to make all this work. The Google drive API and the Google spreadsheet API. They can be searched from the search box at the header box.

python google spreadsheet search gdrive

We need to select and Activate the Google Drive API

python google spreadsheet enable gdrive

And also Activate the Google Spreadsheet API as well as this is also needed.

python google spreadsheet enable spreadsheet api

If these API are not activated, no work can be done.

So, Immediately after the activation,

Create a Google Service Account

Create a New credential – Service account to be precise.

You should select “Credential” from the side bar the hit the button on the top bar, then select Service Account.

python google spreadsheet create credential

In the next page, select Google Drive API in the “which API are you using ?

You should choose Web Server in the Where will you be calling from Field.

Select Application Data

And, choose No, I’m not using them.

See the screenshot below on how it’s done.

python google spreadsheet create credential

On the next page, enter your service account name as whatever you like .

Select JSON for key type and click the Continue button

python google spreadsheet create credential

And we successfully created our service account

python google spreadsheet credential created successfully

A file will be automatically downloaded to your computer. Mine was something like GeekySpread-9b2kdjalblah.json

Keep the file safe, it will be used later for authorization.

Connecting to Google Spreadsheet

Now we have our file for authorization. I will rename our file to spready_tut.json to make things easy.

The Oauth2Client module will be use to authenticate

import gspread # import the gspread module to work with spreadsheet
from oauth2client.service_account import ServiceAccountCredentials # module for google authorization

scope = [ 'https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive'] 

google_cred_spread_file = 'spready_tut.json'
credential = ServiceAccountCredentials.from_json_keyfile_name(google_cred_spread_file, scope)
google_sheet = gspread.authorize(credential) # the google_sheet variable is what is needed from here to talk with the spreadsheet

One final thing before we start moving data in and out of our spreadsheet to make this whole thing work.

I will assume your spreadsheet has been created.

So, you need to share the spreadsheet with our service account email. The service account email/ID can be found in our authority json file spready_tut.json in my case under the client_email key

python google spreadsheet share

So, that’s it we can now connect to the Google Spreadsheet provided everything is done as it should.

Now we move on to reading from a spreadsheet.

Reading from Google Spreadsheet

Firstly, we start by acquiring handle to the workbook and the first sheet

sheet_name = 'geekytut' # this is the title of the SS we working with
workbook = gc.open(sheet_name)
sheet = workbook.sheet1 # we only working with the first sheet for now
sheet.get_all_values() # these returns all values in the spreadsheet as a lsit of list
name = sheet.acell('B2').value # this gets the value in the B2 cell
age = sheet.acell('E1').value # and this gets the value in the E1 cell

We can as well open our sheet by it’s key which can be extracted from the spreadsheet URL

sheet = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

As well as the sheet URL which you can just copy from the browser address bar.

sheet = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl') 

You should study the code snippet above, They are pretty straight forward.

And we have more

sheet.row_values(1) # this returns list of values in the first row only
sheet.col_values(1) # and this returns list of values in the first column only 
sheet.cell(1, 1).value # returns value of cell at index row 1 and col 1

As you might have guess, there’s a method to fetch a range of cells.

cell_list = wks.range('A1:B7')

The above code snippet fetches from cell A1 to cell B7

You know what ? We can even find a value in the spreadsheet.

Finding a particular text in the spreadsheet

cell = worksheet.find("Foo and bar")
print("Found something at R%sC%s" % (cell.row, cell.col))

The above snippet only searches for a single occurrence of the text. All occurrence can be found with:

list_of_cells = worksheet.findall("Foo and bar")

Moreover, There’s more to be explored in the official documentation.

Now, Let’s move on to writing into the Google spreadsheet using python.

Writing into Google Spreadsheet

Now, let’s write some data into the cloud. Like say for example a stock market scrape result.

row_data = ['This is for column 1', 'column 2', 'column 3']
row_index = 1 # we are using the second row in the spreadsheet, first is 0
sheet.insert_row(row_data, row_index) 

Additionally, we can update a specific cell instead of inserting.

 sheet.update_cell(1, 1, "The description has been altered") 

Or maybe perhaps we decided to delete the whole of the second row

sheet.delete_row(2) 

Let’s move on , guys.

A Sample Usage

In this section, we will create a sample application to use with this.

Thinking out loud, what do we create to communicate with Google spreadsheet using Python?

Then, I decided to create a project on Github and link to it from here instead of treating it in this section.

See: Scraping All currency value into Google Spreadsheet.

Conclusion.

Storing data in the cloud on Google Spreadsheet especially during Scraping operation seems like

Additionally, It can also serve as a database in the cloud. Just think outside the box.

Furthering more, You can read more on gspread from their documentation page.

Lastly, If you think i’m missing on somethings or you have contribution to this, Please drop a message in the comment section below and let me know what you think .

READ ALSO: How to Use Custom Prompts in Your Python Interpreter Shell

13 Comments

  1. adebayo abeeb
    • admin
  2. Briantrops
    • admin

Leave a Reply

Ad Blocker Detected

Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Refresh