tracker.ts

packages/sheets-client/src/tracker.ts

1. import { parseCsv } from './csv.js'
2. import { errorMessage } from '@meridian/shared'
4. /**
5. * One row of the Content Creation tracker, normalized.
6. *
7. * `owners` is a list because the sheet has shared rows: "Nora & Sam" on a
8. * photoshoot. A shared commitment counted once per person is three jobs where
9. * there is one (see assignmentGroups in the Ninety client), so the shape
10. * carries the truth and the caller decides how to count it.
11. */
12. export interface TrackerTask {
13. /** Stable within a sync: tab gid plus row number. The sheet has no ids. */
14. id: string
15. /** Which tab it came from, for the row's meta line. */
16. tab: string
17. title: string
18. /** As spelled in the sheet: first names, e.g. "Rafael". */
19. owners: string[]
20. /** Raw Status cell, trimmed. '' when the cell is blank. */
21. status: string
22. /** Free text: links, SKUs, references. Kept for the row subtitle. */
23. notes: string
24. /** MM/DD/YYYY as typed by a human, or null. Not parsed; see below. */
25. deadline: string | null
26. sku: string | null
27. /** True when more than one person is named on the row. */
28. shared: boolean
29. }
31. export interface TrackerFetch {
32. tasks: TrackerTask[]
33. /** Tabs that answered, and what each contributed. */
34. tabs: { label: string; gid: string; rows: number }[]
35. /**
36. * Tabs that failed, with the reason. A partial read is reported, not thrown:
37. * three tabs of four is most of the picture, and losing all of it because one
38. * tab was renamed would be the worse outcome. The caller must say so; this is
39. * the same contract as `truncated` on the Plinth sweep.
40. */
41. failedTabs: { label: string; gid: string; reason: string }[]
42. }
44. /** Column headers we need, matched case-insensitively and trimmed. */
45. const WANT = {
46. owner: ['owner'],
47. task: ['task'],
48. status: ['status'],
49. notes: ['notes/links/resources', 'notes/links'],
50. deadline: ['deadline'],
51. sku: ['sku'],
52. }
54. function headerIndex(header: string[]): Record<keyof typeof WANT, number> {
55. const norm = header.map((h) => h.trim().toLowerCase())
56. const find = (names: string[]): number => norm.findIndex((h) => names.includes(h))
57. return {
58. owner: find(WANT.owner),
59. task: find(WANT.task),
60. status: find(WANT.status),
61. notes: find(WANT.notes),
62. deadline: find(WANT.deadline),
63. sku: find(WANT.sku),
64. }
65. }
67. /**
68. * Split an Owner cell into people.
69. *
70. * "Nora & Sam" is one row owned by two. Splitting on the separators the sheet
71. * actually uses, rather than assuming one name per cell, is what keeps that row
72. * from resolving to nobody and landing in the unowned bucket.
73. */
74. export function splitOwners(cell: string): string[] {
75. return cell
76. .split(/\s*(?:&|\+|,|\band\b|\/)\s*/i)
77. .map((s) => s.trim())
78. .filter(Boolean)
79. }
81. /**
82. * A row is work if it names a task. Everything else in the tab is scaffolding.
83. *
84. * The tracker's tabs are padded with dozens of blank rows carrying only a
85. * trailing FALSE, which is what a spreadsheet looks like when someone dragged a
86. * checkbox down a column. Counting those as untitled work would put 40 phantom
87. * rows on the board, and counting them as anything at all would be reading
88. * formatting as data.
89. */
90. const isWork = (title: string, owner: string): boolean => title.trim() !== '' || owner.trim() !== ''
92. export function parseTab(csv: string, tab: string, gid: string): TrackerTask[] {
93. const rows = parseCsv(csv)
94. if (!rows.length) return []
96. // The header is not always row 0 and not always complete: Google's gviz export
97. // drops leading empty header cells, so a tab whose first columns are dated
98. // arrives with '' where 'Release Date' is. Find the row that actually names
99. // Owner and Task rather than trusting a position.
100. let headerRow = -1
101. let idx: Record<keyof typeof WANT, number> | null = null
102. for (let i = 0; i < Math.min(rows.length, 5); i++) {
103. const candidate = headerIndex(rows[i]!)
104. if (candidate.owner >= 0 && candidate.task >= 0) {
105. headerRow = i
106. idx = candidate
107. break
108. }
109. }
110. // No Owner/Task columns means this is not a work tab. Return nothing rather
111. // than inventing rows from whatever the first columns happened to be.
112. if (!idx || headerRow < 0) return []
114. const out: TrackerTask[] = []
115. for (let i = headerRow + 1; i < rows.length; i++) {
116. const r = rows[i]!
117. const cell = (n: number): string => (n >= 0 && n < r.length ? (r[n] ?? '').trim() : '')
118. const title = cell(idx.task)
119. const ownerCell = cell(idx.owner)
120. if (!isWork(title, ownerCell)) continue
121. const owners = splitOwners(ownerCell)
122. out.push({
123. id: `${gid}:${i + 1}`,
124. tab,
125. title,
126. owners,
127. status: cell(idx.status),
128. notes: cell(idx.notes),
129. // Not parsed into a date. These are hand-typed and inconsistent in the
130. // live sheet: "08/19/26" and "08/19/2026" and "8/21/2026" all appear.
131. // Guessing a year from a two-digit one, or a locale from a slash, is how
132. // you report something as overdue that is not. Shown as typed.
133. deadline: cell(idx.deadline) || null,
134. sku: cell(idx.sku) || null,
135. shared: owners.length > 1,
136. })
137. }
138. return out
139. }
141. /** Where a tab's CSV lives. Public document, no credential, no SDK. */
142. export function tabCsvUrl(sheetId: string, gid: string): string {
143. return `https://docs.google.com/spreadsheets/d/${sheetId}/gviz/tq?tqx=out:csv&headers=0&gid=${gid}`
144. }
146. /**
147. * Read every listed tab.
148. *
149. * Uses global `fetch` and nothing else, so this module is runtime-neutral and the
150. * Worker can import it. No googleapis, no node:fs, no credential (rule 7).
151. */
152. export async function fetchTracker(
153. sheetId: string,
154. tabs: { gid: string; label: string }[],
155. fetchImpl: typeof fetch = fetch,
156. ): Promise<TrackerFetch> {
157. const tasks: TrackerTask[] = []
158. const ok: TrackerFetch['tabs'] = []
159. const failed: TrackerFetch['failedTabs'] = []
161. for (const t of tabs) {
162. try {
163. const res = await fetchImpl(tabCsvUrl(sheetId, t.gid))
164. if (!res.ok) {
165. // 401/403 here means the document's link sharing was tightened. Say
166. // that, rather than "request failed". It is the one cause a reader can
167. // act on, and it is observable from the status code rather than guessed.
168. const hint =
169. res.status === 401 || res.status === 403
170. ? ': the sheet is no longer readable by link, which is how this source is read at all'
171. : ''
172. failed.push({ ...t, reason: `HTTP ${res.status}${hint}` })
173. continue
174. }
175. const rows = parseTab(await res.text(), t.label, t.gid)
176. tasks.push(...rows)
177. ok.push({ label: t.label, gid: t.gid, rows: rows.length })
178. } catch (err) {
179. failed.push({ ...t, reason: errorMessage(err) })
180. }
181. }
183. return { tasks, tabs: ok, failedTabs: failed }
184. }