Python Google Sheet Reader – Data Mastery with Python and Google Sheets

Google Sheets is a widely used tool for data storage and analysis. In this tutorial, we will create Python google sheets reader project that can read data from a Google Sheet and display it in a tabular format.

The project will be built using the Tkinter library for creating the GUI, the Pandas library for reading the data from Google Sheets, and the Pandastable library for displaying the data in a table.

Python Google Sheet Reader

The objective of this tutorial is to create a simple Python app that can read data from a Google Sheet and display it in a tabular format. By the end of this tutorial, you will be able to create a similar app of your own and use it to read data from any Google Sheet.

Prerequisite for Google Sheet Reader using Python

To follow along with this tutorial, you will need to have the following libraries installed on your computer:

• Tkinter
• Pandas
• Pandastable

To install these libraries you can run the following commands in the terminal.

pip install tk
pip install pandas
pip install pandastable

You will also need to have a Google Sheet with some data that you want to read.

Download the Python Google Sheets Reader Code

Please download the python Google Sheets Reader source code from the following link: Google Sheet Reader Python Project

Steps to Build Python Google Sheets Reader Project

Following are the steps to build the project

Step 1: Importing Libraries

The first step in creating our app is to import the necessary libraries. We will be using the Tkinter library for creating the GUI, the Pandas library for reading the data from the Google Sheet, and the Pandastable library for displaying the data in a table.

# Importing required libraries
import tkinter as tk
from tkinter import messagebox
import pandas as pd
from pandastable import Table

Step 2: Create the root window

The next step is to create the root window for the app using tk.Tk(). The window title, size, and background color are also set in this step.

# Creating root window and setting window title, size and background color
root = tk.Tk()
root.title("ProjectGurukul - Google Sheet Reader")
root.geometry("600x390")
root.resizable(width=False, height=False)
root.configure(background="#ffbfa8")

Step 3: Create the read_google_sheet() function

This function is used to read the Google Sheet and convert it into a pandas dataframe. The function first checks if the URL and Sheet name are not empty, and then gets the Google Sheet URL and Sheet name. It then creates the Google Sheet URL, reads the Google Sheet, and converts it into a pandas dataframe.

• Create the table window and frame: The next step is to create the table window and frame and add the table to it. The table is then shown in the frame.
• Run the main loop of the table window: This step is used to run the main loop of the table window so that the app can function properly.

# Creating function to read Google Sheet
def read_google_sheet():


   # Check if URL and Sheet name is not empty
   if url_entry.get() == "":
       messagebox.showerror("Error", "Please enter Google Sheet URL")
       return


   # Check if URL and Sheet name is not empty
   if sheet_entry.get() == "":
       messagebox.showerror("Error", "Please enter Google Sheet Name")
       return


   # Get Google Sheet URL and Sheet name
   google_sheet_ID = url_entry.get().split("/")[5]
   google_sheet_name = sheet_entry.get()


   # Create Google Sheet URL
   google_sheet_url = "https://docs.google.com/spreadsheets/d/{}/gviz/tq?tqx=out:csv&sheet={}".format(google_sheet_ID, google_sheet_name)
  
   # Read Google Sheet and convert it into a pandas dataframe
   df = pd.read_csv(google_sheet_url)


   # Create table window and set window title
   root = tk.Tk()
   root.title("ProjectGurukul - Google Sheet Reader")


   # Create table frame and add table to it
   frame = tk.Frame(root)
   frame.pack(fill=tk.BOTH,expand=1)


   # Create table and show it
   pt = Table(frame, dataframe=df)
   pt.show()


   # Run main loop of table window
   root.mainloop()

Step 4: Create the GUI elements

The next step is to create the various GUI elements such as labels, entries, and buttons. These elements are used to take input from the user and display the data.

# Creating title label and setting its properties
title_label=tk.Label(root)
title_label.configure(background="#03b332",foreground="#ffffff",font="Arial 18 bold",justify="center",text="ProjectGurukul - Google Sheet Reader")
title_label.place(x=0,y=0,width=602,height=50)


# Creating URL label and setting its properties
url_label=tk.Label(root)
url_label.configure(background="#ffbfa8",font="Arial 16",foreground="#333333",text="Enter Google Sheet URL", anchor="w")
url_label.place(x=30,y=80,width=230,height=30)


# Creating URL example label and setting its properties
eg_url = "e.g., https://docs.google.com/spreadsheets/d/1ZOjZme_mSIjSQQPno04gROFRh0VJlCX-TgxCEwXs72Q/edit#gid=1698173607"
url_eg_label=tk.Label(root)
url_eg_label.configure(background="#ffbfa8",font="Arial 12",foreground="#333333",text=eg_url, anchor="w", justify="left", wraplength=540)
url_eg_label.place(x=30,y=150,width=540,height=30)


# Creating URL entry and setting its properties
url_entry=tk.Entry(root)
url_entry.configure(background="#393d49",borderwidth="1px",font="Arial 14",foreground="#e4e4e4",justify="center")
url_entry.place(x=30,y=110,width=540,height=40)


# Creating Sheet name label and setting its properties
sheet_label=tk.Label(root)
sheet_label.configure(background="#ffbfa8",font="Arial 16",foreground="#333333",text="Enter Google Sheet Name", anchor="w")
sheet_label.place(x=30,y=200,width=230,height=30)


# Creating Sheet name example label and setting its properties
sheet_eg_label=tk.Label(root)
sheet_eg_label.configure(background="#ffbfa8",font="Arial 12",foreground="#333333",text="e.g. Sheet1", anchor="w")
sheet_eg_label.place(x=30,y=265,width=70,height=25)


# Creating Sheet name entry and setting its properties
sheet_entry=tk.Entry(root)
sheet_entry.configure(background="#393d49",borderwidth="1px",font="Arial 14",foreground="#e4e4e4",justify="center")
sheet_entry.place(x=30,y=230,width=540,height=40)


# Creating Read Sheet button and setting its properties
read_button=tk.Button(root)
read_button.configure(font="Arial 23 bold",justify="center",text="Read Sheet", command=read_google_sheet)
read_button.place(x=210,y=320,width=183,height=51)

Step 5: Run the main loop of the root window

This step is used to run the main loop of the root window so that the app can function properly.

# Run main loop of root window
root.mainloop()

Now our app is ready to read the google sheets. But read google sheets you need to follow the following instructions

Step 1: Create or Open a Google Sheet and add some data to it.

Step 2: Go to the Share option and click on Get Shareable Link and Give access to “Anyone with link can view”. This will allow anyone to read the sheet with that link.

Step 3: Now, Copy that link and paste it in the URL field of the GUI.

Step 4: Now, Enter the name of the sheet, e.g., Sheet1.

Now it will display the Google Sheet.

Python Google Sheet Reader Output

Summary

Yippee!! You have successfully created a Python Google Sheets Reader project. Now you can use it to read Google sheets without needing to open the browser. However, this is not the end. You can make modifications to the code according to your liking and make a more advanced version of it like making changes in google sheets by editing in the reader and many more. Hope you liked this tutorial.