gapi_sheets_lib.py

"""
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')