How to Automate Notion Database Reports using the API

1import os
2import time
3import requests
4from datetime import date, timedelta
5
6NOTION_TOKEN = os.environ['NOTION_TOKEN']
7DATA_SOURCE_ID = os.environ['NOTION_DATA_SOURCE_ID']
8PARENT_PAGE_ID = os.environ['NOTION_REPORTS_PAGE_ID']
9
10HEADERS = {
11    'Authorization': f'Bearer {NOTION_TOKEN}',
12    'Notion-Version': '2025-09-03',
13    'Content-Type': 'application/json'
14}
15
16def query_all_rows():
17    rows, cursor = [], None
18    while True:
19        payload = {'filter': {'property': 'Date', 'date': {'past_week': {}}}, 'page_size': 100}
20        if cursor:
21            payload['start_cursor'] = cursor
22        resp = requests.post(
23            f'https://api.notion.com/v1/data_sources/{DATA_SOURCE_ID}/query',
24            headers=HEADERS, json=payload
25        )
26        resp.raise_for_status()
27        data = resp.json()
28        rows.extend(data['results'])
29        if not data['has_more']:
30            break
31        cursor = data['next_cursor']
32        time.sleep(0.35)
33    return rows
34
35def aggregate(rows):
36    s = {'revenue': 0.0, 'hours': 0.0, 'count': 0, 'items': []}
37    for row in rows:
38        p = row['properties']
39        rev = p.get('Revenue', {}).get('number') or 0
40        hrs = p.get('Hours', {}).get('number') or 0
41        name = (p.get('Name', {}).get('title') or [{'plain_text': 'Untitled'}])[0]['plain_text']
42        s['revenue'] += rev
43        s['hours'] += hrs
44        s['count'] += 1
45        s['items'].append({'name': name, 'revenue': rev, 'hours': hrs})
46    s['avg'] = s['revenue'] / s['count'] if s['count'] else 0
47    return s
48
49def create_report_page(title):
50    resp = requests.post('https://api.notion.com/v1/pages', headers=HEADERS, json={
51        'parent': {'page_id': PARENT_PAGE_ID},
52        'properties': {'title': {'title': [{'type': 'text', 'text': {'content': title}}]}}
53    })
54    resp.raise_for_status()
55    return resp.json()['id']
56
57def append_blocks(page_id, blocks):
58    for i in range(0, len(blocks), 100):
59        resp = requests.patch(f'https://api.notion.com/v1/blocks/{page_id}/children',
60            headers=HEADERS, json={'children': blocks[i:i+100]})
61        resp.raise_for_status()
62        time.sleep(0.35)
63
64def txt(content): return [{'type': 'text', 'text': {'content': content}}]
65
66def main():
67    today = date.today()
68    w_start = today - timedelta(days=today.weekday() + 7)
69    w_end = w_start + timedelta(days=6)
70    title = f'Weekly Report: {w_start.strftime("%b %d")} - {w_end.strftime("%b %d, %Y")}'
71    print('Querying rows...')
72    rows = query_all_rows()
73    s = aggregate(rows)
74    print(f"Revenue: ${s['revenue']:,.2f}, Hours: {s['hours']}, Count: {s['count']}")
75    page_id = create_report_page(title)
76    blocks = [
77        {'object':'block','type':'heading_2','heading_2':{'rich_text':txt('Summary')}},
78        {'object':'block','type':'paragraph','paragraph':{'rich_text':txt(f"Revenue: ${s['revenue']:,.2f}")}},
79        {'object':'block','type':'paragraph','paragraph':{'rich_text':txt(f"Hours: {s['hours']:.1f}")}},
80        {'object':'block','type':'paragraph','paragraph':{'rich_text':txt(f"Entries: {s['count']}")}},
81        {'object':'block','type':'paragraph','paragraph':{'rich_text':txt(f"Avg per entry: ${s['avg']:,.2f}")}},
82        {'object':'block','type':'heading_2','heading_2':{'rich_text':txt('Top 10 Entries')}},
83    ]
84    for item in sorted(s['items'], key=lambda x: x['revenue'], reverse=True)[:10]:
85        blocks.append({'object':'block','type':'bulleted_list_item','bulleted_list_item':{
86            'rich_text': txt(f"{item['name']}: ${item['revenue']:,.2f} ({item['hours']}h)")
87        }})
88    append_blocks(page_id, blocks)
89    print(f'Report created: https://notion.so/{page_id.replace("-","")}')
90
91if __name__ == '__main__':
92    main()