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)