Read Data From Google Sheets using Python

In this project, we will read the data from Google Sheets using Python. In addition to this, we will create a GUI window where this data will be displayed.

The user will have the option to enter the name of the google sheet that the user wants to read. So let’s get started.

Project to Read Data From Google Sheets using Python Details

The objective of the project is to create a GUI Window that will display data. This displayed data is read from a google sheet. For creating the GUI Window, we are using the Tkinter Module. And we are going to use the gspread library and oauth2client.service_account to create a connection between the python program and the google sheet and display the content.

Project Prerequisites

We will have to create a project and do some setup before going to the code. Let’s look at the setup we need to make step by step.

1. Create a project – Let us create a project in google drive. You need to give a suitable name to your project

2. Enable the APIs – Now we will enable two APIs – Google drive API and Google Sheets API. Search these and click on the enable button. This is how an enabled api looks like

3. Create Credentials – Click on create credentials and create a Json Key

4. Create a Json Key – As soon as we create a Json key, a json file gets downloaded automatically to the system. Save this json file where the code is saved already

5. Access to the google sheet – Now you need to go to the google sheet and give access to the client_email which is given in the json file.

We are done with the initial setup here.

Now for the coding part, we need to import these modules using the commands given along with them.

• Tkinter Module – pip install tkinter
• Gspread Module – pip install gspread

Now that we are done with installing all the modules. Let’s have a look at the code.

Download Project Source Code

For the implementation of Read Data From Google Sheets using Python, please download the code from the following link: Python Read Data From Google Sheets Project

Steps to Read Data From a Google Sheet using Python

These are the steps to build this project.

1. Importing the required modules
2. Creating the GUI Window
3. Reading the Data
4. Widgets – Button, Entry, and Message.

Let’s have a look at each step in detail

1. Importing the required Libraries and Modules:

#importing libraries
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import tkinter as tk
from tkinter import *

• Tkinter Module – This module helps us to create GUI for our project. Tkinter Module has many inbuilt functions like Button(), Entry(), Label() etc which will be used to create different widgets in our GUI window.
• Gspread – Gspread is an API for Google Sheets. This will help us open and read the contents of the Google Sheet.
• ServiceAccountCredentials – This gives us access and makes a copy of the credentials.

2. Creating a GUI Window:

root = Tk()
root.geometry('600x650')#geometry of window
root.title('DataFlair')#title for window
Label(root,text='Lets Display the Content of This File',font='arial 10').pack()

• Now we are going to create the GUI Window for our project. Using the Tk() method, we have created a window and given it the name root.
• After creating the window, we have specified the size and given a title to our window using the geometry() and title() functions respectively.
• Label() – Label is a widget that helps us to display a text on our window. Inside a Label() function, we can define the size, font, color, background color, foreground color etc of the text we are displaying.

3. Reading the Data from Google Sheet:

def read_the_sheet():
    sheet_to_display=s.get()#get the value of s
# use creds to create a client to interact with the Google Drive API
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    creds = ServiceAccountCredentials.from_json_keyfile_name('json file.json', scope)
    client = gspread.authorize(creds)
# Find a workbook by name and open the first sheet
# Make sure you use the right name here.
    sheet = client.open(sheet_to_display).sheet1
# Extract and print all of the values
    display_record = sheet.get_all_records()
    for i in display_record:
        Message(root,text=i).pack()#display the details in sheet

• We have created a function that will be triggered and will read the data from the Google Sheet.
• get() – Using the get method we get the name of the google sheet entered by the user and save it in another variable (sheet_to_display).
• Scope will define the scope of our application. from_json_keyfile_name() application helps us adding the json file and adding the credentials to the code.
• authorize() – Here we authorize the sheet. After these steps, we have established a connection between the client and the google sheet.
• open() – After establishing the connection, using the open() function we are going to open the sheet.
• get_all_records() – Using this method, we read all the details of the file.
• To display the details in the file we make a loop and using the Message() we display the record in the google sheet row-wise.

4. Create Entry and Button:

 
Entry(root,textvariable=s).pack()#entry field
Button(root,text='Display',bg='red',command=read_the_sheet).pack()#button widget on the window

Entry() – This method will help us create an Entry Field where the user will enter the name of the google sheet that needs to be read. Textvariable = s means that whatever value is entered by the user is saved in the variable s.

Button() – This method will help us create a Button on the Window. Whenever this button is clicked the function to read data is triggered. Same as in the Label, we can specify the font, size, color etc of the button.

pack() – To display each widget of this project we use the pack() method. The pack() method is used to display without specifying the x and y coordinates.

We have finally completed the project. Now let us have a look at the output.

Python Read Data From Google Sheets Output

Summary

We have successfully Read Data from the Google Sheets using Python. For creating the GUI of the project we used Tkinter Module. And we have done a little setup and using oauth2 library, we have established connection with the google sheet. In this way now we know how to read data from a google sheet using Python.