← back to Sdcc Journalists Viewer
SDCC journalists viewer: Drive-synced spreadsheet viewer w/ field toggles + active links
00fd144d0ae273b65c6988b18f07522e277ea982 · 2026-07-23 10:02:28 -0700 · Steve Abrams
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Files touched
A .deploy.confA .gitignoreA data/journalists.jsonA public/index.htmlA scripts/refresh.pyA server.js
Diff
commit 00fd144d0ae273b65c6988b18f07522e277ea982
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Jul 23 10:02:28 2026 -0700
SDCC journalists viewer: Drive-synced spreadsheet viewer w/ field toggles + active links
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
---
.deploy.conf | 3 +
.gitignore | 9 ++
data/journalists.json | 1 +
public/index.html | 221 ++++++++++++++++++++++++++++++++++++++++++++++++++
scripts/refresh.py | 54 ++++++++++++
server.js | 71 ++++++++++++++++
6 files changed, 359 insertions(+)
diff --git a/.deploy.conf b/.deploy.conf
new file mode 100644
index 0000000..5b4c32b
--- /dev/null
+++ b/.deploy.conf
@@ -0,0 +1,3 @@
+PROJECT_NAME=sdcc-journalists-viewer
+DEPLOY_PATH=/root/Projects/sdcc-journalists-viewer
+HEALTH_URL=http://127.0.0.1:9878/healthz
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..ca6abc3
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,9 @@
+node_modules/
+.env*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
+data/*.xlsx
diff --git a/data/journalists.json b/data/journalists.json
new file mode 100644
index 0000000..48d9b03
--- /dev/null
+++ b/data/journalists.json
@@ -0,0 +1 @@
+{"generated_at": "2026-07-23T10:02:27.562450-07:00", "drive_pull_ok": true, "source": "gdrive:SDCC/sdcc-journalists-media-contacts.xlsx", "count": 38, "rows": [{"name": "Jillian Berman", "outlet_on_file": "MarketWatch", "current_outlet_verified": "MarketWatch", "medium": "Print/Digital", "beat": "Student Debt", "email": "", "email_source": "not found", "phone": "", "twitter": "@jaborman", "location": "", "latest_article": "https://muckrack.com/jillian-berman/articles", "article_date": "2026-07", "link_type": "author_page", "articles_tracked_10yr": "69", "last_tracked_date": "2026-02-19", "first_contact": "2026-06-24", "initial_email": "Open in Gmail", "last_contact": "2026-06-24", "last_email": "Open in Gmail", "contact_source": "gmail correspondence (name match)", "notes": "Actively publishing July 2026 on OBBB student-loan caps; MarketWatch blocks crawlers \u2014 byline page given. Author of 'Sunk Cost' (2025).", "_links": {"latest_article": "https://muckrack.com/jillian-berman/articles", "initial_email": "https://mail.google.com/mail/u/natalia@studentdebtcrisis.org/#all/19ef9d11379f7fd5", "last_email": "https://mail.google.com/mail/u/natalia@studentdebtcrisis.org/#all/19ef9d11379f7fd5"}}, {"name": "Jessica Priest", "outlet_on_file": "The Texas Tribune", "current_outlet_verified": "The Texas Tribune (with Open Campus)", "medium": "Print/Digital", "beat": "Higher education", "email": "jessica.priest@texastribune.org", "email_source": "SDCC database", "phone": "512-716-8600", "twitter": "", "location": "TX", "latest_article": "Student success should mean more state money for universities, Texas agency suggests", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "0", "last_tracked_date": "", "first_contact": "2026-03-19", "initial_email": "Open in Gmail", "last_contact": "2026-06-03", "last_email": "Open in Gmail", "contact_source": "gmail correspondence (email match)", "notes": "Email on Tribune staff page.", "_links": {"latest_article": "https://www.texastribune.org/2026/07/22/texas-universities-performanced-based-funding-recommendation/", "initial_email": "https://mail.google.com/mail/u/natalia@studentdebtcrisis.org/#all/19d093759a42a8d7", "last_email": "https://mail.google.com/mail/u/natalia@studentdebtcrisis.org/#all/19e90f44246291fd"}}, {"name": "Michael Stratford", "outlet_on_file": "Politico", "current_outlet_verified": "Politico (moved to economy team \u2014 banking/financial regulation, Treasury, student debt)", "medium": "Print/Digital", "beat": "Education Policy", "email": "mstratford@politico.com", "email_source": "publicly listed (web-verified)", "phone": "", "twitter": "@maboratford", "location": "", "latest_article": "Trump administration explores selling off parts of the $1.6 trillion federal student loan portfolio", "article_date": "2025-10", "link_type": "author_page", "articles_tracked_10yr": "73", "last_tracked_date": "2026-01-19", "first_contact": "2026-04-06", "initial_email": "Open in Gmail", "last_contact": "2026-04-06", "last_email": "Open in Gmail", "contact_source": "gmail correspondence (email match)", "notes": "Email self-posted on X ('Add me to your press lists'). Politico direct URLs blocked; Oct 2025 loan-portfolio-sale scoop widely cited.", "_links": {"latest_article": "https://muckrack.com/mstratford/articles", "initial_email": "https://mail.google.com/mail/u/natalia@studentdebtcrisis.org/#all/19d635ef313effc8", "last_email": "https://mail.google.com/mail/u/natalia@studentdebtcrisis.org/#all/19d635ef313effc8"}}, {"name": "Katie Lobosco", "outlet_on_file": "CNN", "current_outlet_verified": "Tax Notes (LEFT CNN ~early 2025)", "medium": "TV", "beat": "Politics & Policy", "email": "", "email_source": "not found", "phone": "", "twitter": "@KaborLobosco", "location": "", "latest_article": "Latest Republican Tax Proposals Stall Ahead of Midterms", "article_date": "2026-07-13", "link_type": "article", "articles_tracked_10yr": "81", "last_tracked_date": "2026-02-27", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Now Capitol Hill tax-policy reporter \u2014 no longer student-loan beat.", "_links": {"latest_article": "https://www.taxnotes.com/featured-news/latest-republican-tax-proposals-stall-ahead-midterms/2026/07/13/7wdd3"}}, {"name": "Danielle Douglas-Gabriel", "outlet_on_file": "The Washington Post", "current_outlet_verified": "The Washington Post (education finance)", "medium": "Print/Digital", "beat": "Higher Education", "email": "", "email_source": "not found", "phone": "", "twitter": "@DaniDougGabriel", "location": "", "latest_article": "Americans say four-year colleges cost too much", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "80", "last_tracked_date": "2026-02-14", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Running a July 2026 paying-for-college reader series. Email not verifiable.", "_links": {"latest_article": "https://www.washingtonpost.com/education/2026/07/22/americans-say-four-year-colleges-cost-too-much/"}}, {"name": "Stacy Cowley", "outlet_on_file": "The New York Times", "current_outlet_verified": "The New York Times", "medium": "Print/Digital", "beat": "Consumer Finance", "email": "", "email_source": "not found", "phone": "", "twitter": "@staborwley", "location": "", "latest_article": "Trump Wants to Create More Banks. Many Firms Are Heeding His Call", "article_date": "2026-07", "link_type": "author_page", "articles_tracked_10yr": "80", "last_tracked_date": "2026-02-10", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "NYT URLs not retrievable \u2014 byline aggregator given. No public email.", "_links": {"latest_article": "https://muckrack.com/stacycowley/articles"}}, {"name": "Cory Turner", "outlet_on_file": "NPR", "current_outlet_verified": "NPR (Ed team correspondent/senior editor)", "medium": "Radio", "beat": "Education Policy", "email": "", "email_source": "not found", "phone": "", "twitter": "@caborturner", "location": "", "latest_article": "Under a new federal rule, colleges must leave grads better off or lose financial aid", "article_date": "2026-06-30", "link_type": "article", "articles_tracked_10yr": "75", "last_tracked_date": "2026-02-15", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "No public email.", "_links": {"latest_article": "https://www.npr.org/2026/06/30/nx-s1-5835631/turner-camhi-do-no-harm-college-loans"}}, {"name": "Tara Siegel Bernard", "outlet_on_file": "The New York Times", "current_outlet_verified": "The New York Times", "medium": "Print/Digital", "beat": "Personal Finance", "email": "", "email_source": "not found", "phone": "", "twitter": "@bernaborara", "location": "", "latest_article": "A Longer Life Can Lead to Financial Concerns, and More Questions", "article_date": "2026-04-29", "link_type": "author_page", "articles_tracked_10yr": "75", "last_tracked_date": "2026-02-16", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Direct NYT URL not retrievable \u2014 byline aggregator given. No public email.", "_links": {"latest_article": "https://muckrack.com/tarasbernard/articles"}}, {"name": "Zack Friedman", "outlet_on_file": "Forbes", "current_outlet_verified": "Mentor (mentormoney.com), founder/CEO \u2014 Forbes byline inactive since Aug 2022", "medium": "Print/Digital", "beat": "Student Loans", "email": "", "email_source": "not found", "phone": "", "twitter": "@zackfriedman", "location": "", "latest_article": "Biden Cancels Student Loans And Extends Student Loan Payment Pause", "article_date": "2022-08-24", "link_type": "article", "articles_tracked_10yr": "75", "last_tracked_date": "2026-02-23", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "CAUTION: no 2025-2026 bylines \u2014 consider deprioritizing; Forbes student-loan coverage now under Forbes Advisor staff.", "_links": {"latest_article": "https://www.forbes.com/sites/zackfriedman/2022/08/24/biden-cancels-student-loans-and-extends-student-loan-payment-pause/"}}, {"name": "Tariq Habash", "outlet_on_file": "The Century Foundation", "current_outlet_verified": "A New Policy (founder) / Student Borrower Protection Center senior fellow \u2014 no longer at The Century Foundation", "medium": "Print/Digital", "beat": "Education Equity", "email": "", "email_source": "not found", "phone": "", "twitter": "@TariqHabash", "location": "", "latest_article": "Banning Foreign Students Is the Opposite of 'Making America Great'", "article_date": "2025-06-18", "link_type": "article", "articles_tracked_10yr": "73", "last_tracked_date": "2026-02-17", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Op-ed in The Nation. TCF \u2192 SBPC \u2192 ED policy advisor (resigned Jan 2024) \u2192 A New Policy. No public email.", "_links": {"latest_article": "https://www.thenation.com/article/society/banning-foreign-students-is-the-opposite-of-making-america-great/"}}, {"name": "Chris Quintana", "outlet_on_file": "USA Today", "current_outlet_verified": "USA Today (investigative, higher ed)", "medium": "Print/Digital", "beat": "Higher Education", "email": "", "email_source": "not found", "phone": "", "twitter": "@ChrisQuintana", "location": "", "latest_article": "Next generation of nurses at risk with proposed student loan caps", "article_date": "2026-01-16", "link_type": "article", "articles_tracked_10yr": "69", "last_tracked_date": "2026-02-08", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "AOL syndication (USA Today blocks crawlers); has more recent Feb 2026 bylines without verifiable URLs.", "_links": {"latest_article": "https://www.aol.com/articles/next-generation-nurses-risk-proposed-213531292.html"}}, {"name": "Ryan Lane", "outlet_on_file": "NerdWallet", "current_outlet_verified": "NerdWallet (managing editor; moved toward small-business but retains student-loan bylines)", "medium": "Print/Digital", "beat": "Student Loans", "email": "", "email_source": "not found", "phone": "", "twitter": "@aborlane", "location": "", "latest_article": "3 Ways to Lower Your Student Loan Interest Rate", "article_date": "2026-06-18", "link_type": "article", "articles_tracked_10yr": "69", "last_tracked_date": "2026-02-12", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Date = last-updated (evergreen model). No public email.", "_links": {"latest_article": "https://www.nerdwallet.com/student-loans/learn/how-to-lower-student-loan-interest-rate"}}, {"name": "Aarthi Swaminathan", "outlet_on_file": "Yahoo Finance", "current_outlet_verified": "MarketWatch", "medium": "Print/Digital", "beat": "Economy & Policy", "email": "aarthi@marketwatch.com", "email_source": "publicly listed (web-verified)", "phone": "", "twitter": "@aaborwami", "location": "", "latest_article": "10 U.S. metro areas where builders are slashing prices on new homes", "article_date": "2026-07-17", "link_type": "article", "articles_tracked_10yr": "67", "last_tracked_date": "2025-12-22", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Moved from Yahoo Finance to MarketWatch (now housing/real-estate beat). URL is Yahoo syndication of her MarketWatch piece. Email self-published in her X post soliciting sources.", "_links": {"latest_article": "https://finance.yahoo.com/real-estate/articles/10-u-metro-areas-where-001000782.html"}}, {"name": "Adam Minsky", "outlet_on_file": "Forbes", "current_outlet_verified": "Forbes (senior contributor)", "medium": "Print/Digital", "beat": "Student Loan Law", "email": "", "email_source": "not found", "phone": "", "twitter": "@AdamSMinsky", "location": "", "latest_article": "Education Department Must Wipe Out Student Loans For 500,000 Borrowers Under Settlement, Says Court", "article_date": "2026-07-21", "link_type": "article", "articles_tracked_10yr": "67", "last_tracked_date": "2026-02-27", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Publishes multiple times weekly on student-loan law; no public email found.", "_links": {"latest_article": "https://www.forbes.com/sites/adamminsky/2026/07/21/education-department-must-wipe-out-student-loans-for-500000-borrowers-under-settlement-says-court/"}}, {"name": "Annie Nova", "outlet_on_file": "CNBC", "current_outlet_verified": "CNBC", "medium": "TV", "beat": "Personal Finance", "email": "", "email_source": "not found", "phone": "", "twitter": "@annianova", "location": "", "latest_article": "Student loan borrowers on new RAP plan can lose key benefits if they pay even one day late", "article_date": "2026-07-12", "link_type": "article", "articles_tracked_10yr": "67", "last_tracked_date": "2026-02-02", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Active on RAP/SAVE transition coverage. No public email found.", "_links": {"latest_article": "https://www.cnbc.com/2026/07/12/student-loans-rap-late-payment.html"}}, {"name": "Ayelet Sheffey", "outlet_on_file": "Business Insider", "current_outlet_verified": "Business Insider (senior economic policy reporter)", "medium": "Print/Digital", "beat": "Policy & Economy", "email": "", "email_source": "not found", "phone": "", "twitter": "@ayaborffey", "location": "", "latest_article": "Latest Trump administration email pushes SAVE student-loan borrowers to find a new repayment plan \u2014 now", "article_date": "2026-07", "link_type": "article", "articles_tracked_10yr": "65", "last_tracked_date": "2026-02-17", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "AOL syndication of her BI piece (BI blocks crawlers). No public email found.", "_links": {"latest_article": "https://www.aol.com/articles/latest-trump-administration-email-pushes-185705250.html"}}, {"name": "Jessica Dickler", "outlet_on_file": "CNBC", "current_outlet_verified": "CNBC", "medium": "TV", "beat": "Personal Finance", "email": "", "email_source": "not found", "phone": "", "twitter": "@jaborickler", "location": "", "latest_article": "BlackRock, Carhartt, Ford and Google launch skilled trades workforce initiative", "article_date": "2026-07-21", "link_type": "article", "articles_tracked_10yr": "62", "last_tracked_date": "2026-02-22", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "No public email found.", "_links": {"latest_article": "https://www.cnbc.com/2026/07/21/blackrock-carhartt-ford-google-skilled-trades.html"}}, {"name": "Laura Beamer", "outlet_on_file": "Jain Family Institute", "current_outlet_verified": "Jain Family Institute", "medium": "Print/Digital", "beat": "Student Debt Research", "email": "", "email_source": "not found", "phone": "", "twitter": "@laboramer", "location": "", "latest_article": "America's Student Loans Were Never Going to Be Repaid (NYT interactive, with Marshall Steinbaum)", "article_date": "2023-07-13", "link_type": "author_page", "articles_tracked_10yr": "62", "last_tracked_date": "2026-01-10", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Researcher, not reporter \u2014 no 2025-2026 bylines found; latest major byline July 2023.", "_links": {"latest_article": "https://jainfamilyinstitute.org/our-team/laura-beamer/"}}, {"name": "Charlie Eaton", "outlet_on_file": "UC Merced / Academic", "current_outlet_verified": "UC Merced (Assoc. Professor; co-founder HERE Lab)", "medium": "Print/Digital", "beat": "Higher Ed Finance", "email": "", "email_source": "not found", "phone": "", "twitter": "@CharlieDEaton", "location": "", "latest_article": "White Flight in Public Higher Education? Racial Avoidance of Hispanic-Serving Institutions (Sociology of Education)", "article_date": "2025", "link_type": "article", "articles_tracked_10yr": "57", "last_tracked_date": "2025-11-12", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Academic, not journalist \u2014 latest is a 2025 peer-reviewed article. Faculty-page email not verifiable.", "_links": {"latest_article": "https://journals.sagepub.com/doi/10.1177/00380407251352786"}}, {"name": "Adam Echelman", "outlet_on_file": "CalMatters", "current_outlet_verified": "CalMatters (with Open Campus)", "medium": "Print/Digital", "beat": "Higher education; workforce/job training; community colleges; student data privacy", "email": "aechelman@calmatters.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "CA", "latest_article": "A new law holds colleges accountable if their grads don't earn more than $36,000 a year", "article_date": "2026-07-16", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-16", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on CalMatters author page.", "_links": {"latest_article": "https://calmatters.org/education/higher-education/2026/07/college-graduate-earnings-california/"}}, {"name": "Aleksandra Appleton", "outlet_on_file": "Chalkbeat", "current_outlet_verified": "Chalkbeat Indiana", "medium": "Print/Digital", "beat": "Indiana education policy; K-12 statewide", "email": "aappleton@chalkbeat.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "IN", "latest_article": "IPEC chooses charter school policy leader to guide its work", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-02-02", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on Chalkbeat author page.", "_links": {"latest_article": "https://www.chalkbeat.org/indiana/2026/07/22/karega-rausch-named-ipec-executive-director/"}}, {"name": "Carolyn Jones", "outlet_on_file": "CalMatters", "current_outlet_verified": "CalMatters", "medium": "Print/Digital", "beat": "K-12; special education; school funding; inequity/student achievement; policy", "email": "carolyn@calmatters.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "CA", "latest_article": "California reading scores are up, thanks to literacy investments. Can the state do more?", "article_date": "2026-07-20", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-11", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on CalMatters author page.", "_links": {"latest_article": "https://calmatters.org/education/k-12-education/2026/07/literacy-california/"}}, {"name": "Emma Epperly", "outlet_on_file": "Idaho Education News", "current_outlet_verified": "Idaho Education News", "medium": "Print/Digital", "beat": "Idaho K-12; North Idaho regional; special education", "email": "", "email_source": "not found", "phone": "", "twitter": "", "location": "ID", "latest_article": "Idaho's average per-pupil spending edges up", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-24", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "No email displayed on author page.", "_links": {"latest_article": "https://www.idahoednews.org/top-news/average-idaho-per-pupil-spending-edges-up/"}}, {"name": "Hannah Dellinger", "outlet_on_file": "Chalkbeat", "current_outlet_verified": "Chalkbeat Detroit", "medium": "Print/Digital", "beat": "Detroit schools; absenteeism; district operations", "email": "hdellinger@chalkbeat.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "Detroit MI", "latest_article": "A crowded field: 35 are in the race for 5 seats on the Detroit school board", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-02-26", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on Chalkbeat author page.", "_links": {"latest_article": "https://www.chalkbeat.org/detroit/2026/07/22/dpscd-candidates-running-for-the-detroit-school-board-in-nov-3-election/"}}, {"name": "Howard Blume", "outlet_on_file": "Los Angeles Times", "current_outlet_verified": "Los Angeles Times", "medium": "Print/Digital", "beat": "Education (K-12 and district policy)", "email": "howard.blume@latimes.com", "email_source": "publicly listed (web-verified)", "phone": "", "twitter": "", "location": "CA", "latest_article": "Falling enrollment. Budget uncertainty. LAUSD warns of layoffs and cuts", "article_date": "2026-07", "link_type": "author_page", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-24", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "LAT blocks crawlers \u2014 byline page given. Email surfaced in public journalist listings.", "_links": {"latest_article": "https://muckrack.com/howard-blume/articles"}}, {"name": "Jaweed Kaleem", "outlet_on_file": "Los Angeles Times", "current_outlet_verified": "Los Angeles Times", "medium": "Print/Digital", "beat": "University of California; higher education; campus activism; free speech; religion; race; politics", "email": "", "email_source": "not found", "phone": "", "twitter": "", "location": "CA", "latest_article": "UC asks California Supreme Court to block release of Trump's UCLA settlement offer", "article_date": "2026-07", "link_type": "author_page", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-19", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "LAT blocks crawlers \u2014 byline page given. Covering UC/higher-ed incl. $1.2B DOJ-UCLA settlement fight.", "_links": {"latest_article": "https://muckrack.com/jaweedkaleem/articles"}}, {"name": "Kevin Richert", "outlet_on_file": "Idaho Education News", "current_outlet_verified": "Idaho Education News", "medium": "Print/Digital", "beat": "Education politics; education policy; higher education", "email": "krichert@idahoednews.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "ID", "latest_article": "How the feds' healthcare grants could affect Idaho education: Three examples", "article_date": "2026-07-21", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-03", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Byline, date, email verified on article page.", "_links": {"latest_article": "https://www.idahoednews.org/top-news/how-the-feds-healthcare-grants-could-affect-idaho-education-three-examples/"}}, {"name": "Melanie Asmar", "outlet_on_file": "Chalkbeat", "current_outlet_verified": "Chalkbeat Colorado (Bureau Chief)", "medium": "Print/Digital", "beat": "Denver Public Schools; school board governance; K-12 policy", "email": "masmar@chalkbeat.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "CO", "latest_article": "Free school supplies, haircuts, vaccines, and more. Check out these 2026 back-to-school events", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-01-21", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on Chalkbeat author page.", "_links": {"latest_article": "https://www.chalkbeat.org/colorado/2026/07/22/free-back-to-school-supplies-backpacks-haircuts-in-denver-metro-area/"}}, {"name": "Mikhail Zinshteyn", "outlet_on_file": "CalMatters", "current_outlet_verified": "CalMatters", "medium": "Print/Digital", "beat": "Higher education; state legislation; financial aid; labor issues; campus housing; college affordability", "email": "mikhail@calmatters.org", "email_source": "SDCC database", "phone": "", "twitter": "mzinshteyn", "location": "CA", "latest_article": "White House admits it used keywords to kill billions worth of California research grants", "article_date": "2026-07-21", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-11", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on CalMatters author page.", "_links": {"latest_article": "https://calmatters.org/education/higher-education/2026/07/research-grants-california-2/"}}, {"name": "Ryan Suppe", "outlet_on_file": "Idaho Education News", "current_outlet_verified": "Idaho Education News (senior reporter, K-12 policy)", "medium": "Print/Digital", "beat": "Education policy; K-12 focus", "email": "ryan@idahoednews.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "ID", "latest_article": "Former Nampa school board chair resigns", "article_date": "2026-07-21", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-02", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on IEN author page; LinkedIn confirms current role.", "_links": {"latest_article": "https://www.idahoednews.org/news/former-nampa-school-board-chairwoman-resigns/"}}, {"name": "Sean Dolan", "outlet_on_file": "Idaho Education News", "current_outlet_verified": "Idaho Education News", "medium": "Print/Digital", "beat": "Education state policy; local district governance", "email": "sean@idahoednews.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "ID", "latest_article": "Schools face soaring laptop costs as AI data centers squeeze supply", "article_date": "2026-07-20", "link_type": "article", "articles_tracked_10yr": "3", "last_tracked_date": "2026-03-25", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "BoiseDev syndication of his IEN piece. Email on IEN author page.", "_links": {"latest_article": "https://boisedev.com/news/2026/07/20/schools-face-soaring-laptop-costs-as-ai-data-centers-squeeze-supply/"}}, {"name": "Melissa Brown", "outlet_on_file": "Chalkbeat", "current_outlet_verified": "Chalkbeat Tennessee (Bureau Chief)", "medium": "Print/Digital", "beat": "Tennessee education policy; school choice/vouchers; immigration impacts on schools", "email": "mbrown@chalkbeat.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "TN", "latest_article": "Tennessee considers easing teacher licensure testing requirements", "article_date": "2026-07-08", "link_type": "article", "articles_tracked_10yr": "2", "last_tracked_date": "2026-03-10", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on Chalkbeat author page. Leading Memphis-Shelby state-takeover coverage.", "_links": {"latest_article": "https://www.chalkbeat.org/tennessee/2026/07/08/will-tennessee-drop-edtpa-requirement-for-teacher-licensure/"}}, {"name": "Reema Amin", "outlet_on_file": "Chalkbeat", "current_outlet_verified": "Chalkbeat Chicago", "medium": "Print/Digital", "beat": "Chicago Public Schools", "email": "ramin@chalkbeat.org", "email_source": "SDCC database", "phone": "", "twitter": "", "location": "Chicago IL", "latest_article": "Chicago Public Schools lays off 162 central and citywide employees", "article_date": "2026-07-13", "link_type": "article", "articles_tracked_10yr": "1", "last_tracked_date": "2026-03-20", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email publicly listed by Chalkbeat.", "_links": {"latest_article": "https://www.chalkbeat.org/chicago/2026/07/13/cps-lays-off-162-central-and-citywide-employees/"}}, {"name": "Jaden Edison", "outlet_on_file": "The Texas Tribune", "current_outlet_verified": "The Texas Tribune", "medium": "Print/Digital", "beat": "Public education", "email": "jaden.edison@texastribune.org", "email_source": "SDCC database", "phone": "512-716-8600", "twitter": "", "location": "TX", "latest_article": "Texas' longstanding 'Robin Hood' school finance system will face a new legal challenge", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "0", "last_tracked_date": "", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Email on Tribune staff page.", "_links": {"latest_article": "https://www.texastribune.org/2026/07/22/west-texas-midland-district-lawsuit-school-finance-robin-hood/"}}, {"name": "jillian", "outlet_on_file": "", "current_outlet_verified": "", "medium": "Print/Digital", "beat": "General", "email": "", "email_source": "not found", "phone": "", "twitter": "", "location": "", "latest_article": "", "article_date": "", "link_type": "", "articles_tracked_10yr": "0", "last_tracked_date": "", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "DATA-ENTRY STUB \u2014 no outlet on file", "_links": {}}, {"name": "marka", "outlet_on_file": "", "current_outlet_verified": "", "medium": "Print/Digital", "beat": "General", "email": "", "email_source": "not found", "phone": "", "twitter": "", "location": "", "latest_article": "", "article_date": "", "link_type": "", "articles_tracked_10yr": "0", "last_tracked_date": "", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "DATA-ENTRY STUB \u2014 no outlet on file", "_links": {}}, {"name": "news day", "outlet_on_file": "", "current_outlet_verified": "", "medium": "Print/Digital", "beat": "General", "email": "", "email_source": "not found", "phone": "", "twitter": "", "location": "", "latest_article": "", "article_date": "", "link_type": "", "articles_tracked_10yr": "0", "last_tracked_date": "", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "DATA-ENTRY STUB \u2014 no outlet on file", "_links": {}}, {"name": "Sneha Dey", "outlet_on_file": "The Texas Tribune", "current_outlet_verified": "The Texas Tribune", "medium": "Print/Digital", "beat": "Public education", "email": "sneha.dey@texastribune.org", "email_source": "SDCC database", "phone": "512-716-8600", "twitter": "", "location": "Austin TX", "latest_article": "Higher education agency prepares to cut 3% of spending as ordered by Texas leaders", "article_date": "2026-07-22", "link_type": "article", "articles_tracked_10yr": "0", "last_tracked_date": "", "first_contact": "", "initial_email": "", "last_contact": "", "last_email": "", "contact_source": "no contact on record", "notes": "Beat now education-to-employment pathways, community colleges, higher ed. Email on Tribune author page.", "_links": {"latest_article": "https://www.texastribune.org/2026/07/22/texas-higher-education-spending-cuts/"}}]}
\ No newline at end of file
diff --git a/public/index.html b/public/index.html
new file mode 100644
index 0000000..feea6f3
--- /dev/null
+++ b/public/index.html
@@ -0,0 +1,221 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width,initial-scale=1">
+<title>SDCC Media Contacts — Journalist Finder</title>
+<style>
+:root { --cols: 3; --scale: 1; --bg:#0C0F1A; --surface:#131829; --el:#1C2237; --pri:#10b981; --amber:#f59e0b; --text:#f0f0f5; --sub:#a1a1b5; --muted:#6B7280; --border:#2A3048; }
+* { box-sizing: border-box; }
+body { margin:0; background:var(--bg); color:var(--text); font:14px/1.45 -apple-system,BlinkMacSystemFont,"Segoe UI",sans-serif; }
+a { color:var(--pri); text-decoration:none; } a:hover { text-decoration:underline; }
+header { display:flex; align-items:center; gap:14px; padding:14px 20px; border-bottom:1px solid var(--border); position:sticky; top:0; background:var(--bg); z-index:5; flex-wrap:wrap; }
+header h1 { font-size:17px; margin:0; letter-spacing:.02em; }
+header .sync { font-size:12px; color:var(--sub); margin-left:auto; display:flex; gap:10px; align-items:center; }
+button { background:var(--el); color:var(--text); border:1px solid var(--border); border-radius:8px; padding:6px 12px; cursor:pointer; font-size:12.5px; }
+button:hover { border-color:var(--pri); }
+.layout { display:flex; min-height:calc(100vh - 60px); }
+aside { width:250px; flex:0 0 250px; border-right:1px solid var(--border); padding:14px; position:sticky; top:60px; align-self:flex-start; max-height:calc(100vh - 60px); overflow-y:auto; }
+aside h3 { font-size:11px; text-transform:uppercase; letter-spacing:.1em; color:var(--muted); margin:16px 0 8px; }
+aside h3:first-child { margin-top:0; }
+.field-toggle { display:flex; align-items:center; gap:8px; padding:4px 2px; font-size:13px; color:var(--sub); cursor:pointer; user-select:none; }
+.field-toggle input { accent-color:var(--pri); }
+.field-toggle:hover { color:var(--text); }
+main { flex:1; padding:16px 20px; }
+.controls { display:flex; gap:12px; align-items:center; flex-wrap:wrap; margin-bottom:14px; }
+.controls input[type=search] { background:var(--el); border:1px solid var(--border); border-radius:8px; color:var(--text); padding:7px 12px; width:260px; font-size:13px; }
+.controls select { background:var(--el); border:1px solid var(--border); border-radius:8px; color:var(--text); padding:7px 10px; font-size:13px; }
+.controls .density { display:flex; align-items:center; gap:6px; font-size:12px; color:var(--sub); }
+.controls input[type=range] { accent-color:var(--pri); width:120px; }
+.count { font-size:12px; color:var(--muted); }
+.grid { display:grid; grid-template-columns:repeat(var(--cols),1fr); gap:12px; }
+.card { background:var(--surface); border:1px solid var(--border); border-radius:12px; padding:calc(14px*var(--scale)); font-size:calc(13px*var(--scale)); display:flex; flex-direction:column; gap:6px; }
+.card .name { font-weight:700; font-size:calc(15.5px*var(--scale)); }
+.card .outlet { color:var(--amber); font-size:calc(12.5px*var(--scale)); }
+.badge { display:inline-block; background:var(--el); border:1px solid var(--border); border-radius:99px; padding:1px 9px; font-size:calc(11px*var(--scale)); color:var(--sub); margin-right:4px; }
+.badge.contact { border-color:var(--pri); color:var(--pri); }
+.badge.stale { border-color:#f43f5e; color:#f43f5e; }
+.kv { color:var(--sub); }
+.kv b { color:var(--text); font-weight:600; }
+.linkrow { display:flex; flex-wrap:wrap; gap:6px; margin-top:4px; }
+.linkrow a { background:var(--el); border:1px solid var(--border); border-radius:8px; padding:3px 9px; font-size:calc(11.5px*var(--scale)); display:inline-flex; gap:5px; align-items:center; }
+.linkrow a:hover { border-color:var(--pri); text-decoration:none; }
+.article { border-left:3px solid var(--pri); padding-left:9px; margin-top:2px; color:var(--sub); }
+.article a { color:var(--text); font-weight:600; }
+.notes { color:var(--muted); font-size:calc(11.5px*var(--scale)); font-style:italic; }
+.empty { color:var(--muted); padding:40px; text-align:center; }
+@media (max-width:900px){ .layout{flex-direction:column} aside{width:100%;position:static;max-height:none;flex:auto} :root{--cols:1!important} }
+</style>
+</head>
+<body>
+<header>
+ <h1>SDCC Media Contacts</h1>
+ <span class="count" id="count"></span>
+ <span class="sync">
+ <span id="syncinfo">loading…</span>
+ <button id="refreshBtn" title="Pull latest edits from the Google Drive spreadsheet">⟳ Refresh from Drive</button>
+ <a href="https://drive.google.com/file/d/1uTKInwC0BfaZlSNrTR821C_7aqVBI8Pn/view" target="_blank" rel="noopener noreferrer">Open spreadsheet ↗</a>
+ </span>
+</header>
+<div class="layout">
+<aside>
+ <h3>Search</h3>
+ <input type="search" id="q" placeholder="name, outlet, beat…" style="width:100%;background:var(--el);border:1px solid var(--border);border-radius:8px;color:var(--text);padding:7px 10px;font-size:13px">
+ <h3>Show fields</h3>
+ <div id="toggles"></div>
+ <h3>Filter</h3>
+ <label class="field-toggle"><input type="checkbox" id="fEmail"> Has email</label>
+ <label class="field-toggle"><input type="checkbox" id="fContact"> Has Gmail history</label>
+ <label class="field-toggle"><input type="checkbox" id="fActive"> Active beat only</label>
+</aside>
+<main>
+ <div class="controls">
+ <select id="sort">
+ <option value="contact">Last contact (newest)</option>
+ <option value="articles">Articles tracked</option>
+ <option value="name">Name A→Z</option>
+ <option value="outlet">Outlet A→Z</option>
+ <option value="artdate">Latest article date</option>
+ </select>
+ <span class="density">Density <input type="range" id="density" min="1" max="5" step="1" value="3"></span>
+ </div>
+ <div class="grid" id="grid"></div>
+ <div class="empty" id="empty" style="display:none">No journalists match.</div>
+</main>
+</div>
+<script>
+const FIELDS = [
+ ["beat","Beat",true],["medium","Medium",true],["location","Location",true],
+ ["email","Email",true],["phone","Phone",true],["social","Social links (X / LinkedIn)",true],
+ ["article","Latest article",true],["tracked","Articles tracked by SDCC",true],
+ ["contact","Gmail contact history",true],["outletchange","Outlet change flag",true],
+ ["notes","Notes",false],
+];
+const LS = (k,v)=> v===undefined ? localStorage.getItem("sdccjv_"+k) : localStorage.setItem("sdccjv_"+k,v);
+let DATA = [], META = {};
+
+const vis = {};
+FIELDS.forEach(([k,,def])=>{ const s=LS("f_"+k); vis[k] = s===null?def:s==="1"; });
+
+function el(html){ const t=document.createElement("template"); t.innerHTML=html.trim(); return t.content.firstChild; }
+const esc = s => (s||"").replace(/[&<>"]/g, c=>({"&":"&","<":"<",">":">",'"':"""}[c]));
+
+function buildToggles(){
+ const box = document.getElementById("toggles"); box.innerHTML="";
+ FIELDS.forEach(([k,label])=>{
+ const row = el(`<label class="field-toggle"><input type="checkbox" ${vis[k]?"checked":""}> ${label}</label>`);
+ row.querySelector("input").onchange = e => { vis[k]=e.target.checked; LS("f_"+k, e.target.checked?"1":"0"); render(); };
+ box.appendChild(row);
+ });
+}
+
+function linkX(handle,name,outlet){
+ if(handle) return `https://x.com/${encodeURIComponent(handle.replace(/^@/,""))}`;
+ return `https://x.com/search?q=${encodeURIComponent(name+" "+(outlet||""))}&f=user`;
+}
+function linkLI(name,outlet){
+ return `https://www.linkedin.com/search/results/people/?keywords=${encodeURIComponent(name+" "+(outlet||""))}`;
+}
+function outletChanged(r){
+ const a=(r.outlet_on_file||"").toLowerCase(), b=(r.current_outlet_verified||"").toLowerCase();
+ return a && b && !b.includes(a.split(" ")[0]) ? true : false;
+}
+
+function card(r){
+ const L = r._links||{};
+ const parts = [];
+ parts.push(`<div class="name">${esc(r.name)}</div>`);
+ const oc = outletChanged(r);
+ parts.push(`<div class="outlet">${esc(r.current_outlet_verified||r.outlet_on_file||"—")}${vis.outletchange&&oc?` <span class="badge stale">moved from ${esc(r.outlet_on_file)}</span>`:""}</div>`);
+ const badges=[];
+ if(vis.medium&&r.medium) badges.push(`<span class="badge">${esc(r.medium)}</span>`);
+ if(vis.location&&r.location) badges.push(`<span class="badge">${esc(r.location)}</span>`);
+ if(vis.tracked&&r.articles_tracked_10yr&&+r.articles_tracked_10yr>0) badges.push(`<span class="badge">${esc(r.articles_tracked_10yr)} tracked</span>`);
+ if(vis.contact&&r.date_of_last_contact) badges.push(`<span class="badge contact">last contact ${esc(r.date_of_last_contact)}</span>`);
+ if(badges.length) parts.push(`<div>${badges.join("")}</div>`);
+ if(vis.beat&&r.beat) parts.push(`<div class="kv"><b>Beat:</b> ${esc(r.beat)}</div>`);
+
+ const links=[];
+ if(vis.email&&r.email) links.push(`<a href="mailto:${esc(r.email)}" title="${esc(r.email_source||"")}">✉️ ${esc(r.email)}</a>`);
+ if(vis.phone&&r.phone) links.push(`<a href="tel:${esc(r.phone.replace(/[^0-9+]/g,""))}">📞 ${esc(r.phone)}</a>`);
+ if(vis.social){
+ links.push(`<a href="${linkX(r.twitter,r.name,r.current_outlet_verified||r.outlet_on_file)}" target="_blank" rel="noopener noreferrer">𝕏 ${esc(r.twitter||"find")}</a>`);
+ links.push(`<a href="${linkLI(r.name,r.current_outlet_verified||r.outlet_on_file)}" target="_blank" rel="noopener noreferrer">in LinkedIn</a>`);
+ }
+ if(vis.contact){
+ if(L.initial_email) links.push(`<a href="${esc(L.initial_email)}" target="_blank" rel="noopener noreferrer">📥 first email${r.date_of_first_contact?" · "+esc(r.date_of_first_contact):""}</a>`);
+ if(L.last_email) links.push(`<a href="${esc(L.last_email)}" target="_blank" rel="noopener noreferrer">📥 last email${r.date_of_last_contact?" · "+esc(r.date_of_last_contact):""}</a>`);
+ }
+ if(links.length) parts.push(`<div class="linkrow">${links.join("")}</div>`);
+
+ if(vis.article&&(r.latest_article||L.latest_article)){
+ const t = esc(r.latest_article||"latest article");
+ parts.push(`<div class="article">${L.latest_article?`<a href="${esc(L.latest_article)}" target="_blank" rel="noopener noreferrer">${t} ↗</a>`:t}${r.article_date?`<div>${esc(r.article_date)}${r.link_type==="author_page"?" · byline page":""}</div>`:""}</div>`);
+ }
+ if(vis.notes&&r.notes) parts.push(`<div class="notes">${esc(r.notes)}</div>`);
+ return el(`<div class="card">${parts.join("")}</div>`);
+}
+
+function render(){
+ const q=(document.getElementById("q").value||"").toLowerCase();
+ const fE=document.getElementById("fEmail").checked, fC=document.getElementById("fContact").checked, fA=document.getElementById("fActive").checked;
+ let rows = DATA.filter(r=>{
+ if(q && !JSON.stringify(r).toLowerCase().includes(q)) return false;
+ if(fE && !r.email) return false;
+ if(fC && !r.date_of_last_contact) return false;
+ if(fA && /stub|deprioritiz|not a (journalist|reporter)|inactive/i.test(r.notes||"")) return false;
+ return true;
+ });
+ const s=document.getElementById("sort").value;
+ const by=(f,dir=1)=>(a,b)=>((a[f]||"")<(b[f]||"")?-1:1)*dir;
+ if(s==="name") rows.sort(by("name"));
+ if(s==="outlet") rows.sort(by("current_outlet_verified"));
+ if(s==="artdate") rows.sort(by("article_date",-1));
+ if(s==="articles") rows.sort((a,b)=>(+b.articles_tracked_10yr||0)-(+a.articles_tracked_10yr||0));
+ if(s==="contact") rows.sort((a,b)=>((b.date_of_last_contact||"")+(+b.articles_tracked_10yr||0)).localeCompare((a.date_of_last_contact||"")+(+a.articles_tracked_10yr||0)));
+ const grid=document.getElementById("grid"); grid.innerHTML="";
+ rows.forEach(r=>grid.appendChild(card(r)));
+ document.getElementById("empty").style.display = rows.length?"none":"block";
+ document.getElementById("count").textContent = `${rows.length} of ${DATA.length} journalists`;
+}
+
+async function load(){
+ const r = await fetch("/api/data"); const j = await r.json();
+ // normalize header-derived keys from the sheet
+ DATA = (j.rows||[]).map(x=>({
+ name:x.name, outlet_on_file:x.outlet_on_file||x["outlet_on_file"], current_outlet_verified:x.current_outlet_verified,
+ medium:x.medium, beat:x.beat, email:x.email, email_source:x.email_source, phone:x.phone,
+ twitter:x.twitter, location:x.location, latest_article:x.latest_article, article_date:x.article_date,
+ link_type:x.link_type, articles_tracked_10yr:x.articles_tracked_10yr, date_of_first_contact:x.first_contact||x.date_of_first_contact,
+ date_of_last_contact:x.last_contact||x.date_of_last_contact, contact_source:x.contact_source, notes:x.notes, _links:x._links||{}
+ }));
+ META=j;
+ const t = j.generated_at ? new Date(j.generated_at) : null;
+ document.getElementById("syncinfo").textContent =
+ (j.drive_pull_ok?"synced from Drive ":"⚠ Drive unreachable, cached ") + (t?t.toLocaleString(undefined,{month:"short",day:"numeric",hour:"numeric",minute:"2-digit"}):"");
+ render();
+}
+
+document.getElementById("q").oninput=render;
+["fEmail","fContact","fActive"].forEach(id=>document.getElementById(id).onchange=render);
+document.getElementById("sort").onchange=e=>{ LS("sort",e.target.value); render(); };
+document.getElementById("density").oninput=e=>{
+ const v=+e.target.value; LS("density",v);
+ document.documentElement.style.setProperty("--cols", v);
+ document.documentElement.style.setProperty("--scale", (1.25 - v*0.08).toFixed(2));
+};
+document.getElementById("refreshBtn").onclick=async()=>{
+ const b=document.getElementById("refreshBtn"); b.disabled=true; b.textContent="⟳ pulling…";
+ try{ await fetch("/api/refresh",{method:"POST"}); await load(); } finally { b.disabled=false; b.textContent="⟳ Refresh from Drive"; }
+};
+
+// restore persisted controls
+const sv=LS("sort"); if(sv) document.getElementById("sort").value=sv;
+const dv=LS("density")||3; document.getElementById("density").value=dv;
+document.getElementById("density").dispatchEvent(new Event("input"));
+buildToggles();
+load();
+setInterval(load, 60*1000); // pick up server's 5-min Drive pulls without a manual reload
+</script>
+</body>
+</html>
diff --git a/scripts/refresh.py b/scripts/refresh.py
new file mode 100644
index 0000000..3574751
--- /dev/null
+++ b/scripts/refresh.py
@@ -0,0 +1,54 @@
+#!/usr/bin/env python3
+"""Pull the SDCC media-contacts spreadsheet from Google Drive and emit data/journalists.json.
+
+The Drive file (gdrive:SDCC/sdcc-journalists-media-contacts.xlsx) is the source of
+truth — team edits in Google Sheets/Drive land here on every refresh.
+"""
+import json, subprocess, sys, os, datetime
+
+ROOT = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
+DATA = os.path.join(ROOT, "data")
+XLSX = os.path.join(DATA, "sdcc-journalists-media-contacts.xlsx")
+OUT = os.path.join(DATA, "journalists.json")
+
+# 1. pull latest from Drive (rclone only copies if changed)
+r = subprocess.run(["rclone", "copy", "gdrive:SDCC/sdcc-journalists-media-contacts.xlsx", DATA],
+ capture_output=True, text=True, timeout=120)
+drive_ok = r.returncode == 0
+if not drive_ok:
+ sys.stderr.write(f"rclone pull failed: {r.stderr[:300]}\n")
+ if not os.path.exists(XLSX):
+ sys.exit(2) # nothing cached either — hard fail
+
+# 2. parse xlsx (headers row 1; hyperlinks preserved per cell)
+from openpyxl import load_workbook
+wb = load_workbook(XLSX)
+ws = wb.active
+headers = [str(c.value or "").strip() for c in ws[1]]
+
+def key(h):
+ return h.lower().replace(" (", "_").replace(")", "").replace(" ", "_").replace("(", "").replace("__", "_").strip("_")
+
+rows = []
+for row in ws.iter_rows(min_row=2):
+ if not any(c.value for c in row):
+ continue
+ rec, links = {}, {}
+ for h, c in zip(headers, row):
+ k = key(h)
+ rec[k] = "" if c.value is None else str(c.value)
+ if c.hyperlink and c.hyperlink.target:
+ links[k] = c.hyperlink.target
+ rec["_links"] = links
+ rows.append(rec)
+
+payload = {
+ "generated_at": datetime.datetime.now().astimezone().isoformat(),
+ "drive_pull_ok": drive_ok,
+ "source": "gdrive:SDCC/sdcc-journalists-media-contacts.xlsx",
+ "count": len(rows),
+ "rows": rows,
+}
+with open(OUT, "w") as f:
+ json.dump(payload, f)
+print(f"refreshed: {len(rows)} rows, drive_pull_ok={drive_ok}")
diff --git a/server.js b/server.js
new file mode 100644
index 0000000..dfb53a0
--- /dev/null
+++ b/server.js
@@ -0,0 +1,71 @@
+#!/usr/bin/env node
+/**
+ * SDCC Journalists Viewer — live view over the Drive spreadsheet.
+ * Basic auth admin/DW2024! (unauth /healthz for the fleet keepalive).
+ * Data source of truth: gdrive:SDCC/sdcc-journalists-media-contacts.xlsx,
+ * re-pulled by scripts/refresh.py on boot, every 5 min, and via POST /api/refresh.
+ */
+const http = require('http');
+const fs = require('fs');
+const path = require('path');
+const { execFile } = require('child_process');
+
+const PORT = parseInt(process.env.PORT || '9878', 10);
+const AUTH = 'Basic ' + Buffer.from(process.env.BASIC_AUTH || 'admin:DW2024!').toString('base64');
+const DATA_JSON = path.join(__dirname, 'data', 'journalists.json');
+const REFRESH = path.join(__dirname, 'scripts', 'refresh.py');
+
+let refreshing = false;
+function refresh(cb) {
+ if (refreshing) return cb && cb(new Error('refresh already running'));
+ refreshing = true;
+ execFile('python3', [REFRESH], { timeout: 150000 }, (err, stdout, stderr) => {
+ refreshing = false;
+ if (err) console.error('[refresh]', stderr || err.message);
+ else console.log('[refresh]', stdout.trim());
+ cb && cb(err);
+ });
+}
+refresh();
+setInterval(() => refresh(), 5 * 60 * 1000);
+
+const MIME = { '.html': 'text/html', '.js': 'text/javascript', '.css': 'text/css', '.json': 'application/json', '.svg': 'image/svg+xml' };
+
+const server = http.createServer((req, res) => {
+ const url = req.url.split('?')[0];
+
+ if (url === '/healthz') { res.writeHead(200, { 'Content-Type': 'application/json' }); return res.end('{"ok":true}'); }
+
+ if (req.headers.authorization !== AUTH) {
+ res.writeHead(401, { 'WWW-Authenticate': 'Basic realm="SDCC"' });
+ return res.end('auth required');
+ }
+
+ if (url === '/api/data') {
+ try {
+ const body = fs.readFileSync(DATA_JSON);
+ res.writeHead(200, { 'Content-Type': 'application/json' });
+ return res.end(body);
+ } catch {
+ res.writeHead(503, { 'Content-Type': 'application/json' });
+ return res.end('{"error":"data not yet loaded — try /api/refresh"}');
+ }
+ }
+
+ if (url === '/api/refresh' && req.method === 'POST') {
+ return refresh((err) => {
+ res.writeHead(err ? 500 : 200, { 'Content-Type': 'application/json' });
+ res.end(JSON.stringify({ ok: !err, error: err ? err.message : undefined }));
+ });
+ }
+
+ const file = path.join(__dirname, 'public', url === '/' ? 'index.html' : url);
+ if (!file.startsWith(path.join(__dirname, 'public'))) { res.writeHead(403); return res.end(); }
+ fs.readFile(file, (err, body) => {
+ if (err) { res.writeHead(404); return res.end('not found'); }
+ res.writeHead(200, { 'Content-Type': MIME[path.extname(file)] || 'application/octet-stream' });
+ res.end(body);
+ });
+});
+
+server.listen(PORT, () => console.log(`sdcc-journalists-viewer on http://127.0.0.1:${PORT}`));
(oldest)
·
back to Sdcc Journalists Viewer
·
Google sign-in (allowlisted) + live at sdccjournalists.agent ca6312d →