How to access Google Sheets using Python and gspread

Published on 2020-10-20, by Javed Shaikh

Subscribe for new article
*No spam. unsubscribe at anytime

Hello everyone, In this post I am going to show how we can access and update Google spreadsheet using Python and an excellent library gspread. With just few lines of codes we can create a new or use existing sheet and do the operation such as add, update or delete. Another cool thing is you can convert this spreadsheet into pandas dataframe using just one line. So lets get started..


  • Google developer account to create api credentials
  • Google service account to access spreadsheet programmatically
  • gspread - Python library
  • oauth2client - Python library

Create Google service account

Step 1: First you need to signup for a Google developer account if you have not already. Visit Google developer signup page at to create your account.

Step 2: Create service account Since we will be accessing spreadsheet using programs, we need to create service account and credentials for it. So login to your developer account and create new project by clicking at the top left corner new project icon.

As shown below a new window will appear. Provide your project name and click create button

post image 6aUnnXGrg_wuvymb

Now once project is created, it will take you to the project page (If not click on small bell icon on the top right and go to the project page). It should look like below screen print

post image 5_wjZgUML_zdsknv

Now we need to enable APIs that we want to use. For this tutorial we need to enable api for Google drive and Google sheet. We need google drive api because your spreadsheet is going to be saved on your google drive. So click menu icon on top left corner and then go to 'APIs and Services' and then click Library and it will take you to the API Library page like below

post image isSLOy9_J_viwolf

Now search for Google Drive api and click enable

Once enabled it will take you to Google drive api page. Now click create credentials

To create credentials follow the steps as shown in below screenshot and click "what credentials do I need?"

post image UKnU5zR91_srj9fz

In the next page you need to provide service account name, service account id and role as editor since we want to do add and update operation on Google sheet programmatically.

post image Ut_7thcLL_fdqwp4

When you click continue in above step, a JSON file with authentication details will be created.

Save this file in your system.

At this point we have created the service account successfully. Now lets enable Google Sheet api using the same steps we used to enable Google drive api.

Congratulations! we have successfully created our service account to access Google spreadsheet using Python. Now lets create a sample spreadsheet so that we can use it for our Python program to access. Lets go to and create a sample sheet. I have create a sheet named Student for this example tutorial.

post image wz5lZcvkX_fbn3tb

To use this sheet with our python code we need to share the file with Google api client email ID. Open a spreadsheet and click Share button on top right corner of the spreadsheet. You can find the client email from the JSON file we just downloaded in the previous example.

Install Python libraries

Install these libraries so that we can add and update spreadsheet using python

pip install gspread
pip install oauth2client

Though these two libraries are enough for us to for this tutorial. However we can install pandas to view our data using Pandas's Dataframe

pip install pandas

Lets start coding

At this point we have created Google account and then enabled Google drive api and Google sheet api and created credentials and saved in our local drive as JSON file. We also created a sample spreadsheet and shared the sheet with client email provided in the JSON file. We also installed required Python libraries in the above step. So now lets get started to code 🙂

import gspread
from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd

With above code we imported the libraries we installed in the last step. Now before accessing the spreadsheet we need to authenticate using JSON file we downloaded

#Authenticate Google service account
gp = gspread.service_account(filename='Testapp-9f185f872dd9.json')

We need to provide the correct path of JSON file. Since I have copied this file to my project directory, so just name is enough.

Now lets open the spreadsheet 'Student' we created in one of the steps.

#Open Google spreadsheet
gsheet ='Student')

Since spreadsheet is opened, now we need to select the worksheet we want to access using below line

#Select worksheet
wsheet = gsheet.worksheet("Sheet1")

As shown above we have selected 'Sheet1' which is the first worksheet.

Retrieve all records from a worksheet

Now to retrieve the records or cell values we can use one of below functions

  • get_all_values() fetches all values from a worksheet as a list of lists.
  • get_all_records() fetches all values from a worksheet as a list of dictionaries.

