From last Google Sheets API in Python lesson, we learned how to write data to Google Sheets using Google Sheets API. In this lesson, we will be learning how to use Google Sheets API to read data from a Google Sheets file in Python.
Buy Me a Coffee? Your support is much appreciated!
PayPal Me: https://www.paypal.me/jiejenn/5
Venmo: @Jie-Jenn
References
Google Sheets Documentation: https://developers.google.com/sheets/api
Google Sheets Scopes: https://developers.google.com/sheets/api/guides/authorizing
Install Google Client Library:
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
Source Code:
from Google import Create_Service
CLIENT_SECRET_FILE = 'client_secret.json'
API_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
service = Create_Service(CLIENT_SECRET_FILE, API_NAME, API_VERSION, SCOPES)
# https://developers.google.com/sheets/api/samples/data
spreadsheet_id = '11s3zkBvyn7NKNhiS9uuHESP9befW_sVv-x3YszyY7C8'
sheet_id = '44602522'
"""
Example 1. Value Must be Greater than 50
"""
# request_body = {
# 'requests': [
# # Rule #1: Allowing Value > 50
# {
# 'setDataValidation': {
# 'range': {
# 'sheetId': sheet_id,
# 'startRowIndex': 3,
# 'endRowIndex': 16,
# 'startColumnIndex': 1,
# 'endColumnIndex': 2
# },
# 'rule': {
# 'condition': {
# 'type': 'NUMBER_GREATER',
# 'values': [
# {
# 'userEnteredValue': '50',
# }
# ]
# },
# 'inputMessage': 'Value must be greater than 50',
# 'strict': True
# }
# }
# }
# ]
# }
# response = service.spreadsheets().batchUpdate(
# spreadsheetId=spreadsheet_id,
# body=request_body
# ).execute()
"""
Example 2. Date Range Validation
"""
request_body = {
'requests': [
{
'setDataValidation': {
'range': {
'sheetId': sheet_id,
'startRowIndex': 3,
'endRowIndex': 16,
'startColumnIndex': 2,
'endColumnIndex': 3
},
'rule': {
'condition': {
'type': 'DATE_BETWEEN',
'values': [
{
'userEnteredValue': '2019-01-01'
},
{
'userEnteredValue': '2019-12-31'
}
]
},
'inputMessage': 'Date value must be between 1/1/2019 and 12/31/2019',
'strict': False
}
}
}
]
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=spreadsheet_id,
body=request_body
).execute()
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
def Create_Service(client_secret_file, api_name, api_version, *scopes):
print(client_secret_file, api_name, api_version, scopes, sep='-')
CLIENT_SECRET_FILE = client_secret_file
API_SERVICE_NAME = api_name
API_VERSION = api_version
SCOPES = [scope for scope in scopes[0]]
print(SCOPES)
cred = None
pickle_file = f'token_{API_SERVICE_NAME}_{API_VERSION}.pickle'
# print(pickle_file)
if os.path.exists(pickle_file):
with open(pickle_file, 'rb') as token:
cred = pickle.load(token)
if not cred or not cred.valid:
if cred and cred.expired and cred.refresh_token:
cred.refresh(Request())
else:
flow = InstalledAppFlow.from_client_secrets_file(CLIENT_SECRET_FILE, SCOPES)
cred = flow.run_local_server()
with open(pickle_file, '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
def convert_to_RFC_datetime(year=1900, month=1, day=1, hour=0, minute=0):
dt = datetime.datetime(year, month, day, hour, minute, 0).isoformat() + 'Z'
return dt