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

python excel excel-vba

Bootstrap 5 Complete Course with Examples

Bootstrap 5 Tutorial - Bootstrap 5 Crash Course for Beginners

Nest.JS Tutorial for Beginners

Hello Vue 3: A First Look at Vue 3 and the Composition API

Building a simple Applications with Vue 3

Deno Crash Course: Explore Deno and Create a full REST API with Deno

How to Build a Real-time Chat App with Deno and WebSockets

Convert HTML to Markdown Online

HTML entity encoder decoder Online

Excel VBA Tutorial for Beginners 17- Delete Cells using VBA in MS Excel

In this MS Excel VBA video, we are going to see that how can we delete cells in excel using the VB code in the VBA editor and the Overview of formulas in Excel. We will see Basic Excel formulas & functions with examples .

Excel VBA Tutorial for Beginners 16 - Clear Cells in Excel using VBA

In thi MS Excel VBA video, we are going to look at how can we use the VB code to clear up cells in MS Excel. The clear cells feature of MS Excel is used for the purpose. Using this feature we can easily clear cell contents, cell formatting, cell comments and hyperlinks as well

Excel VBA Tutorial for Beginners 20 - Column Width Manipulation in Excel VBA

In this MS Excel VBA video, we are going to see how can we use the VB code to manipulate the Column Width of Cells in MS Excel. So we are going to use two methods and explore them in detail, along with this we aare also going to see that what are the possible values for the width of columns

Excel VBA Tutorial for Beginners 10 - Background Colors in Excel VBA

In this Excel VBA video, we are going to see the usage of With Block in Excel VBA. Using with block, we can reuse and rewrite multiple code lines. Also we are going to look at the interior property in brief as well, which allows us to set background colors and background gradient as well

Excel VBA Tutorial for Beginners 11 - Excel VBA PasteSpecial Method

In this Excel VBA video, we are going to look at the usage of the Paste Special function in Excel using the VB code. Paste Special function helps us to use the paste function in different formats such as Pasting Cell Width, Pasting Text only, Pasting Cell Formats.