Get Data of Google Sheets using Python Pandas

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.

create a new project google developer console
Step 2

Enter any project name and create it.

data science learner sheets google project
Step 3

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

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.

enable the api
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.

create credentials for the google sheets

click on the api key

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

reading the sheet using pandas

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

 

 
Thank you For sharing.We appreciate your support. Don't Forget to LIKE and FOLLOW our SITE to keep UPDATED with Data Science Learner