Created
October 22, 2020 20:51
-
-
Save calexandrepcjr/93b1340633694d290ea372ffc0cd827a to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
""" | |
GAPI Sheets module improves the Gapi sheets usage | |
Turn on the credentials in your account: | |
https://developers.google.com/sheets/api/quickstart/python | |
Put the redirect uri as http://localhost:9999/ | |
""" | |
from __future__ import print_function | |
import os.path | |
import pickle | |
from google.auth.exceptions import RefreshError | |
from google.auth.transport.requests import Request | |
from google_auth_oauthlib.flow import InstalledAppFlow | |
from googleapiclient.discovery import build | |
def gpi_auth_sheets(_credentials_file, _scopes=['https://www.googleapis.com/auth/spreadsheets.readonly']): | |
# The file token.pickle stores the user's access and refresh tokens, and is | |
# created automatically when the authorization flow completes for the first | |
# time. | |
creds = None | |
if os.path.exists('../token.pickle'): | |
with open('../token.pickle', 'rb') as token: | |
creds = pickle.load(token) | |
# If there are no (valid) credentials available, let the user log in. | |
if not creds or not creds.valid: | |
if creds and creds.expired and creds.refresh_token: | |
creds.refresh(Request()) | |
else: | |
flow = InstalledAppFlow.from_client_secrets_file( | |
_credentials_file, _scopes) | |
creds = flow.run_local_server(port=9999) | |
# Save the credentials for the next run | |
with open('../token.pickle', 'wb') as token: | |
pickle.dump(creds, token) | |
return creds | |
def gapi_make_service(_credentials_file='credentials.json', _scopes=None): | |
if _scopes is None: | |
_scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'] | |
return { | |
'credentials': _credentials_file, | |
'scopes': _scopes, | |
'service': build('sheets', 'v4', credentials=gpi_auth_sheets(_credentials_file, _scopes)) | |
} | |
def gapi_make_range(_sheet_name): | |
return _sheet_name + '!A1:Z' | |
def gapi_load_sheet(_sheet_id, _sheet_name, _service_dict): | |
# Call the Sheets API | |
sheet = _service_dict["service"].spreadsheets() | |
result = sheet.values().get(spreadsheetId=_sheet_id, | |
range=gapi_make_range(_sheet_name)).execute() | |
values = result.get('values', []) | |
return values | |
def gapi_set_service_type_values(_sheet_id, _sheet_name, _values, _service_dict): | |
body = { | |
'values': _values, | |
} | |
# Avoids BrokenPipeError after long wait | |
_retries = 0 | |
while _retries < 3: | |
try: | |
result = _service_dict["service"].spreadsheets().values().update( | |
spreadsheetId=_sheet_id, | |
range=gapi_make_range(_sheet_name), | |
body=body, | |
valueInputOption="USER_ENTERED" | |
).execute() | |
except (ConnectionError, RefreshError, AttributeError): | |
_service_dict["service"] = gapi_make_service(_service_dict["credentials"], _service_dict["scopes"]) | |
_retries += 1 | |
return result.get('updatedCells') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment