Google Sheets API Integration Using Python

Published On: 7 July 2021.By praveen kumawat.

Digital Engineering
General

Google Sheets API Integration Using Python

Introduction

Google Sheets is the free, web-based spreadsheet application available to anyone with a Google account or Gmail address. It has become a useful, feature-rich competitor to Excel.

Google sheets or spreadsheets are commonly used in every business and project. There are so many things that could be easily automated with help of google sheets API and python. It has its own API which is free, secure and easy to use. It has awesome documentation and support also.
This post will cover how to set up the latest Google Sheets API, v4, for Python. We’ll also cover how to extract data, write data in a Google Sheet range and some more amazing things about Google Sheets API

What is google sheets API?

The Google Sheets API lets you interact with Google Sheets without having to use the app directly.
For people who regularly deal with data sets to get work done, importing data into spreadsheets can get burdensome. The process takes up a lot of time since it’s often done manually, and it probably isn’t something you want to spend much of your day working on.

If you’re building out a quick CRUD app for e.g. internal use, Google Docs as a backend (consumed via JSON) is surprisingly powerful.

In python there are many ways to interact with google sheets using API, If you have basic requirement of reading and writing from sheet then gspread is an awesome library to do that. Here you can find how to start with gspread
(https://docs.gspread.org/en/v3.7.0/)
I prefer to do it by creating a service object using google-api-client because it gives more control over HTTP requests, API requests and request timeouts.

This API is useful for:

● Managing the worksheets in a Google Sheets file.
● Consuming the rows of a worksheet.
● Managing cells in a worksheet by position.
● Consuming data entered into a spreadsheet by a user.
● If you are planning to build a small application which requires lightweight and very less frequent used database then google sheet might be right pick for you

Steps to use google sheets api using python

Create Google Service Account:

To create a service account go to google console on GCP(Google Cloud Platform) https://console.cloud.google.com/ and follow these steps:-

1. Create a new project.

2. Search for the Google Drive API click enable API.

3. Create credentials for a OAuth 2.0 Client ID of type ‘Web application’ to access Application Data.

4. Download the JSON file.

5. Copy the JSON file to your code directory and rename it to client_secret.json

Have you created OAuth 2.0 Client ID and got client_secret.json file? Awesome, that’s all the setup needs to be done on GCP now we are good to go on python code.

Before that
Lets understand how google does secure authentication using OAuth 2.0 Client ID, access tokens and refresh tokens.

Authentication using OAuth 2

● client_secret.JSON includes client_id, client_secret and redirect_uris
● Retrieve an authorization code using scopes and client_id from client_secret.JSON
● Retrieve access token and refresh token using the authorization code, client_id, client_secret and redirect_uris

Process

● When you run the google api using python for the first time, the authorization process using your browser is launched. At that time, the script of Quickstart/Service-Object retrieves the authorization code using client_id and scopes, and then the access token and refresh token are retrieved using the authorization code, client_id, client_secret and redirect_uris.

● After the first run of the Quickstart, the access token is retrieved by the refresh token from token.pickle. By this, retrieving the authorization code using the browser is not required to do. So when there is token.pickle, client_secret.JSON is not required.

● If you want to change scopes and/or credentials of client_secret.JSON, the authorization process using the browser and retrieving the authorization code are required to do. For this, you have to remove token.pickle and authorize again. At that time, at Quickstart, client_secret.JSON is used again.

Create Google.py to create service object and execute any google API’s

Step 1:- Install the Google client library Using this command

pip install –upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

Step 2:- Move client_secret.JSON to your working directory

client_secret.json file that we have downloaded from GCP, move it to out working directory.

Step 3:- Configure Google.py

import pickle
import os
from google_auth_oauthlib.flow import Flow, InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload
from google.auth.transport.requests import Request
 
from pathlib import Path

 
BASE_DIR = Path(__file__).resolve().parent.parent
 
TOKEN_PATH = os.path.join(BASE_DIR, "GoogleCredentials")
 

def Create_Service(api_name, api_version, *scopes):  
   CLIENT_SECRET_FILE = os.path.join(TOKEN_PATH, 'client_secret.json')
   API_SERVICE_NAME = api_name
   API_VERSION = api_version
   SCOPES = scopes[0]
   cred = None
 
   pickle_file = 'token.pickle'
 
   if os.path.exists(TOKEN_PATH + '/token.pickle'):
       with open(TOKEN_PATH + '/token.pickle', 'rb') as token:
           cred = pickle.load(token)
           log_info.info(f"refresh token:{cred.refresh_token}")
 
   if not cred or not cred.valid:
       if cred and cred.expired and cred.refresh_token:
          
           cred.refresh(Request())
           log_info.info("refresh token found||token refreshed")
       else:
           flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)
           cred = flow.run_local_server(port = 34905)
           log_info.info("new token created")
 
       with open(TOKEN_PATH + '/token.pickle', 'wb') as token:
           pickle.dump(cred, token)
 
   try:
       service = build(API_SERVICE_NAME, API_VERSION, credentials=cred)
       print(API_SERVICE_NAME, 'service created successfully')
       return service
   except Exception as e:
       print('Unable to connect.')
       print(e)
       return None

import pickle

