def value_range(self, rng):
"""Returns a list of values in a range."""
start, end = rng.split(':')
(row_offset, column_offset) = a1_to_rowcol(start)
(last_row, last_column) = a1_to_rowcol(end)
out = []
for col in self.values[row_offset - 1:last_row]:
out.extend(col[column_offset - 1:last_column])
return out
def _range_to_gridrange_object(range, worksheet_id):
parts = range.split(':')
start = parts[0]
end = parts[1] if len(parts) > 1 else ''
(row_offset, column_offset) = a1_to_rowcol(start)
(last_row, last_column) = a1_to_rowcol(end) if end else (row_offset,
column_offset)
return {
'sheetId': worksheet_id,
'startRowIndex': row_offset - 1,
'endRowIndex': last_row,
'startColumnIndex': column_offset - 1,
'endColumnIndex': last_column
}
def update_note(worksheet, cell, content):
if not isinstance(content, str):
raise TypeError("Only string allowed as content for a note.")
(startRow, startColumn) = a1_to_rowcol(cell)
body = {
"requests": [
{
"updateCells": {
"range": {
"sheetId": worksheet.id,
"startRowIndex": startRow - 1,
"endRowIndex": startRow,
"startColumnIndex": startColumn - 1,
"endColumnIndex": startColumn,
},
"rows": [
{
"values": [
{
"note": content
}
]
}
],
"fields": "note"
}
}
]
}
worksheet.spreadsheet.batch_update(body)
def get_data_validation_rule(worksheet, label):
"""Returns a DataValidationRule object or None representing the
data validation in effect for the cell identified by ``label``.
:param worksheet: Worksheet object containing the cell whose data
validation rule is desired.
:param label: String with cell label in common format, e.g. 'B1'.
Letter case is ignored.
Example:
>>> get_data_validation_rule(worksheet, 'A1')
<DataValidationRule condition=(bold=True)>
>>> get_data_validation_rule(worksheet, 'A2')
None
"""
label = '%s!%s' % (worksheet.title, rowcol_to_a1(*a1_to_rowcol(label)))
resp = worksheet.spreadsheet.fetch_sheet_metadata({
'includeGridData':
True,
'ranges': [label],
'fields':
'sheets.data.rowData.values.effectiveFormat,sheets.data.rowData.values.dataValidation'
})
props = resp['sheets'][0]['data'][0]['rowData'][0]['values'][0].get(
'dataValidation')
return DataValidationRule.from_props(props) if props else None
def get_user_entered_format(worksheet, label):
"""Returns a CellFormat object or None representing the user-entered formatting directives,
if any, for the cell.
:param worksheet: Worksheet object containing the cell whose format is desired.
:param label: String with cell label in common format, e.g. 'B1'.
Letter case is ignored.
Example:
>>> get_user_entered_format(worksheet, 'A1')
<CellFormat textFormat=(bold=True)>
>>> get_user_entered_format(worksheet, 'A2')
None
"""
label = '%s!%s' % (worksheet.title, rowcol_to_a1(*a1_to_rowcol(label)))
resp = worksheet.spreadsheet.fetch_sheet_metadata({
'includeGridData':
True,
'ranges': [label],
'fields':
'sheets.data.rowData.values.userEnteredFormat'
})
props = resp['sheets'][0]['data'][0]['rowData'][0]['values'][0].get(
'userEnteredFormat')
return CellFormat.from_props(props) if props else None
def clear_expenses(self):
"""Clear all expenses and notes for the month in all categories."""
_, col_1 = a1_to_rowcol(f"{self.FIRST_DAY_COLUMN}1")
_, col_31 = a1_to_rowcol(f"{self.LAST_DAY_COLUMN}1")
cell_list = [
Cell(row=row, col=col, value="")
for col in range(col_1, col_31 + 1)
for row in self.CATEGORY_ROWS.values()
]
label_notes = {
rowcol_to_a1(cell.row, cell.col): ""
for cell in cell_list
}
self.worksheet.update_cells(cell_list=cell_list)
self.worksheet.spreadsheet.client.insert_notes(
worksheet=self.worksheet, labels_notes=label_notes, replace=True)
def list_of_col_names(number_of_cols, starting_col='B'):
first_col_num = a1_to_rowcol(starting_col + '1')[1]
last_col_num = first_col_num + number_of_cols - 1
return [
a1[:-1] for a1 in list_from_range_string(
rowcol_to_a1(1, first_col_num) + ':' +
rowcol_to_a1(1, last_col_num))
]
def format_mkdocs_md(message):
matches = [match for match in re.finditer(PVMESpreadSheet.PATTERN, message.content)]
for match in reversed(matches):
worksheet_data = formatter.util.obtain_pvme_spreadsheet_data(match.group(1))
row, column = a1_to_rowcol(match.group(2))
if worksheet_data:
price_formatted = "{}".format(worksheet_data[row-1][column-1])
else:
price_formatted = "N/A"
message.content = message.content[:match.start()] + price_formatted + message.content[match.end():]
def a1_to_coords(label):
"""
Convert A1 label notion to coordinates in array.
:return tuple: A1 ==> [0, 0]
"""
row, col = a1_to_rowcol(label)
row -= 1
col -= 1
assert row >= 0 and col >= 0, f"{label} results in wrong index [{row}, {col}]"
return row, col
async def update_acell(self, label, value):
"""Updates the value of a cell. Wraps :meth:`gspread.models.Worksheet.row_values`.
:param label: Cell label in A1 notation.
Letter case is ignored.
:type label: str
:param value: New value.
:param nowait: (optional) If true, return a scheduled future instead of waiting for the API call to complete.
:type nowait: bool
"""
row, col = a1_to_rowcol(label)
return await self.update_cell(row, col, value)
def get_cell_as_tuple(cell):
"""Take cell in either format, validate, and return as tuple"""
if type(cell) == tuple:
if len(cell) != 2 or type(cell[0]) != int or type(cell[1]) != int:
raise TypeError("{0} is not a valid cell tuple".format(cell))
return cell
elif isinstance(cell, basestring):
if not match("[a-zA-Z]+[0-9]+", cell):
raise TypeError("{0} is not a valid address".format(cell))
return a1_to_rowcol(cell)
else:
raise TypeError("{0} is not a valid format".format(cell))
def format_sphinx_html(msg, doc_info):
matches = [
match
for match in re.finditer(PVMESpreadsheet.PATTERN, msg.content)
]
for match in reversed(matches):
worksheet_data = PVMESpreadsheet.obtain_pvme_spreadsheet_data(
match.group(1))
row, column = a1_to_rowcol(match.group(2))
price_formatted = "{}".format(worksheet_data[row - 1][column - 1])
msg.content = msg.content[:match.start(
)] + price_formatted + msg.content[match.end():]
def get_cell_as_tuple(cell):
"""Take cell in either format, validate, and return as tuple."""
if type(cell) == tuple:
if (len(cell) != 2 or not np.issubdtype(type(cell[ROW]), np.integer)
or not np.issubdtype(type(cell[COL]), np.integer)):
raise TypeError("{0} is not a valid cell tuple".format(cell))
return cell
elif isinstance(cell, basestring):
if not match("[a-zA-Z]+[0-9]+", cell):
raise TypeError("{0} is not a valid address".format(cell))
return a1_to_rowcol(cell)
else:
raise TypeError("{0} is not a valid format".format(cell))
def list_from_range_string(range_string):
"""Extract all individual cell names from a Excel range.
Keyword arguments:
range_string -- The Excel expression for the range
Example:
If range_string == 'A1:B3' then the list
['A1', 'B1', 'A2', 'B2', 'A3', 'B3'] is returned
"""
colon_position = range_string.find(':')
if colon_position == -1:
raise
first_cell = range_string[:colon_position]
last_cell = range_string[colon_position + 1:]
first_row, first_col = a1_to_rowcol(first_cell)
last_row, last_col = a1_to_rowcol(last_cell)
return [
rowcol_to_a1(i, j)
for i, j in product(range(first_row, last_row +
1), range(first_col, last_col + 1))
]
def post_to_spreadsheet(self, character="Y"):
"""Update spreadsheet with current transaction's has_receipt values."""
worksheet_transactions = defaultdict(list)
for transaction in self.transactions:
worksheet_transactions[transaction.worksheet].append(transaction)
for worksheet, transactions in worksheet_transactions.items():
cell_list = [
Cell(
*a1_to_rowcol(transaction.label),
character if transaction.has_receipt else "",
)
for transaction in transactions
]
worksheet.update_cells(cell_list=cell_list)
def get_earliest_label(*labels):
"""Return the earliest among labels in left-to-right top-to-bottom order."""
labels = [label for label in labels if label]
if not labels:
raise ValueError("At least one non-empty label must be provided.")
if len(labels) == 1:
return labels[0]
coords = [a1_to_rowcol(label) for label in labels]
min_row = min(row for row, _ in coords)
earliest_row = [(row, col) for row, col in coords if row == min_row]
min_col = min(col for _, col in earliest_row)
earliest = [(row, col) for row, col in earliest_row if col == min_col][0]
earliest_label = rowcol_to_a1(*earliest)
return earliest_label
def _prices(self):
"""
Get all prices and recognize their type.
This method practices lazy evaluation too for the same reasons.
:return dict: a map sorted by cell label
{
"D10": (Decimal(3.45), CellType.REGULAR),
"D12": (Decimal(4.56), CellType.REGULAR),
...
"D13": (Decimal(1.23), CellType.TOTAL),
"D15": (Decimal(1.23), CellType.TAX),
}
"""
result = {}
_, col = a1_to_rowcol(f"{self.PRICE_COLUMN}1")
price_cells = [
Cell(row=row, col=col, value=line[col - 1])
for row, line in enumerate(self.content, 1)
if line[col - 1] and row > 1
]
for cell in reversed(price_cells):
label = rowcol_to_a1(cell.row, cell.col)
amount = price_to_decimal(cell.value,
worksheet_title=self.worksheet.title,
label=label)
is_summary_collected = all(price_type in result
for price_type in SUMMARY_TYPES)
if is_summary_collected:
result[label] = (amount, CellType.REGULAR)
# if all summary prices are identified already, then we don't need
# to check the color of other prices because the rest of them are
# regular prices. That's why we move on to the next cell right away.
continue
cell_type = self.get_cell_type(label=label) or CellType.REGULAR
result[label] = (amount, cell_type)
result = dict(natsorted(result.items()))
return result
def save(self):
if not self.fill_table:
return self.message("Is not implemented yet...", True)
table = self.controller.get_sheet(self.fill_table)
if not table:
return self.message("Table is not selected", True)
data = self.prepare_data()
if not len(data):
return self.message(f"There is no data to fill")
if self.prepend_date:
data = list(map(self.add_date, data))
ws = self.controller.load_table(self.fill_table, self._worksheet)
fill_index = self._fill_index or self.find_index(ws)
if not fill_index:
return self.message("Cannot find place to fill", True)
if self._insert:
cell_range = fill_index
for row in data:
retry_fn(lambda: ws.insert_row(
row, index=fill_index, value_input_option='USER_ENTERED'))
else:
ridx, cidx = a1_to_rowcol(fill_index)
ridx += len(data) - 1
cidx += len(data[0]) - 1
cell_range = self.get_fill_range(fill_index, ridx, cidx)
cell_list = ws.range(cell_range)
data_to_fill = [c for row in data for c in row]
try:
self.check_rows(cell_list, data_to_fill)
except CellIsNotBlank:
return self.message(
f"Some cells in range {cell_range} are not blank", True)
ws.update_cells(cell_list)
self.message(f"Successfully filled {table}, {cell_range}")
def _extract_table(spreadsheet, worksheet_title, starting_cell, column_names):
worksheet = spreadsheet.worksheet(worksheet_title)
header_row, first_column = a1_to_rowcol(starting_cell)
num_columns = len(column_names)
last_column = first_column + num_columns - 1
header = tuple(cell.value for cell in worksheet.range(
header_row, first_column, header_row, last_column))
assert header == column_names, 'Header does not match desired columns. %r != %r' % (
header, column_names)
LOG.info(f'Reading {column_names} from {worksheet_title}')
first_row = header_row + 1
for row_cells in _sheet_rows(worksheet, first_row, first_column,
last_column):
values = tuple(cell.value for cell in row_cells)
if any(values):
yield values
else:
# stop when we reach a blank line
break
def _names(self):
"""
Get all names from the Names column with recognized type.
This method practices lazy evaluation - the first time a
certain good gets requested, the entire column of purchased
items gets parsed. This is done for performance reasons in
order to reduce the number of API requests because it takes
one API call to get the style of each cell.
:return dict: a map sorted by cell label
{
"B8": ('Bread', CellType.GROCERY),
"B10": ('SUSHI ROLL', CellType.TAKEOUTS),
"B14": ('DEBIT', CellType.REGULAR),
...
}
"""
result = {}
_, col = a1_to_rowcol(f"{self.NAME_COLUMN}1")
for row, line in enumerate(self.content, 1):
if row == 1:
continue
value = line[col - 1]
label = rowcol_to_a1(row, col)
cell_type = self.get_cell_type(label=label)
if (not cell_type or cell_type == CellType.REGULAR) and not value:
continue
result[label] = (value, cell_type)
result = dict(natsorted(result.items()))
return result
def get_destination_label(self, created: date, good_type: CellType) -> str:
"""Return the cell label in a month billing for a certain Purchase or Transaction."""
row = self.CATEGORY_ROWS[good_type]
_, col = a1_to_rowcol(f"{self.FIRST_DAY_COLUMN}1")
col += created.day - 1
return rowcol_to_a1(row, col)
def test_addr_converters(self):
for row in range(1, 257):
for col in range(1, 512):
addr = utils.rowcol_to_a1(row, col)
(r, c) = utils.a1_to_rowcol(addr)
self.assertEqual((row, col), (r, c))
def test_a1_to_rowcol(self):
self.assertEqual(utils.a1_to_rowcol('ABC3'), (3, 731))
def import_csv(source: Optional[Union[str, StringIO]] = None,
url: Optional[str] = None,
cell: Optional[str] = None,
credentials: Optional[Union[str, dict]] = None,
config: Optional[Union[str, dict]] = None,
delimiter: Optional[str] = None) -> dict:
"""
Import CSV file to Google sheet
:param source: path to source CSV file or StringIO object
:param url: destination sheet url
:param cell: destination sheet cell (can include tab name: 'MyTab!A1')
:param credentials: path to google service account credentials file or dict
:param config: path to config file or dict
:return: Google Sheet API response object
"""
settings = load_config(config) if isinstance(config, str) else None
if settings is None and (source is None or url is None
or credentials is None):
raise ValueError('required parameters missed')
if settings is not None:
source = settings['source']
url = settings['url']
cell = settings.get('cell', 'A1')
credentials = settings['credentials']
else:
cell = cell if cell is not None else 'A1'
# TODO: add other types of credentials
if isinstance(credentials, dict):
credentials = load_credentials_from_dict(credentials)
elif isinstance(credentials, str):
credentials = load_credentials_from_json(credentials)
else:
credentials = None
if credentials is None:
raise ValueError('invalid credentials')
if isinstance(source, str):
try:
infile = open(source, 'r')
if delimiter is None:
dialect = csv.Sniffer().sniff(infile.readline())
infile.seek(0)
csv_data = infile.read()
except IOError as e:
raise ValueError(f'source file error {str(e)}')
elif isinstance(source, StringIO):
if delimiter is None:
dialect = csv.Sniffer().sniff(source.readline())
source.seek(0)
csv_data = source.read()
else:
raise ValueError('not supported source type')
gc = gspread.authorize(credentials)
sheet = gc.open_by_url(url)
if '!' in cell:
tab_name, cell = cell.split('!')
worksheet = sheet.worksheet(tab_name)
clear_range = f'{tab_name}!'
else:
worksheet = sheet.sheet1
clear_range = ''
# clear old values in the sheet
row_col = utils.rowcol_to_a1(worksheet.row_count, worksheet.col_count)
clear_range = f'{clear_range}A1:{row_col}'
sheet.values_clear(clear_range)
first_row, first_column = utils.a1_to_rowcol(cell)
body = {
'requests': [{
'pasteData': {
"coordinate": {
"sheetId": worksheet.id,
"rowIndex": first_row - 1,
"columnIndex": first_column - 1,
},
"data": csv_data,
"type": 'PASTE_NORMAL',
"delimiter":
dialect.delimiter if delimiter is None else delimiter
}
}]
}
return sheet.batch_update(body)
def from_google_spreadsheets(retries=10):
filename = 'Turnos EcoMun'
sheetname = 'Calendario'
logger.debug('Getting info from google spreadsheets - %s - %s', filename,
sheetname)
# Some stuff that needs to be done to use google sheets
scope = [
'https://spreadsheets.google.com/feeds',
'https://www.googleapis.com/auth/drive'
]
credentials = Sac.from_json_keyfile_name(GS_CREDENTIALS_PATH, scope)
while retries > 0:
try:
gcc = gspread.authorize(credentials)
except httplib2.ServerNotFoundError:
logger.warning(
'Server not found error in authorization, retries=%r', retries)
retries -= 1
continue
except TimeoutError:
logger.warning('Timeout error in authorization, retries=%r',
retries)
retries -= 1
continue
except requests.exceptions.ConnectionError:
logger.warning('Connection error in authorization, retries=%r',
retries)
retries -= 1
continue
except oauth2client.client.HttpAccessTokenRefreshError:
logger.warning(
'HttpAccessTokenRefreshError in authorization, retries=%r',
retries)
retries -= 1
continue
try:
archivo = gcc.open(filename)
except requests.exceptions.ConnectionError:
logger.warning('Connection error getting file, retries=%r',
retries)
retries -= 1
continue
wks = archivo.worksheet(sheetname)
data = wks.range('G5:W9')
output = {}
for cell in data:
for day, recorded_cell in DAYS_TO_CELL.items():
row, col = a1_to_rowcol(recorded_cell)
if cell.row == row and cell.col == col:
output[day] = cell.value
return output
logger.critical('Max retries')
send_email(ADMIN_EMAIL, 'ERROR', 'MAX RETRIES. CHECK LOG')
raise RuntimeError('Max retries')