Reading Data From Google Sheets | Google Sheets API in Python (Part 4)

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