import os

from google_auth_oauthlib.flow import Flow, InstalledAppFlow

from googleapiclient.discovery import build

from googleapiclient.http import MediaFileUpload, MediaIoBaseDownload

from google.auth.transport.requests import Request

from pathlib import Path

BASE_DIR = Path(__file__).resolve().parent.parent

TOKEN_PATH = os.path.join(BASE_DIR, "GoogleCredentials")

def Create_Service(api_name, api_version, *scopes):

CLIENT_SECRET_FILE = os.path.join(TOKEN_PATH, 'client_secret.json')

API_SERVICE_NAME = api_name

API_VERSION = api_version

SCOPES = scopes[0]

cred = None

pickle_file = 'token.pickle'

if os.path.exists(TOKEN_PATH + '/token.pickle'):

with open(TOKEN_PATH + '/token.pickle', 'rb') as token:

cred = pickle.load(token)

log_info.info(f"refresh token:{cred.refresh_token}")

if not cred or not cred.valid:

if cred and cred.expired and cred.refresh_token:

cred.refresh(Request())

log_info.info("refresh token found||token refreshed")

else:

flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)

cred = flow.run_local_server(port = 34905)

log_info.info("new token created")

with open(TOKEN_PATH + '/token.pickle', 'wb') as token:

pickle.dump(cred, token)

try:

service = build(API_SERVICE_NAME, API_VERSION, credentials=cred)

print(API_SERVICE_NAME, 'service created successfully')

return service

except Exception as e:

print('Unable to connect.')

print(e)

return None

The Function Create_Service() does authentication using client_secret.json, build a service object using build() for any google API service and return it.

If you’re using more than one google API services than you can execute these API by passing all the scopes.

STEP 4:- Passing scopes, version, name and creating service object

Lets import Create_Service from Google.py and create a service object

from core.googleServices.Google import Create_Service
 
API_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/drive','https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/script.external_request']
service = Create_Service(API_NAME, API_VERSION, SCOPES)

from core.googleServices.Google import Create_Service

API_NAME = 'sheets'

API_VERSION = 'v4'

SCOPES = ['https://www.googleapis.com/auth/drive','https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/script.external_request']

service = Create_Service(API_NAME, API_VERSION, SCOPES)

There is always confusion if we should create separate service objects for all sheet operations or keep it common for all.
We should create separate service objects for all operations because in bulk API requests and too many concurrent request HTTP timeout issues may arise.

Now as we have created a service object so lets execute some basic read, write requests.

Reading a range from Sheet:-

service = Create_Service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)
Worksheet_id = "1DdIgHXCCwQGGfznsw1HUt1jaYsavKOxJlnWh0kwA-TBFE"
range_name ="Permit Determination Output!B8:D68"
result = service.spreadsheets().values().get(
               spreadsheetId=worksheet_id, range=range_names).execute()
sheet_output_data = result["values"]
print(sheet_output_data)

service = Create_Service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)

Worksheet_id = "1DdIgHXCCwQGGfznsw1HUt1jaYsavKOxJlnWh0kwA-TBFE"

range_name ="Permit Determination Output!B8:D68"

result = service.spreadsheets().values().get(

spreadsheetId=worksheet_id, range=range_names).execute()

sheet_output_data = result["values"]

print(sheet_output_data)

Worksheet id is unique id for every google sheet you can easily find it from google sheet url.
range_name is basically sheet/tab name + cells range.
result[“values”] is output data from sheet in the form of a 2X2 array/list.

Writing values to range:-

Range in the sheet is a 2×2 array so we need to prepare our data in the form of 2×2 array and that will be passed as values in api-json request, let’s take an example.

input_values_list = [
                   ['a','b','c'],
                   ['a','b','c'],
                   ['a','b','c']
                   ]
write_requests = [{
                   "range": "Sheet1!B7:D9",
                   "majorDimension": "ROWS",
                   "values": input_values_list
               }]
Body = {"valueInputOption": "RAW", "data": write_requests}
result = service.spreadsheets().values().batchUpdate(spreadsheetId=worksheet_id, body=Body).execute()

input_values_list = [

['a','b','c'],

['a','b','c']

]

write_requests = [{

"range": "Sheet1!B7:D9",

"majorDimension": "ROWS",

"values": input_values_list

}]

Body = {"valueInputOption": "RAW", "data": write_requests}

result = service.spreadsheets().values().batchUpdate(spreadsheetId=worksheet_id, body=Body).execute()

values().batchUpdate() is a function to execute multiple bulk update API requests(writing multiple ranges, removing rows, deleting sheets, hiding sheets etc)in one HTTP request or connection.
spreadsheet() has many functions which can help you based on your requirement, you find them here(https://developers.google.com/resources/api-libraries/documentation/sheets/v4/python/latest/sheets_v4.spreadsheets.html)

All these requests take very less time in executing even if you’re reading or writing large ranges. Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately.

Google cloud console has a dashboard also which helps you track errors, latency and request quota, if you’re using this API on a large scale then this might be helpful for you.

So, guys, this is all about google sheets API , if you have any confusion in understanding code or have any queries then we can discuss them in the comment section.

Recommended links
https://developers.google.com/sheets/api