def get_sheet_earnings(date, value_render_option='FORMATTED_VALUE'):
month = date.strftime("%B")
result = SheetService().read_sheet(SPREADSHEET_ID,
f'{month}!A3:C',
value_render_option=value_render_option)
values = result.get('values', [])
return values
def get_chart_from_sheet(date):
month = date.strftime("%B")
result = SheetService().read_sheet(SPREADSHEET_ID, f'{month}!F2:G')
values = result.get('values', [])
if len(values) == 0:
return None
title = values[0]
values = list(map(convert_to_decimal, values[1:]))
values.insert(0, title)
html = HTML(html=str(chart_template % {"json": values}))
loop = asyncio.new_event_loop()
text = threading.Thread(target=render, args=(html, loop))
text.start()
text.join()
# HEROKU PATH
# config = imgkit.config(wkhtmltoimage='.apt/usr/local/bin/wkhtmltoimage')
config = imgkit.config(
wkhtmltoimage='C:\\Program Files\\wkhtmltopdf\\bin\\wkhtmltoimage.exe')
content = html.find('#content')[0].html
img = imgkit.from_string(content,
False,
config=config,
options={
'format': 'png',
'crop-w': '650',
'crop-x': '150',
'crop-y': '50',
'crop-h': '400',
'encoding': 'utf-8',
})
str_file = io.BytesIO(img)
return str_file
def get_table_from_sheet(date):
month = date.strftime("%B")
result = SheetService().read_sheet_multiple(
SPREADSHEET_ID, [f'{month}!A2:C', f'{month}!E2:G', f'{month}!I2:K'],
major_dimension='COLUMNS')
values = result.get('valueRanges', [])
header = []
l_ = []
if len(values) > 0:
for val in values:
header = header + [item[0] for item in val['values']]
l_ = l_ + [i[1:] for i in val['values']]
scope = PlotlyScope()
fig = go.Figure(data=[
go.Table(columnwidth=[80, 150, 100, 80, 150, 100, 100, 100],
header=dict(values=header),
cells=dict(values=l_,
fill=dict(color=[
'lightgreen', 'lightgreen', 'lightgreen',
'#ff9982', '#ff9982', '#ff9982', '#ffbf70',
'#ffbf70'
]),
height=30))
])
test = scope.transform(fig, format="png", scale=2)
str_file = io.BytesIO(test)
return str_file
else:
return None
def create_sheet_by_month(date):
sheet_service = SheetService()
sheet_properties = sheet_service.add_sheet(SPREADSHEET_ID,
date.strftime("%B"))
sheet_id = sheet_properties['sheetId']
requests = get_sheet_format(sheet_id)
requests = requests + [{
"updateCells": {
"rows": [{
"values": table_titles
}],
"fields": "*",
"start": {
"sheetId": sheet_id,
"rowIndex": 1,
"columnIndex": 0
}
}
}]
# Check recurrent elements
with open(recurrent_file) as json_file:
try:
data = json.load(json_file)
if len(data['earnings']) > 0:
requests = requests + [{
"updateCells": {
"rows": __get_values_for_update(
data['earnings'], date),
"fields": "*",
"start": {
"sheetId": sheet_id,
"rowIndex": 2,
"columnIndex": 0
}
}
}]
if len(data['expenses']) > 0:
requests = requests + [{
"updateCells": {
"rows": __get_values_for_update(
data['expenses'], date),
"fields": "*",
"start": {
"sheetId": sheet_id,
"rowIndex": 2,
"columnIndex": 4
}
}
}]
except JSONDecodeError:
pass
sheet_service.update_sheet(SPREADSHEET_ID, requests)
def add_new_sheet_element(date: datetime.date, name: str, amount: float,
start_range: str):
month = date.strftime("%B")
values = [[date.strftime('%d/%m/%Y'), name, amount]]
updated = SheetService().write_append_sheet(SPREADSHEET_ID,
f'{month}!{start_range}',
values)
return updated
def get_sheet_min_max_month():
spreadsheet = SheetService().get_spreadsheet(SPREADSHEET_ID, [], False)
sheets = spreadsheet['sheets']
if len(sheets) > 0:
first = sheets[0]
last = sheets[-1]
try:
first_month = strptime(first['properties']['title'], '%B').tm_mon
last_month = strptime(last['properties']['title'], '%B').tm_mon
except ValueError as e:
print('error type: ', type(e))
return -1, -1
logger.info('first month %s, last month %s', first_month, last_month)
return first_month, last_month
else:
return -1, -1
def get_sheet_report(date):
month = date.strftime("%B")
result = SheetService().read_sheet(SPREADSHEET_ID, f'{month}!I3:K3')
values = result.get('values', [])
return values[0][0], values[0][1], values[0][2]
def delete_earning(date, index):
month = date.strftime("%B")
values = get_sheet_earnings(date, 'UNFORMATTED_VALUE')
del values[index]
values.append(["", "", ""])
SheetService().write_sheet(SPREADSHEET_ID, f'{month}!A3:C', values)