Last active
June 12, 2020 00:36
-
-
Save anikkon/95d98d9cdc838fa5725a36a257e012bb 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
"""Script to automate insertion of values in Google Sheets. | |
Google Sheets API credentials are required that can be obtained at | |
https://console.developers.google.com/flows/enableapi?apiid=sheets.googleapis.com | |
Example usage: | |
client = GoogleSheetsClient('https://docs.google.com/spreadsheets/d/.../edit#gid=0', | |
'./client_secret.json') | |
client.set_cell_value('Column name', 'Row name', 'New value') | |
set_value = client.get_cell_value_formatted('Column name', 'Row name') | |
""" | |
import httplib2, os, re | |
from apiclient import discovery | |
from oauth2client import client, tools | |
from oauth2client.file import Storage | |
try: | |
import argparse | |
flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args() | |
except ImportError: | |
flags = None | |
### CHANGE THIS TO FIT YOUR APP ### | |
SHEET_NAME = 'Main' | |
### ### ### | |
SHEET_RANGE = SHEET_NAME + '!A1:ZZ100000' | |
APPLICATION_NAME = 'Google Sheets Master' | |
DISCOVERY_URL = 'https://sheets.googleapis.com/$discovery/rest?version=v4' | |
SCOPES = 'https://www.googleapis.com/auth/spreadsheets' | |
CLIENT_CREDENTIALS_FILE = 'google-sheets-master.json' | |
class GoogleSheetsClient: | |
def __init__(self, sheets_url, path_to_client_secret): | |
"""Args: | |
:param sheets_url: (str): URL of the Google Sheet to be processed. | |
:param path_to_client_secret: (str): path to client credentials json file, relative or absolute | |
If no credentials are found or they are invalid user is prompted for authentication | |
to to obtain the new credentials. | |
""" | |
try: | |
path_to_client_secret = self.__get_absolute_path(path_to_client_secret) | |
self.__spreadsheet_id = re.search('docs\.google\.com/spreadsheets/d/([a-zA-Z0-9-_]*)/', sheets_url).group(1) | |
except AttributeError: | |
raise ValueError('Invalid url. Doesn\'t look like a valid Google Sheets link.') | |
except IOError: | |
print('Client secret path invalid!') | |
raise | |
http = self.__get_credentials(path_to_client_secret).authorize(httplib2.Http()) | |
self.__service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=DISCOVERY_URL) | |
def set_cell_value(self, column_name, row_name, value, silent_mode=True): | |
"""Inserts value in a Google Sheet. | |
Args: | |
:param column_name: (str): Column name. | |
:param row_name: (str): Row name. | |
:param value: (str): Value to be inserted. | |
:param silent_mode: (bool): Whether log messages should be printed. | |
Returns: | |
(bool): The return value. True for success, False otherwise. | |
""" | |
service = self.__service | |
spreadsheet_id = self.__spreadsheet_id | |
cell_index = self.get_cell_index_formatted(column_name, row_name) | |
request_body = {'values': [[value]]} | |
result = service.spreadsheets().values().update( | |
spreadsheetId=spreadsheet_id, range=cell_index, | |
valueInputOption='USER_ENTERED', body=request_body).execute() | |
if result.get('updatedCells') == 1: | |
if not silent_mode: | |
print('Cell ', cell_index, 'updated with value ', value) | |
return True | |
else: | |
if not silent_mode: | |
print('Operation failed') | |
return False | |
def get_cell_value(self, column_name, row_name): | |
"""Gets value from a Google Sheet. | |
Args: | |
:param column_name: (str): Column name. | |
:param row_name: (str): Row name. | |
Returns: | |
(str): The cell value. | |
""" | |
columns, _, col_index, row_index = self.get_cell_with_context(column_name, row_name) | |
try: | |
value = columns[col_index][row_index] | |
except IndexError: | |
# Cell is empty and column is stripped to not include trailing empty cells | |
value = '' | |
except Exception: | |
print('This should not happen. Go fix your code!') | |
raise | |
return value | |
def get_cell_index_formatted(self, column_name, row_name): | |
"""Finds the index of the cell by the given column and row names. | |
Args: | |
:param column_name: (str): Column name. | |
:param row_name: (str): Row name. | |
Returns: | |
(str): Index of the cell value in format 'Sheet1![A-ZZ][0-100000]' | |
""" | |
_, _, col_index, row_index = self.get_cell_with_context(column_name, row_name) | |
return self.__format_cell_index_to_str(col_index, row_index) | |
def get_cell_with_context(self, column_name, row_name): | |
"""Finds the the cell by the given column and row names. Returns indices and data sheet. | |
Args: | |
:param column_name: (str): Column name. | |
:param row_name: (str): Row name. | |
Returns: | |
([[]]) : data sheet grouped by columns | |
([[]]) : data sheet grouped by rows | |
(str) : searched cell column index | |
(str) : searched cell row idnex | |
""" | |
service = self.__service | |
spreadsheet_id = self.__spreadsheet_id | |
rows = service.spreadsheets().values().get( | |
spreadsheetId=spreadsheet_id, range=SHEET_RANGE, | |
majorDimension='ROWS').execute().get('values', []) | |
columns = service.spreadsheets().values().get( | |
spreadsheetId=spreadsheet_id, range=SHEET_RANGE, | |
majorDimension='COLUMNS').execute().get('values', []) | |
if not rows or not columns: | |
raise Exception('Sheet is empty. No data to process.') | |
# Find column index by name | |
for index, row in enumerate(rows): | |
if row_name in row: | |
row_index = index | |
break | |
else: | |
raise ValueError('Invalid row name.') | |
# Find row index by name | |
for index, column in enumerate(columns): | |
if column_name in column: | |
col_index = index | |
break | |
else: | |
raise ValueError('Invalid column name.') | |
return columns, rows, col_index, row_index | |
def get_whole_sheet(self): | |
""" Retrieves the whole sheet by the specified URL. | |
Returns: | |
[[]] : 2D list Columns-Rows | |
""" | |
service = self.__service | |
spreadsheet_id = self.__spreadsheet_id | |
return service.spreadsheets().values().get( | |
spreadsheetId=spreadsheet_id, range=SHEET_RANGE, | |
majorDimension='COLUMNS').execute().get('values', []) | |
@staticmethod | |
def __format_cell_index_to_str(col_index, row_index): | |
"""Converts column and row names to a formatted cell index in format 'Sheet1![A-ZZ][0-100000]' | |
Args: | |
:param col_index: (str): Column index. | |
:param row_index: (str): Row index. | |
Returns: | |
(str): Formatted index of the cell value | |
""" | |
col_index += 1 | |
col_name = "" | |
while col_index > 0: | |
col_index, remainder = divmod(col_index - 1, 26) | |
col_name = chr(65 + remainder) + col_name | |
return SHEET_NAME + '!' + col_name + str(row_index + 1) | |
@staticmethod | |
def __get_credentials(client_secret): | |
"""Gets valid user credentials from storage. | |
If nothing has been stored, or if the stored credentials are invalid, | |
the OAuth2 flow is completed to obtain the new credentials. | |
Credentials stored at ~/.credentials/sheets.googleapis.google-sheets-master.json | |
Returns: | |
Credentials, the obtained credential. | |
""" | |
home_dir = os.path.expanduser('~') | |
credential_dir = os.path.join(home_dir, '.credentials') | |
if not os.path.exists(credential_dir): | |
os.makedirs(credential_dir) | |
credential_path = os.path.join(credential_dir, 'sheets.googleapis.' + CLIENT_CREDENTIALS_FILE) | |
store = Storage(credential_path) | |
credentials = store.get() | |
if not credentials or credentials.invalid: | |
flow = client.flow_from_clientsecrets(client_secret, SCOPES) | |
flow.user_agent = APPLICATION_NAME | |
if flags: | |
credentials = tools.run_flow(flow, store, flags) | |
else: # Needed only for compatibility with Python 2.6 | |
credentials = tools.run(flow, store) | |
print('Storing credentials to ' + credential_path) | |
return credentials | |
@staticmethod | |
def __get_absolute_path(path): | |
"""Converts provided file path to absolute path. Throws IOError if file doesn't exist. | |
Args: | |
:param path (str): Path to file. Absolute or relative. | |
Returns: | |
(str): Absolute path to file. | |
""" | |
if os.path.isabs(path) and os.path.isfile(path): | |
return path | |
elif path.startswith('~/'): | |
# Looks like home directory | |
path = os.path.expanduser(path) | |
else: | |
# Looks like project directory | |
path = os.path.abspath(path) | |
if os.path.isabs(path) and os.path.isfile(path): | |
return path | |
raise IOError('File not found') |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thank you. Its a really well written 💯