Google Apps Script: Ingest Aroya + TrolMaster + OpenSprinkler → Google Sheets (LLM-ready, °F) — Milestones

**OVERVIEW**
Build a Google **Apps Script** that automatically pulls data from **Aroya (API key available), TrolMaster, and OpenSprinkler** and writes to **Google Sheets** every 5–10 minutes. Use a tidy, consistent schema so ChatGPT/Grok/Gemini can analyze irrigation vs EC/WC and recommend schedule changes. All temperatures must be in **Fahrenheit**.

**STACK (must use)**
- Google Apps Script only (no servers)
- `UrlFetchApp`, `PropertiesService` (secrets + cursors), `SpreadsheetApp` with **batched `setValues()`**
- No Zapier/Make for core ingestion

**SOURCES**
- **Aroya:** official API with my key (readings + room mapping)
- **TrolMaster:** API if available; otherwise Drive/Gmail-fed **CSV** ingestion
- **OpenSprinkler:** local HTTP API (station/program on/off, durations, flow/pressure if present)

**GOOGLE SHEETS TABS (template provided)**
`aroya_raw`, `trolmaster_raw`, `opensprinkler_raw`, `unified_timeseries`, `daily_rollups`, `views_for_llm`, `data_dictionary`, `errors_log`, `settings`

**RAW ROW SCHEMA (exact order for all raw/unified tabs)**
`timestamp_utc, local_tz, room, zone, sensor_id, metric, value, unit, source, source_id`

**STANDARDS**
- Timestamps: store **UTC** in raw; `local_tz = "America/Los_Angeles"`
- Units: EC **mS/cm**; temperature **°F** (`metric = temp_f`). If a source returns °C, convert: `temp_f = temp_c*9/5 + 32`; set `unit="°F"` and `metric="temp_f"`
- **Dedupe key:** `{source, source_id}`; if no ID, hash `source|sensor_id|timestamp_utc|metric` (Apps Script `Utilities.computeDigest`)
- **Cursoring:** keep per-source last-sync ISO timestamp in `settings` tab
- **Triggers:** time-driven every 5–10 min; daily rollups job
- **Errors:** log to `errors_log` (`timestamp_utc, stage, message, payload_excerpt`) **and** send email via `MailApp`
- **Quotas:** batch reads/writes; page external APIs; avoid `appendRow` loops
- **Security:** keys in Script Properties; read-only scope; ToS-compliant (no scraping without written approval)

**DELIVERABLES**
1) Working scheduled ingestion to my Sheet (all three sources)
2) **30-day backfill** (no duplicates) across sources
3) `daily_rollups` logic + two simple charts (EC vs WC vs irrigation)
4) 3 LLM views in `views_for_llm`:
- `R1_last7days` (Room 1 EC/WC/PPFD/VPD/CO₂/**temp_f**)
- `Schedule_Today` (today’s irrigation with durations + est mL)
- `Env_Today` (today’s **temp_f**, RH, VPD, PPFD, CO₂)
5) README: setup, where to put keys, set triggers, add rooms/sensors, troubleshooting

**MILESTONES & PAYMENTS (fixed-price; propose within total $600–$1,200)**
- **M1 — Aroya → Sheet** (°F + cursor + dedupe + errors) **+ 30-day backfill**
*Acceptance:* run `syncAroya()` → rows in `aroya_raw`; temp stored as `temp_f`; re-run adds **no duplicates**; bad key writes `errors_log` & sends email; time trigger configured.
- **M2 — OpenSprinkler → Sheet** (cursor + dedupe) & unify mapping
*Acceptance:* toggling a station writes rows to `opensprinkler_raw` within 10 min; re-run no duplicates; `unified_timeseries` fills for both sources.
- **M3 — TrolMaster ingestion** (API or CSV), unify all, `daily_rollups`, 3 LLM views, 2 charts
*Acceptance:* `trolmaster_raw` populated; `unified_timeseries` shows all sources; `daily_rollups` filled for yesterday/today; LLM views populated; charts render.

**ACCEPTANCE TESTS (summary)**
- Write test → correct raw tab + exact schema; temp as **`temp_f` (°F)**
- Dedupe test → immediate re-run adds **NO** duplicates
- Cursor test → older cursor only appends newer records; cursor advances
- Error test → invalid key logs to `errors_log` **and** emails me
- Trigger → cron-style time trigger runs every 5–10 min
- **30-day backfill** completes without duplicates

**WHAT I WILL PROVIDE**
- Google Sheet ID (template preloaded)
- Aroya API key (read-only) + any org/room IDs
- OpenSprinkler IP/port + auth
- TrolMaster API details **OR** sample CSVs + export cadence

**WHAT TO INCLUDE IN YOUR PROPOSAL**
- 3–5 sentence architecture (cursoring, dedupe, °F conversion, quotas)
- A short Apps Script snippet you wrote using `UrlFetchApp` + batched `setValues()` (no secrets)
- Fixed-price quote split by the 3 milestones + timeline (start date & total days)
- Confirmation you’ll deliver clean, commented code + README and keep raw tabs free of formulas/merged cells

**Notes:** Apps Script only; read-only credentials in Script Properties; keep everything ToS-compliant.