Lets try one by one on Jupyter Notebook


Output of above statement on Jupyter notebook. As shown all records are fetched as list of dictionaries

[['Student', 'Course', 'Grade', 'Score'],
 ['John', 'Data Science', 'A', '480'],
 ['Amit', 'Cyber Security', 'B', '440'],
 ['Ronit', 'Data Science', 'A', '475'],
 ['Maya', 'Business Analytics', 'A', '481']]

Similarly for get_all_values()



[{'Student': 'John', 'Course': 'Data Science', 'Grade': 'A', 'Score': 480},
 {'Student': 'Amit', 'Course': 'Cyber Security', 'Grade': 'B', 'Score': 440},
 {'Student': 'Ronit', 'Course': 'Data Science', 'Grade': 'A', 'Score': 475},
 {'Student': 'Maya',
  'Course': 'Business Analytics',
  'Grade': 'A',
  'Score': 481}]

Using Pandas DataFrame. First we extracted all values from worksheet, then set the columns for dataframe which is nothing but first list of all_rows.

all_rows = wsheet.get_all_values()
columns = all_rows.pop(0)
df = pd.DataFrame(all_rows,columns=columns)

Here is the output of df

Student    Course    Grade    Score
0    John    Data Science    A    480
1    Amit    Cyber Security    B    440
2    Ronit    Data Science    A    475
3    Maya    Business Analytics    A    481

Retrieve cell values from a worksheet

To get a specific cell value use acell function. Below snippet will print "John"

cell_A2 = wsheet.acell('A2').value

To get the value using coordinates use cell function as shown below. This will also print "John"

cell = wsheet.cell(2,1).value

Creating another worksheet

To create another sheet on the same spreadsheet use add_worksheet function

another_sheet = gsheet.add_worksheet(title="AnotherSheet", rows="10", cols="10")

This will create another sheet named "AnotherSheet" with 10 rows and 10 columns

Deleting a worksheet

Use function del_worksheet to delete a worksheet. So below instruction will delete "AnotherSheet" we created just now in the last step


Updating cell value

To update cell values we can use update function like below Below operation will replace "John" with "David"

We can perform same operation using coordinates

wsheet.update('A2', 'David')

worksheet.update_cell(1, 2, 'David')

Insert a new row

With append_row, we can append a new row at the end. As shown below, we are adding a new row for new student.

wsheet.append_row(["Gina","Computer Science",'B',447])


As shown in above examples, gspread is an excellent and super easy to use library to access Google spreadsheet. Hopefully examples and steps shown above will help you build your apps using Google spreadsheet. I have been using gspread for one of my app to track my daily expenses using sms. I am going to post about this in my next story on how to build an expense tracker using gspread,Twilio and Flask and then deploying it on Heroku 🙂

About the Author

I am a backend system engineer working for a credit card issuer. I mostly work on C/C++ and assembler programs on IBM's TPF OS. However I love web development and keep trying my hands on Nodejs and Python when offwork

Connect with author

Related articles ...

How to auto change desktop wallpaper every minute using Python

We are going to build a CLI app using Python to change desktop wallpaper every given number of minutes. Every wallpaper downloaded from internet will be unique and our app will change the wallpaper based on the time we set.


How to get email alert when your website is down using shell and Python?

There are many ways to monitor and set alerts for your web server using third party apps but they don't come with free, be it Pingdom or Amazon's CloudWatch. In this post I am going to show how we can write and setup our own alert tool with just few lines of code


Files, Folders and Python

In this post we are going to see how we can use Python to find the size of any file and folder. We will check how many files we have inside a folder and how many of them are empty files and how to delete those empty files.


How to setup alarm for CPU usage using IFTTT ?

Monitoring CPU and memory usage are one of the top todo checklist for a backend engineer. Sometimes you wont even notice when your server is down due to high CPU usage unless you login and manually check the system.