Accessing Google Spreadsheet Data using Python

Accessing Google Spreadsheet Data using Python

If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’. Well, Not so fast.

If you’re building a simple internal app and you might probably be thinking that ‘I’m going to need a database now right!’. Well, Not so fast.

As you all are familiar with importing, exporting and manipulating comma separate files (CSV) using Python, Hereby in this article I’m going to show you the step by step guide to access Google Spreadsheets on the cloud using Python.

As the very first thing, go to Google API Manager by simply googling it and go to https://console.developers.google.com/

https://console.developers.google.com/

To kick things off first create a new project.

Figure 1.0: Creating a new Project

I’ll name my project as ‘Telemedicine’ since we will be working with a spreadsheet which includes all the tweets related to Telemedicine hashtags which I extracted earlier (Click here to see how I extracted tweets using hashtags). Define a suitable project name according to your dataset on the cloud then click CREATE to initiate the project. (You don’t have to worry about the Location* below the project name)

Figure 1.0: Define a suitable project name according to your dataset

OKAY. The first part is done, Now go to API Library and search for Google Drive.

Figure 2.0: Search for Google Drive in API Library

Then add Google Drive API to our project which will allow us to access spreadsheet inside of Google Sheets for our account.

Figure 2.1: Click on Enable

Once that’s added, we need to create some credentials to access the API so click on Add Credentials on the next screen you see after enabling the API.

Figure 3.0: Click on Create Credentials to initialize credentials to access the API

Since we’ll be accessing the API using a web server, We’ll add the Web Serveroption on this page and give access to Application Data and tell them that you’re not running your application on either GCE or GAE by selecting the option ‘No, I’m not using them’ then click on the button below.

Figure 3.1: Creating credentials

Next, we will create a service account named Employees and assigned it the role Project Editor which will allow it to access and edit all the data within the API. Clicking continue will generate a JSON file that I will rename and add it to the project as Telemedicine_secret.json.

Figure 3.2: Creating service account credentials

Then open the JSON file in a text editor (I prefer Atom) :)

Inside the file, you can locate an email address property called “client_email”, if we copy that and take it over to our spreadsheet on the cloud, we can share that particular spreadsheet with the email address we provide to give us access to it from the API.

Figure 4.0: Copy the email address property client_email

Figure 4.1: Paste that copied email as a shared email address

Reading spreadsheet data with Python

Let’s move into the terminal to install gspread and oauth2client packagesand wait till all components get installed.

$ pip install gspread oauth2client

Then I’m going to create a new python file called spreadsheet.py in my favorite editor ATOM and write the following code to import gspread and ServiceAccountCredentials from oauth2client .

import gspread
from oauth2client.service_account import ServiceAccountCredentials
  1. Then, we have to define the scope and create credentials using that scope and the content of employees_secret.json file.
  2. Then I’ll create a gspread client authorizing it using those credentials.
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('employees_secret.json', scope)
client = gspread.authorize(creds)


3. NOW, We can access our google sheets so we’ll call a client.open and pass it the spreadsheet name and getting access to sheet1

sheet = client.open('telemedicine_data').sheet1

4. Now we can set our employees equal to all of the records inside that sheet and print them out to the terminal.

telemedicine = sheet.get_all_records()
print(telemedicine)

Let’s go the terminal try to run our program and we’ll get a glorious list of perfectly formatted content like in the image below (See the highlighted text and you can find all the columns in our dataset).

python spreadsheet.py

Figure 5.0: Display results as a list

Whoa! It wasn’t what you’re expecting, wasn’t it? Well, trust me I got the perfect solution for that!

We can clean up the result by using pprint module, using that we can create a prettyprinter that we can use to display the result and its a much nicer way to display the output.

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pprint
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
creds = ServiceAccountCredentials.from_json_keyfile_name('employees_secret.json', scope)
client = gspread.authorize(creds)
sheet = client.open('employee_reviews').sheet1
pp = pprint.PrettyPrinter()
employees = sheet.get_all_records()
pp.pprint(employees)



Figure 5.1: Formatted results using pprint

If you want to go through the full documentation of gspread click here and in the meantime I’ll follow you up with these cool tricks.

Filtering Data

#to get all the values inside the file
sheet.get_all_values()
#to get exact row values in a second row (Since 1st row is the header)
sheet.row_values(2)
#to get all the column values in the column 'place'
sheet.col_values(16)
#to extract a particular cell value
sheet.cell(1, 1).value



Figure 6.0: sheet.col_values(16) to get all the row values in column 16 which is ‘place’


Figure 6.1: sheet.row_values(2) to get all the first row values


OK! Reading is all done. YES! But, Can we do more? Oh Yes!



Insert / Update and Delete from Spreadsheet

And also, you can enter a random row and can give values to each cell by separating each word using double quotations.

row = ["I'm","inserting","a","new","row","into","a,","Spreadsheet","using","Python"]
index = 3
sheet.insert_row(row, index)

Figure 6.2: Spreadsheet after inserting our new row

Furthermore, It’s also possible to alter data inside a specific cell. Look at how it is done, I’ll change the header column name ‘id’ to ‘telemedicine_id’:

sheet.update_cell(1, 1, "telemedicine_id")

Figure 6.3: After updating the header name which is cell(1,1)


Finally, In conclusion to this article, I’m going to wrap up proving that it is not essential to create databases to simple internal apps. Spreadsheets might be your best answer!

You can access the full code here.

Photo by Paul Gilmore on Unsplash


Thank you!

If you liked this post, share it with all of your programming buddies!

Follow me on Facebook | Twitter


Learn More

Complete Python Bootcamp: Go from zero to hero in Python 3

Machine Learning A-Z™: Hands-On Python & R In Data Science

Python and Django Full Stack Web Developer Bootcamp

Complete Python Masterclass

The Python Bible™ | Everything You Need to Program in Python

Python Tutorial for Beginners (2019) - Learn Python for Machine Learning and Web Development

MongoDB with Python Crash Course - Tutorial for Beginners

Learning Python: From Zero to Hero

A Complete Machine Learning Project Walk-Through in Python

MySQL Databases With Python Tutorial

An A-Z of useful Python tricks

Angular 9 Tutorial: Learn to Build a CRUD Angular App Quickly

What's new in Bootstrap 5 and when Bootstrap 5 release date?

Brave, Chrome, Firefox, Opera or Edge: Which is Better and Faster?

How to Build Progressive Web Apps (PWA) using Angular 9

What is new features in Javascript ES2020 ECMAScript 2020

Top Python Development Companies | Hire Python Developers

After analyzing clients and market requirements, TopDevelopers has come up with the list of the best Python service providers. These top-rated Python developers are widely appreciated for their professionalism in handling diverse projects. When...

To VBA and beyond - building a RESTful backend using plain Microsoft Excel macros

When my coworkers and I discussed backend technologies for an upcoming project, someone jokingly mentioned Excel as people widely misused it as a terrible database replacement. Although we settled for .NET, the idea of using Excel as a backend fascinated me. Since I just recently finished my bachelor's thesis and had some spare time, I thought I'd give it a shot and see how far I'd get.

Python GUI Programming Projects using Tkinter and Python 3

Python GUI Programming Projects using Tkinter and Python 3