Getting Started | Google Sheets API in Python (Part 1)

Google Sheets API is one of the most useful and versatile Google APIs currently, giving the Sheets API is on version 4, the API has evolved coming from a long way. At the same time, because there are so many layers and properties involved, the Sheets API can take a bit of time to learn and also not the easiest API to work with. But once you get proficient with using Google Sheets API, you should be able to fully automate most of your spreadsheet work.

In part 1 of the Google Sheets API in Python tutorial series, we will be learning:

1) important information when using Google Sheets API (Quota limit, authorization, scopes, enable Google Sheets API)

2) how to create Google Sheets API service instance to connect to the endpoint

Google Sheets API is an API service provided by Google allowing you to read, write, format data, create a chart, and more, programmatically.

---

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:

import os
from Google import Create_Service

FOLDER_PATH = r'<Folder Path>'
CLIENT_SECRET_FILE = os.path.join(FOLDER_PATH, 'Client_Secret.json')
API_SERVICE_NAME = 'sheets'
API_VERSION = 'v4'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

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

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