Skip to content

Commit c2b051e

Browse files
ErikBjareErikBjare
authored
feat(example): added working_hours_gspread.py example script (#81)
* feat(example): added working_hours_gspread.py example script, updated working_hours.py to be more reusable * fix(example): improvements to working_hours.py and working_hours_gspread.py examples * build: added gspread as a dev dependency (used in examples) * fix: fixed examples * fix: fixed load_dataframe example in CI * fix: fixed working_hours example in CI
1 parent b4598ff commit c2b051e

File tree

6 files changed

+319
-33
lines changed

6 files changed

+319
-33
lines changed

‎Makefile‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -13,7 +13,7 @@ test-integration:
1313
test-examples:
1414
cd examples; pytest -v *.py
1515
cd examples; yes | python3 load_dataframe.py
16-
cd examples; python3 working_hours.py
16+
cd examples; python3 working_hours.py 'activitywatch|aw-|github.com' fakedata
1717

1818
typecheck:
1919
poetry run mypy

‎examples/load_dataframe.py‎

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,8 @@
11
"""
22
Load ActivityWatch data into a dataframe, and export as CSV.
33
"""
4+
import os
5+
import socket
46
from datetime import datetime, timedelta, timezone
57

68
import iso8601
@@ -11,10 +13,11 @@
1113

1214

1315
def build_query() -> str:
16+
hostname = "fakedata" if os.getenv("CI") else socket.gethostname()
1417
canonicalQuery = canonicalEvents(
1518
DesktopQueryParams(
16-
bid_window="aw-watcher-window_",
17-
bid_afk="aw-watcher-afk_",
19+
bid_window=f"aw-watcher-window_{hostname}",
20+
bid_afk=f"aw-watcher-afk_{hostname}",
1821
classes=default_classes,
1922
)
2023
)

‎examples/working_hours.py‎

Lines changed: 48 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -5,22 +5,25 @@
55
"""
66

77
import json
8-
import re
8+
import logging
99
import os
10-
from datetime import datetime, timedelta, time
11-
from typing import List, Tuple, Dict
12-
13-
from tabulate import tabulate
10+
import re
11+
import socket
12+
import sys
13+
from datetime import datetime, time, timedelta
14+
from typing import Dict, List, Tuple
1415

1516
import aw_client
1617
from aw_client import queries
1718
from aw_core import Event
1819
from aw_transform import flood
20+
from tabulate import tabulate
1921

20-
21-
EXAMPLE_REGEX = r"activitywatch|algobit|defiarb|github.com"
2222
OUTPUT_HTML = os.environ.get("OUTPUT_HTML", "").lower() == "true"
2323

24+
td1d = timedelta(days=1)
25+
day_offset = timedelta(hours=4)
26+
2427

2528
def _pretty_timedelta(td: timedelta) -> str:
2629
s = str(td)
@@ -47,19 +50,11 @@ def generous_approx(events: List[dict], max_break: float) -> timedelta:
4750
)
4851

4952

50-
def query(regex: str = EXAMPLE_REGEX, save=True):
53+
def query(regex: str, timeperiods, hostname: str):
5154
print("Querying events...")
52-
td1d = timedelta(days=1)
53-
day_offset = timedelta(hours=4)
5455
print(f" Day offset: {day_offset}")
5556
print("")
5657

57-
now = datetime.now().astimezone()
58-
today = (datetime.combine(now.date(), time()) + day_offset).astimezone()
59-
60-
timeperiods = [(today - i * td1d, today - (i - 1) * td1d) for i in range(5)]
61-
timeperiods.reverse()
62-
6358
categories: List[Tuple[List[str], Dict]] = [
6459
(
6560
["Work"],
@@ -75,8 +70,8 @@ def query(regex: str = EXAMPLE_REGEX, save=True):
7570

7671
canonicalQuery = queries.canonicalEvents(
7772
queries.DesktopQueryParams(
78-
bid_window="aw-watcher-window_",
79-
bid_afk="aw-watcher-afk_",
73+
bid_window=f"aw-watcher-window_{hostname}",
74+
bid_afk=f"aw-watcher-afk_{hostname}",
8075
classes=categories,
8176
filter_classes=[["Work"]],
8277
)
@@ -89,16 +84,38 @@ def query(regex: str = EXAMPLE_REGEX, save=True):
8984

9085
res = aw.query(query, timeperiods)
9186

92-
for break_time in [0, 5 * 60, 10 * 60, 15 * 60]:
93-
_print(
94-
timeperiods, res, break_time, {"category_rule": categories[0][1]["regex"]}
95-
)
87+
return res
9688

97-
if save:
98-
fn = "working_hours_events.json"
99-
with open(fn, "w") as f:
100-
print(f"Saving to {fn}...")
101-
json.dump(res, f, indent=2)
89+
90+
def main():
91+
if len(sys.argv) < 2:
92+
print("Usage: python3 working_hours.py <regex> [hostname]")
93+
exit(1)
94+
95+
regex = sys.argv[1]
96+
print(f"Using regex: {regex}")
97+
98+
if len(sys.argv) > 2:
99+
hostname = sys.argv[2]
100+
print(f"Using hostname: {hostname}")
101+
else:
102+
hostname = socket.gethostname()
103+
104+
now = datetime.now().astimezone()
105+
today = (datetime.combine(now.date(), time()) + day_offset).astimezone()
106+
107+
timeperiods = [(today - i * td1d, today - (i - 1) * td1d) for i in range(5)]
108+
timeperiods.reverse()
109+
110+
res = query(regex, timeperiods, hostname)
111+
112+
for break_time in [0, 5 * 60, 15 * 60]:
113+
_print(timeperiods, res, break_time, {"regex": regex})
114+
115+
fn = "working_hours_events.json"
116+
with open(fn, "w") as f:
117+
print(f"Saving to {fn}...")
118+
json.dump(res, f, indent=2)
102119

103120

104121
def _print(timeperiods, res, break_time, params: dict):
@@ -134,4 +151,7 @@ def _print(timeperiods, res, break_time, params: dict):
134151

135152

136153
if __name__ == "__main__":
137-
query()
154+
# ignore log warnings in aw_transform
155+
logging.getLogger("aw_transform").setLevel(logging.ERROR)
156+
157+
main()

‎examples/working_hours_gspread.py‎

Lines changed: 122 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,122 @@
1+
"""
2+
This script uses ActivityWatch events to updates a Google Sheet
3+
with the any events matching a regex for the last `days_back` days.
4+
5+
It uses the `working_hours.py` example to calculate the working hours
6+
and the `gspread` library to interact with Google Sheets.
7+
8+
The Google Sheet is identified by its key, which is hardcoded in the script.
9+
The script uses a service account for authentication with the Google Sheets API.
10+
11+
The script assumes that the Google Sheet has a worksheet for each hostname, named "worked-{hostname}".
12+
If such a worksheet does not exist, the script will fail.
13+
14+
The working hours are calculated generously, meaning that if the time between two consecutive
15+
events is less than `break_time` (10 minutes by default), it is considered as working time.
16+
17+
Usage:
18+
python3 working_hours_gspread.py <sheet_key> <regex>
19+
"""
20+
import socket
21+
import sys
22+
from datetime import datetime, time, timedelta
23+
24+
import gspread
25+
26+
import working_hours
27+
28+
td1d = timedelta(days=1)
29+
break_time = 10 * 60
30+
31+
32+
def update_sheet(sheet_key: str, regex: str):
33+
"""
34+
Update the Google Sheet with the working hours for the last `days_back` days.
35+
36+
1. Open the sheet and get the last entry
37+
2. Query the working hours for the days since the last entry
38+
3. Update the last entry in the Google Sheet (if any)
39+
4. Append any new entries
40+
"""
41+
42+
hostname = socket.gethostname()
43+
hostname_display = hostname.replace(".localdomain", "").replace(".local", "")
44+
45+
try:
46+
gc = gspread.service_account()
47+
except Exception as e:
48+
print(e)
49+
print(
50+
"Failed to authenticate with Google Sheets API.\n"
51+
"Make sure you have a service account key in ~/.config/gspread/service_account.json\n"
52+
"See https://gspread.readthedocs.io/en/latest/oauth2.html#for-bots-using-service-account"
53+
)
54+
exit(1)
55+
56+
# Open the sheet
57+
sh = gc.open_by_key(sheet_key)
58+
print(f"Updating document: {sh.title}")
59+
worksheet = sh.worksheet(f"worked-{hostname_display}")
60+
print(f"Updating worksheet: {worksheet.title}")
61+
62+
# Get the most recent entry from the Google Sheet
63+
values = worksheet.get_all_values()
64+
if values:
65+
last_row = values[-1]
66+
last_date = datetime.strptime(last_row[0], "%Y-%m-%d").date()
67+
else:
68+
last_date = None
69+
70+
last_datetime = (
71+
(datetime.combine(last_date, time()) + working_hours.day_offset).astimezone()
72+
if last_date
73+
else None
74+
)
75+
76+
if last_datetime:
77+
print(f"Last entry: {last_datetime}")
78+
79+
now = datetime.now().astimezone()
80+
today = (
81+
datetime.combine(now.date(), time()) + working_hours.day_offset
82+
).astimezone()
83+
84+
# Create a list of time periods to query, from last_date or days_back_on_new back if None
85+
days_back_on_new = 30
86+
days_back = (today - last_datetime).days + 1 if last_datetime else days_back_on_new
87+
timeperiods = [(today - i * td1d, today - (i - 1) * td1d) for i in range(days_back)]
88+
timeperiods.reverse()
89+
90+
# Run the query function from the original script and get the result
91+
res = working_hours.query(regex, timeperiods, hostname)
92+
93+
# Iterate over the result and update or append the data to the Google Sheet
94+
for tp, r in zip(timeperiods, res):
95+
date = tp[0].date()
96+
duration = (
97+
working_hours.generous_approx(r["events"], break_time).total_seconds()
98+
/ 3600
99+
)
100+
row = [str(date), duration]
101+
102+
# If the date is the same as the last entry, update it
103+
if last_date and date == last_date:
104+
print(f"Updating {row}")
105+
worksheet.update_cell(len(worksheet.get_all_values()), 2, duration)
106+
# If the date is later than the last entry, append it
107+
elif not last_date or date > last_date:
108+
print(f"Appending {row}")
109+
worksheet.append_row(row, value_input_option="USER_ENTERED")
110+
else:
111+
print(f"Skipping {row}")
112+
113+
114+
if __name__ == "__main__":
115+
if len(sys.argv) == 3:
116+
sheet_key = sys.argv[1]
117+
regex = sys.argv[2]
118+
else:
119+
print("Usage: python3 working_hours_gspread.py <sheet_key> <regex>")
120+
exit(1)
121+
122+
update_sheet(sheet_key, regex)

0 commit comments

Comments
 (0)