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 to read the sheets. These are oauth2client and gspread for authorization, and pandas for converting the Excel file to the data frame. Matplotlib for Plotting the Line chart for the Close Price.

If your system does not have oauth2client and gspread installed then you can use the below command to install them.

pip install oauth2client
pip install gspread

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")

 

Conclusion

These are steps to access and read Google Sheets using pandas in python. There are many use cases for Google Sheets. For example, if you have to datasheet that is dynamic then you can read it using Pandas and make your web or mobile application. Hope you have liked this tutorial. If you have any queries then you can contact us for more information.