From 54978eb6b515afd1da68642de07e482b09acf9f3 Mon Sep 17 00:00:00 2001 From: Jan Tuomi Date: Sun, 14 Feb 2021 17:01:19 +0200 Subject: Update last done in Sheets --- hommaexceli_py/data_parser.py | 63 +++++++++++++++++++++++++++++++++++++++ hommaexceli_py/dataclass_defs.py | 22 ++++++++++++++ hommaexceli_py/main.py | 1 - hommaexceli_py/parser.py | 52 -------------------------------- hommaexceli_py/sheets_client.py | 63 +++++++++++++++++++++++++++++---------- hommaexceli_py/telegram_client.py | 35 ++++++++++++++++------ 6 files changed, 158 insertions(+), 78 deletions(-) create mode 100644 hommaexceli_py/data_parser.py create mode 100644 hommaexceli_py/dataclass_defs.py delete mode 100644 hommaexceli_py/parser.py diff --git a/hommaexceli_py/data_parser.py b/hommaexceli_py/data_parser.py new file mode 100644 index 0000000..e8ca33b --- /dev/null +++ b/hommaexceli_py/data_parser.py @@ -0,0 +1,63 @@ +from dataclasses import dataclass +import datetime +import re +from dataclass_defs import ProcessedRow, SheetsRow + +INTERVAL_ABBREVS = { + "pv": datetime.timedelta(days=1), + "vk": datetime.timedelta(days=7), + "kk": datetime.timedelta(days=30), + "v": datetime.timedelta(days=365), +} + + +def _calculate_interval(interval_str: str): + match = re.search(r"(\d+)(\w+)", interval_str) + number = int(match[1]) + abbrev = match[2] + + try: + interval = INTERVAL_ABBREVS[abbrev] + except KeyError: + raise Exception(f'"{interval_str}" is an invalid interval string!') + + total_interval = datetime.timedelta(seconds=interval.total_seconds() * number) + return total_interval + + +def _timestamp_to_date(timestamp: str) -> datetime.datetime: + return datetime.datetime.strptime(timestamp, "%Y-%m-%d") + + +def _date_to_timestamp(date: datetime.datetime) -> str: + return datetime.datetime.strftime(date, "%Y-%m-%d") + + +def _process_row(row: SheetsRow): + interval = _calculate_interval(row.interval) + last_done_date = _timestamp_to_date(row.last_done) + next_date = last_done_date + interval + next_date_str = _date_to_timestamp(next_date) + + return ProcessedRow( + name=row.name, + next_datestamp=next_date_str, + next_date=next_date, + last_datestamp=row.last_done, + last_date=last_done_date, + interval=row.interval, + ) + + +def process_rows(rows: list[SheetsRow]) -> list[ProcessedRow]: + return list(map(_process_row, rows)) + + +def _is_this_week(processed_row: ProcessedRow): + next_date = processed_row.next_date + now = datetime.datetime.now() + return (next_date - now) < datetime.timedelta(weeks=1) + + +def filter_only_this_week(rows: list[ProcessedRow]) -> list[ProcessedRow]: + return list(filter(_is_this_week, rows)) diff --git a/hommaexceli_py/dataclass_defs.py b/hommaexceli_py/dataclass_defs.py new file mode 100644 index 0000000..d808fb8 --- /dev/null +++ b/hommaexceli_py/dataclass_defs.py @@ -0,0 +1,22 @@ +from dataclasses import dataclass +import datetime + + +@dataclass +class SheetsRow: + interval: str + name: str + last_done: str + + def __str__(self): + return f"{self.interval}, {self.name}, {self.last_done}" + + +@dataclass +class ProcessedRow: + name: str + next_datestamp: str + next_date: datetime.datetime + last_datestamp: str + last_date: datetime.datetime + interval: str diff --git a/hommaexceli_py/main.py b/hommaexceli_py/main.py index 90ad361..4e9c898 100644 --- a/hommaexceli_py/main.py +++ b/hommaexceli_py/main.py @@ -1,5 +1,4 @@ import logging -from os import environ from dotenv import load_dotenv, dotenv_values load_dotenv() diff --git a/hommaexceli_py/parser.py b/hommaexceli_py/parser.py deleted file mode 100644 index 1b779a6..0000000 --- a/hommaexceli_py/parser.py +++ /dev/null @@ -1,52 +0,0 @@ -import datetime -import logging -import re -from sheets_client import SheetsRow - -INTERVAL_ABBREVS = { - "pv": datetime.timedelta(days=1), - "vk": datetime.timedelta(days=7), - "kk": datetime.timedelta(days=30), - "v": datetime.timedelta(days=365), -} - - -def _calculate_interval(interval_str: str): - match = re.search(r"(\d+)(\w+)", interval_str) - number = int(match[1]) - abbrev = match[2] - - try: - interval = INTERVAL_ABBREVS[abbrev] - except KeyError: - raise Exception(f'"{interval_str}" is an invalid interval string!') - - total_interval = datetime.timedelta(seconds=interval.total_seconds() * number) - return total_interval - - -def _timestamp_to_date(timestamp: str): - return datetime.datetime.strptime(timestamp, "%Y-%m-%d") - - -def _date_to_timestamp(date): - return datetime.datetime.strftime(date, "%Y-%m-%d") - - -def _process_row(row: SheetsRow): - interval = _calculate_interval(row.interval) - last_done_date = _timestamp_to_date(row.last_done) - next_date = last_done_date + interval - next_date_str = _date_to_timestamp(next_date) - return (next_date, row.name, row.interval) - - -def _is_this_week(processed_row): - next_date = processed_row[0] - now = datetime.datetime.now() - return (next_date - now) < datetime.timedelta(weeks=1) - - -def filter_only_this_weeks_rows(rows: list[SheetsRow]): - processed_rows = map(_process_row, rows) - return filter(_is_this_week, processed_rows) diff --git a/hommaexceli_py/sheets_client.py b/hommaexceli_py/sheets_client.py index b30eb3d..9a09f07 100644 --- a/hommaexceli_py/sheets_client.py +++ b/hommaexceli_py/sheets_client.py @@ -1,26 +1,20 @@ import pickle import os.path import logging +import re from os import getenv from datetime import date from dataclasses import dataclass -from googleapiclient.discovery import build +from googleapiclient.discovery import Resource, build from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request - -@dataclass -class SheetsRow: - interval: str - name: str - last_done: str - - def __str__(self): - return f"{self.interval}, {self.name}, {self.last_done}" +from data_parser import ProcessedRow +from dataclass_defs import SheetsRow # If modifying these scopes, delete the file token.pickle. -SCOPES = ["https://www.googleapis.com/auth/spreadsheets.readonly"] +SCOPES = ["https://www.googleapis.com/auth/spreadsheets"] def _get_or_default(list, index, default=""): @@ -36,10 +30,27 @@ def _row_to_dataclass(values): today = date.today() last_done = _get_or_default(values, 2, today.strftime("%Y-%m-%d")) - return SheetsRow(interval, name, last_done) + return SheetsRow(interval=interval, name=name, last_done=last_done) + + +def _get_rightmost_column_in_range(range: str) -> str: + # Match range of format Sheet!A1:D4 + match = re.search(r"(.+\!)?([A-Z]+?)(\d+)\:([A-Z]+?)(\d+)", range) + + sheet_name: str = match[1] or "" + # topleft_col = match[2] + topleft_row = int(match[3]) + bottomright_col = match[4] + bottomright_row = int(match[5]) + + rm_col = bottomright_col + rm_row_start = topleft_row + rm_row_end = bottomright_row + + return f"{sheet_name}{rm_col}{rm_row_start}:{rm_col}{rm_row_end}" -def authenticate_and_fetch_sheets_data(): +def authenticate_sheets() -> Resource: logging.info("Reading Google credentials and authenticating...") creds = None # The file token.pickle stores the user's access and refresh tokens, and is @@ -59,9 +70,10 @@ def authenticate_and_fetch_sheets_data(): with open("token.pickle", "wb") as token: pickle.dump(creds, token) - service = build("sheets", "v4", credentials=creds, cache_discovery=False) + return build("sheets", "v4", credentials=creds, cache_discovery=False) - # Call the Sheets API + +def fetch_sheet_data(service: Resource) -> list[SheetsRow]: logging.info("Calling Sheets API to fetch data...") sheet = service.spreadsheets() result = ( @@ -75,4 +87,23 @@ def authenticate_and_fetch_sheets_data(): rows = result.get("values", []) logging.info(f"Fetched {len(rows)} rows of data") - return map(_row_to_dataclass, rows) + return list(map(_row_to_dataclass, rows)) + + +def update_sheet_last_done_column( + service: Resource, processed_rows: list[ProcessedRow] +): + logging.info("Calling Sheets API to update last done column...") + range = getenv("SHEETS_RANGE") + sheet = service.spreadsheets() + last_done_column_range = _get_rightmost_column_in_range(range) + + # Update last done date to match next date + body = {"values": list(map(lambda row: [row.next_datestamp], processed_rows))} + + sheet.values().update( + spreadsheetId=getenv("SHEETS_SPREADSHEET_ID"), + range=last_done_column_range, + valueInputOption="RAW", + body=body, + ).execute() diff --git a/hommaexceli_py/telegram_client.py b/hommaexceli_py/telegram_client.py index 6d83469..ad1f478 100644 --- a/hommaexceli_py/telegram_client.py +++ b/hommaexceli_py/telegram_client.py @@ -3,10 +3,17 @@ import signal import sys import logging from os import getenv +from typing import Union +from googleapiclient.discovery import Resource from telegram.ext import Updater, CommandHandler, CallbackContext from telegram import Update -from sheets_client import authenticate_and_fetch_sheets_data -from parser import filter_only_this_weeks_rows +from sheets_client import ( + authenticate_sheets, + fetch_sheet_data, + update_sheet_last_done_column, +) +from data_parser import filter_only_this_week, process_rows +from functools import partial chats_mem_cache = set() @@ -23,22 +30,25 @@ def _signal_handler(sig, frame): sys.exit(0) -def _callback_alarm(context: CallbackContext): +def _callback_alarm(context: CallbackContext, sheets_service: Resource): chat_id: int = context.job.context logging.info(f"Authenticating and fetching data from Sheets...") - rows = authenticate_and_fetch_sheets_data() + rows = fetch_sheet_data(sheets_service) logging.info(f"Processing data...") - processed_data = filter_only_this_weeks_rows(rows) + processed_rows = process_rows(rows) + this_weeks_rows = filter_only_this_week(processed_rows) logging.info(f"Sending sheet data to chat id {chat_id}...") message = "Tällä viikolla tehtävät hommat:\n" + "\n".join( - map(lambda task: f"{task[1]} ({task[2]})", processed_data) + map(lambda task: f"{task.name} ({task.interval})", this_weeks_rows) ) context.bot.send_message(chat_id=chat_id, text=message) + update_sheet_last_done_column(sheets_service, processed_rows) -def _callback_timer(update: Update, context: CallbackContext): + +def _callback_timer(update: Update, context: CallbackContext, sheets_service: Resource): chat_id = update.message.chat_id user_id = update.message.from_user.id @@ -55,9 +65,10 @@ def _callback_timer(update: Update, context: CallbackContext): ) chats_mem_cache.add(chat_id) context.job_queue.run_repeating( - _callback_alarm, + partial(_callback_alarm, sheets_service=sheets_service), datetime.timedelta(weeks=1), 1, # run once immediately after 1 sec + name="callback_alarm", context=chat_id, ) @@ -80,13 +91,19 @@ def _stop_timer(update: Update, context: CallbackContext): def run_telegram(): + sheets_service = authenticate_sheets() + tg_token = getenv("TELEGRAM_TOKEN") logging.info("Creating Telegram bot object...") updater = Updater(tg_token) logging.info("Registering Telegram bot /start handler...") updater.dispatcher.add_handler( - CommandHandler("start", _callback_timer, pass_job_queue=True) + CommandHandler( + "start", + partial(_callback_timer, sheets_service=sheets_service), + pass_job_queue=True, + ) ) logging.info("Registering Telegram bot /stop handler...") -- cgit v1.3