In part 2 of the Google Sheets API in Python series, we will learn how to use Google Sheets API to create a Google Sheets file along with configuring some useful default settings such as auto-calculation setting, time zone, default 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
FOLDER_PATH = r'<Clent Secret File 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)
sheets_file1 = service.spreadsheets().create().execute()
# dict_keys(['spreadsheetId', 'properties', 'sheets', 'spreadsheetUrl'])
print(sheets_file1)
print(sheets_file1['spreadsheetUrl'])
print(sheets_file1['spreadsheetId'])
print(sheets_file1['sheets'])
print(sheets_file1['properties']) # Google Sheets information
"""
To specify Google Sheets file basic settings and as well as configure default worksheets
"""
sheet_body = {
'properties': {
'title': 'My first Google Sheets',
'locale': 'en_US', # optional
'autoRecalc': 'ON_CHANGE', # calculation setting #https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#RecalculationInterval
'timeZone': 'America/Los_Angeles'
}
,
'sheets': [
{
'properties': {
'title': 'Sales South'
}
},
{
'properties': {
'title': 'Sales North'
}
}
]
}
sheets_file2 = service.spreadsheets().create(body=sheet_body).execute()
print(sheets_file2['spreadsheetUrl'])
print(sheets_file2['spreadsheetId'])
print(sheets_file2['sheets'])
print(sheets_file2['properties']) # Google Sheets information
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