#!/usr/bin/env python3 """シリーズのページに埋め込んでいた Google スプレッドシート・ドキュメントの中身を取ってきて、 サイトに直接載せるためのデータ（src/data/series_docs.json）に書き出す。 - 元は Google 側。サイトは書き出した時点の写しを載せる。Google 側を直したら、これを動かして push する python3 tools/fetch_series_docs.py - 取ってくるのは「ウェブに公開」した版（/pub・/pubhtml）。ログインはいらない - 表は CSV で取る。ドキュメントは公開版の HTML から、見出し・段落・箇条書き・表・太字・リンクだけを残す - デザインノートは Google から写さない（サイトの値が正。src/data/design_note.json と src/lib/sheets.ts） - 載せる場所は src/data/static.json の {"type": "sheet" | "doc", "key": ...} のブロック """ import csv, io, json, re, sys, urllib.request from urllib.parse import urlparse, parse_qs from bs4 import BeautifulSoup, NavigableString OUT = 'src/data/series_docs.json' SHEET = 'https://docs.google.com/spreadsheets/d/e/{id}' DOC = 'https://docs.google.com/document/d/e/{id}/pub?embedded=true' SHEETS = { # key: 公開版の id（2PACX-...）。タブは公開版に出ている順にすべて取る 'hakobu-progress': '2PACX-1vQnqeo6YmdjAz9VQiH8-PYR4OzF9RdlgEOBe-SMtNjYcwdeHkkw2gNTOnLw9jSHBcWmIT3gaxnYnTH6', 'flow-library': '2PACX-1vRpLxXW9hSt-ediJC6AUIt8peUUbRATWB05SL7fdj14ygbsdQM1Hi9LEondS7phSu43Fv1A5MvGwDCT', } DOCS = { 'hakobu-requirements': '2PACX-1vQ8DnpuXG_E-iyE9U0li_i7iwsneP2VpI7DxK-rOP-HFVhuf2ARqWGgiW0gChVb3QgOPcjAaOqS8l9a', 'tsukamu-requirements': '2PACX-1vSTXcuWYVsHofVRjwcR3rqTvrzV6mGnOfUe8MdcCsBpKWfVA2zj-HLmDB3rnQ3MW2_OogYNzHhNDCtR', } def get(url): req = urllib.request.Request(url, headers={'User-Agent': 'p2m-site fetch_series_docs'}) with urllib.request.urlopen(req, timeout=60) as r: return r.read().decode('utf-8') def trim(rows): """後ろの空の行・列を落とす""" while rows and not any(c.strip() for c in rows[-1]): rows.pop() width = max((max((i + 1 for i, c in enumerate(r) if c.strip()), default=0) for r in rows), default=0) return [[c.strip() for c in (r + [''] * width)[:width]] for r in rows] def fetch_sheet(sid): base = SHEET.format(id=sid) html = get(f'{base}/pubhtml?widget=true&headers=false') title = re.search(r'(.*?)', html).group(1) title = BeautifulSoup(title, 'html.parser').get_text().removesuffix(' - Google Drive').strip() tabs = [] for name, gid in re.findall(r'items\.push\(\{name: "((?:[^"\\]|\\.)*)", pageUrl: "[^"]*", gid: "(\d+)"', html): name = json.loads(f'"{name}"') rows = trim(list(csv.reader(io.StringIO(get(f'{base}/pub?gid={gid}&single=true&output=csv'))))) tabs.append({'name': name, 'rows': rows}) if not tabs: raise SystemExit(f'タブが見つからない: {sid}') return {'title': title, 'tabs': tabs} def clean_doc(html): soup = BeautifulSoup(html, 'html.parser') css = ' '.join(s.get_text() for s in soup.find_all('style')) rules = dict(re.findall(r'\.(c\d+)\{([^}]*)\}', css)) def has(el, prop): return any(prop in rules.get(c, '') for c in (el.get('class') or [])) def size(el): for c in el.get('class') or []: m = re.search(r'font-size:(\d+(?:\.\d+)?)pt', rules.get(c, '')) if m: return float(m.group(1)) return 0 body = soup.find(class_='doc-content') out = BeautifulSoup('', 'html.parser') def inline(src, dst): for node in src.children: if isinstance(node, NavigableString): dst.append(str(node)) continue if node.name == 'br': dst.append(out.new_tag('br')) continue if node.name == 'a' and node.get('href'): href = node['href'] if 'google.com/url' in href: href = parse_qs(urlparse(href).query).get('q', [href])[0] a = out.new_tag('a', href=href, target='_blank', rel='noopener') inline(node, a) dst.append(a) continue if node.name == 'span': target = dst if has(node, 'font-weight:700'): b = out.new_tag('strong'); dst.append(b); target = b if has(node, 'font-style:italic'): i = out.new_tag('em'); target.append(i); target = i inline(node, target) continue if node.name in ('img', 'sup'): continue inline(node, dst) def empty(el): return not el.get_text(strip=True) def block(el, parent): name = el.name if name in ('h1', 'h2', 'h3', 'h4'): # ページの見出し（h1・シリーズの見出し h2・ラベル）の下に入るので、3段下げる h = out.new_tag(f'h{min(int(name[1]) + 2, 6)}') inline(el, h); parent.append(h) if not empty(el) else None elif name == 'p': if empty(el): return # 文書のタイトル（大きな文字の段落）は見出しにする。太字の印は見出しには付けない if max([size(el)] + [size(s) for s in el.find_all('span')]) >= 20: h = out.new_tag('h3'); h.string = el.get_text(strip=True); parent.append(h) return p = out.new_tag('p') inline(el, p); parent.append(p) elif name in ('ul', 'ol'): m = re.search(r'lst-kix_[\w]+-(\d+)', ' '.join(el.get('class') or [])) lv = int(m.group(1)) if m else 0 lst = out.new_tag(name) if lv: lst['class'] = f'lv{min(lv, 4)}' for li in el.find_all('li', recursive=False): n = out.new_tag('li'); inline(li, n); lst.append(n) parent.append(lst) elif name == 'table': t = out.new_tag('table') for i, tr in enumerate(el.find_all('tr')): r = out.new_tag('tr') for td in tr.find_all('td', recursive=False): c = out.new_tag('th' if i == 0 else 'td') for k in ('colspan', 'rowspan'): if td.get(k) and td[k] != '1': c[k] = td[k] for j, p in enumerate(td.find_all(['p', 'li'])): if j: c.append(out.new_tag('br')) inline(p, c) r.append(c) t.append(r) wrap = out.new_tag('div', attrs={'class': 'table'}) wrap.append(t); parent.append(wrap) elif name == 'hr': parent.append(out.new_tag('hr')) elif name == 'div': for c in el.children: if getattr(c, 'name', None): block(c, parent) for el in body.children: if getattr(el, 'name', None): block(el, out) html = str(out) # Google の空白の詰め物を整える html = html.replace('\xa0', ' ') return html def main(): data = {'sheets': {}, 'docs': {}} for key, sid in SHEETS.items(): data['sheets'][key] = fetch_sheet(sid) print('sheet', key, [(t['name'], len(t['rows'])) for t in data['sheets'][key]['tabs']]) for key, did in DOCS.items(): data['docs'][key] = {'html': clean_doc(get(DOC.format(id=did)))} print('doc', key, len(data['docs'][key]['html']), 'chars') with open(OUT, 'w', encoding='utf-8') as f: json.dump(data, f, ensure_ascii=False, indent=1) f.write('\n') print('->', OUT) if __name__ == '__main__': sys.exit(main())