Get Data of Google Sheets using Python Pandas : Plot the Line Chart

I know you must have worked inside the google sheets or saved your data inside google drive. But do you know that you can access and read google sheets using Pandas and Google APIs? Yes, you can. You can do all the analysis for the entire data in google sheets in python. In this entire tutorial, you will know how to get data from google sheets and do manipulation using Pandas Dataframe.

Before going to the coding part please note that you have to create an API key for accessing the google sheets. Just do all the steps given below.

Step 1

Go to Google Developer Console and click on the Create Project.

Step 2

Enter any project name and create it.

Step 3

Now scroll down to see Getting Started and click on Explore and Enable APIs

Step 4

Search for the sheet and Click on Google Sheets APIs

Then you will see the option to enable the API .Click on it.

Step 5

Create Credentials

Now its time to create an API key for accessing the sheets. Fill all the details to create and download the API key as a JSON file. You will use it for accessing the data in the excel sheet.

Steps by Steps to Read Google Sheets using Pandas

Step1: Import the necessary libraries

Here you will import these libraries that are required reading the sheets. These are oauth2client and gspread for authorization, and pandas for converting the Excel file to data frame. Matplotlib for Plotting the Line chart for the Close Price.

from oauth2client.service_account import ServiceAccountCredentials
import pandas as pd
import gspread
import matplotlib
import matplotlib.pyplot as plt

Step 2: Create Authorization for Accessing the Sheets

In this step, You will use the downloaded JSON API file for allowing the URL scope to get the token and authorize to read or write the file. Use the following code.

scope = [
'https://www.googleapis.com/auth/spreadsheets',
]

GOOGLE_SHEETS_KEY_FILE = 'data-science-learner-sheets.json'
credentials = ServiceAccountCredentials.from_json_keyfile_name(GOOGLE_SHEETS_KEY_FILE, scope)
gc = gspread.authorize(credentials)

Step 3: Read the sheets

After Authorising the Sheet API and now you can easily read any sheets. Just know the sheet id of the workbook ( Find it in URL) and get all the values. Use the code below.

sheet_id = "1cfKO0Ng1Au23Mero7u9O2hd12ddw6Kkqj8fYZITAjGE"
workbook = gc.open_by_key(sheet_id)
sheet = workbook.get_worksheet(0)
values = sheet.get_all_values()
data = pd.DataFrame(values[1:],columns=values[0])
data

You will get the output of the data like this. You can now manipulate anything with the data like in our example I am drawing the line chart for the close price of the Facebook Share.

close_price = data["Close"].astype(float)
close_price.plot(figsize=(20,10),title ="FACEBOOK SHARE PRICE")

Join our list

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

We respect your privacy and take protecting it seriously

Thank you for signup. A Confirmation Email has been sent to your Email Address.

Something went wrong.