From the last lesson, we learned how to create a new Google Sheets file using Google Sheets API. In this lesson, we are going to learn how to write data to existing worksheets.


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

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)

spreadsheet_id = '<Spreadsheet Id>'
mySpreadsheets = service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()

"""
values.pdate method
"""
worksheet_name = 'Sales North!'
cell_range_insert = 'B2'
values = (
    ('Col A', 'Col B', 'Col C', 'Col D'),
    ('Apple', 'Orange', 'Watermelon', 'Banana')
)
value_range_body = {
    'majorDimension': 'ROWS',
    'values': values
}

service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id,
    valueInputOption='USER_ENTERED',
    range=worksheet_name + cell_range_insert,
    body=value_range_body
).execute()



service.spreadsheets().values().clear(
    spreadsheetId=spreadsheet_id,
    range='Sales North'
).execute()


worksheet_name = 'Sales North!'
cell_range_insert = 'B2'
values = (
    ('Col A', 'Col B', 'Col C', 'Col D'),
    ('Apple', 'Orange', 'Watermelon', 'Banana')
)
value_range_body = {
    'majorDimension': 'COLUMNS',
    'values': values
}

service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id,
    valueInputOption='USER_ENTERED',
    range=worksheet_name + cell_range_insert,
    body=value_range_body
).execute()


"""
values.append
"""

worksheet_name = 'Sales North!'
cell_range_insert = 'B2'
values = (
    ('Col E', 'Col F', 'Col G', 'Col H'),
    ('Toyota', 'Honda', 'Tesla', 'BMW')
)
value_range_body = {
    'majorDimension': 'COLUMNS',
    'values': values
}

service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id,
    valueInputOption='USER_ENTERED',
    range=worksheet_name + cell_range_insert,
    body=value_range_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