How to read Google Sheet Data using python (Google Sheet API call with Python)?
Hi Everyone, hope you are doing good. In this article, we are going to walk through python logic to extract/read data from google sheets using Sheet APIs. This knowledge will help you integrate Google Sheet ( Automate data export from google sheet to application) in your work project or college project.
Before diving into implementing the Python code for Google Sheet API.
You need to have some credentials and set up before using Google Sheet API with python. Refer to the Getting Started with Google Sheet API.
Without wasting any time let’s get started.
Table of Contents:
- Create a JSON File
- Read Credentials from JSON file.
- Custom Module for OAuth Authentication.
- Get Sheet IDs Programmatically.
- Read Sheet Data Programmatically.
- Full Python Script.
Create a JSON File to Store Credentials:
It is always preferred to have a JSON file to store authentication-related credentials like – access token, client id, etc. This way it’s very easy to maintain and update. For the coming python code to run, save the below JSON format as “sheet_api_cred.json”.
{
"client_id":"Replace with Client ID",
"client_secret":"Replace with Client Secret",
"access_token":"Replace with Access Token",
"refresh_token":"Replace with Refresh Token"
}
Read Credentials from JSON File:
In order to authenticate we first need to read data from the JSON file. Below is the code which will read the needed data from the above-created JSON file. It’s easy code, do not just copy, you won’t learn anything – put some effort to understand the working of logic. Save the below code as g_sheet_main.py.
Note: This file will be run directly and it will initiate a call for OAuth authentication and API call as well.
#!/usr/local/bin/python3
#Python3 ./src/sheet_main_logic.py
import getopt
import sys
import json
from datetime import datetime, timedelta
if __name__ == '__main__':
try:
timestamp = datetime.strftime(datetime.now(),'%Y-%m-%d : %H:%M')
print("DATE : ",timestamp)
print("Sheet process Started")
#Read the Credentials from the JSON file.
sheet_cred = "./sheet_cred.json"
sheet_cred= open(sheet_cred, 'r')
cred_json = json.load(sheet_cred)
client_id = cred_json["client_id"]
client_secret = cred_json["client_secret"]
access_token = cred_json["access_token"]
refresh_token = cred_json["refresh_token"]
print("Sheet Process Finished\n")
except:
print("Sheet processing Failed :", sys.exc_info())
Custom Module for OAuth Authentication:
In this step, you will see python code to authenticate applications on behalf of the user. I will suggest you understand this function and how this function is called because this will also help you understand how to build and use a custom python module. Save the below file as “g_sheet_authetication.py”.
#!/usr/local/bin/python3
import sys
#Packages needed for authentication
import httplib2 as lib2
import google.oauth2.credentials
from google_auth_httplib2 import AuthorizedHttp
import gspread
from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request
def ga_auth(access_token,refresh_token,client_id,client_secret):
try:
#This is consistent for all Google services
token_uri = 'https://accounts.google.com/o/oauth2/token'
#The real code that initialized the client
credentials = google.oauth2.credentials.Credentials(access_token,
refresh_token=refresh_token,
token_uri='https://accounts.google.com/o/oauth2/token',
client_id=client_id,
client_secret=client_secret)
service = gspread.authorize(credentials)
print("GA_AUTHENTICATION : authentication process finished successfully")
return service
except:
print("GA_AUTHENTICATION : authentication process Failed %s ",sys.exc_info())
After saving the above as “g_sheet_authentication.py“, you need to edit the “g_sheet_main.py” file and add the below code snippet.
First import above created authentication module.
from g_sheet_authentication import*
Second, call the authentication function from the above module.
#Function call for Authentication. service = ga_auth(access_token,refresh_token,client_id,client_secret)
Get Sheet IDs Programmatically :
Below and upcoming are the one of the most important logics. Below python logic’s main functionality to list out all Google sheets (getting sheet ID and sheet name).
I’m going to purposely miss out on a very small thing in this section which will be covered in the final code. The purpose is to make you go through the final python script so that you understand the code.
You need to add the below function to the g_sheet_main.py file if you want you can create a custom module for this and any data modulation step according to your requirement.
def get_sheet_list(service):
try:
tmp = service.openall()
data = []
for spreadsheet in tmp:
s = {'id': spreadsheet.id,
'name': spreadsheet.title}
data.append(s)
#print("\nget_sheet_list function finished successfully")
return data
except:
print("\nget_sheet_list function Failed %s ",sys.exc_info())
Call the above function from main by adding below code under if __name__ == ‘__main__’:
sheet_list = get_sheet_list(service)
print("\nSheet List : ",sheet_list)
Read Sheet Data Programmatically :
Below python logic’s main functionality to read data of provided Google sheets ID (Below code read Sheet1 tab data).
You need to add the below function to the “g_sheet_main.py” file if you want you can create a custom module for this and any data modulation step according to your requirement.
def get_sheet_data(sheet_name,sheet_id,service):
try:
#sheet = service.open(sheet_name).sheet1
sheet = service.open_by_key(sheet_id).sheet1
sheet_data = sheet.get_all_records()
sheet_data_df = pd.DataFrame(sheet_data)
# convert the 'Date' column to datetime format
sheet_data_df['Date']= pd.to_datetime(sheet_data_df['Date'])
return sheet_data_df
except:
print("\nget_sheet_data function Failed %s ",sys.exc_info())
call the above function by adding below code in main.
#Read the first Sheet from the list.
#you can read any or all sheets as you prefer.
sheet_name = sheet_list[0]['name']
sheet_id = sheet_list[0]['id']
sheet_data_df = get_sheet_data(sheet_name,sheet_id,service)
print("\n Sheet data : \n",sheet_data_df)
Full Python Script:
Final Python code after adding and calling all the needed functions. If you have followed all the above processes mindfully your g_sheet_main.py should be similar to the below code. Except some import will be missing from your file which I purposely left to cover in prior steps.
#!/usr/local/bin/python3
#Python3 ./src/sheet_main_logic.py
import sys
import json
import pandas as pd
from g_sheet_authentication import*
from datetime import datetime, timedelta
def get_sheet_list(service):
try:
tmp = service.openall()
data = []
for spreadsheet in tmp:
s = {'id': spreadsheet.id,
'name': spreadsheet.title}
data.append(s)
#print("\nget_sheet_list function finished successfully")
return data
except:
print("\n *** get_sheet_list function Failed %s ",sys.exc_info())
def get_sheet_data(sheet_name,sheet_id,service):
try:
#sheet = service.open(sheet_name).sheet1
sheet = service.open_by_key(sheet_id).sheet1
sheet_data = sheet.get_all_records()
sheet_data_df = pd.DataFrame(sheet_data)
# convert the 'Date' column to datetime format
sheet_data_df['Date']= pd.to_datetime(sheet_data_df['Date'])
return sheet_data_df
except:
print("\n *** get_sheet_data function Failed %s ",sys.exc_info())
if __name__ == '__main__':
try:
timestamp = datetime.strftime(datetime.now(),'%Y-%m-%d : %H:%M')
print("DATE : ",timestamp)
print("Sheet process Started")
#Read the Credentials from the JSON file.
sheet_cred = "./sheet_api_cred.json"
sheet_cred= open(sheet_cred, 'r')
cred_json = json.load(sheet_cred)
client_id = cred_json["client_id"]
client_secret = cred_json["client_secret"]
access_token = cred_json["access_token"]
refresh_token = cred_json["refresh_token"]
#Function call for Authentication.
service = ga_auth(access_token,refresh_token,client_id,client_secret)
sheet_list = get_sheet_list(service)
print("\nSheet List : ",sheet_list)
#Read the first Sheet from the list.
#you can read any or all sheets as you prefer.
sheet_name = sheet_list[0]['name']
sheet_id = sheet_list[0]['id']
sheet_data_df = get_sheet_data(sheet_name,sheet_id,service)
print("\n Sheet data : \n",sheet_data_df)
print("\n Sheet Process Finished\n")
except:
print("\n *** Sheet processing Failed :", sys.exc_info())
After running the above code by python3 ./g_sheet_main.py below screengrab is the output I get:
Congratulation! you have successfully build a python script to authenticate and read data from Google Sheet data using Google Sheet API. With little or no data-wrangling your application is ready to show data from google sheet directly. Hope I have made the entire process of Getting Started with Google Sheet API simple enough.
If you have any questions or comments feel free to reach me at.