Automated content publishing system that syncs a Google Sheets content tracking spreadsheet with Micro.blog.
This system automatically posts content (podcasts, videos, blog posts, presentations, and guest appearances) from a Google Sheets spreadsheet to the appropriate categories on whitneylee.com (powered by Micro.blog).
Key Features:
- ✅ Full CRUD operations (create, update, delete posts)
- ✅ Automatic URL regeneration for SEO-friendly slugs
- ✅ Weekday content sync via GitHub Actions (16:15 UTC = 11:15 am CDT / 10:15 am CST, Mon-Fri)
- ✅ Daily page visibility management (6-month inactivity threshold)
- ✅ Rate-limited publishing (max 1 post/day across Micro.blog + Bluesky)
- ✅ Link-priority publishing (content with links posts before content without links)
- ✅ Dual-post strategy for backdated content (prevents broken social links)
- ✅ Smart error handling with retry logic
- ✅ Spreadsheet as single source of truth
- ✅ 96% API efficiency improvement (120→5 calls/day for visibility)
- Add/edit content in Google Sheets (Name, Type, Show, Date, Link)
- System syncs weekdays at 16:15 UTC (11:15 am CDT / 10:15 am CST, Mon-Fri) via GitHub Actions
- Link-priority selection: Content with links (Column G) posts before content without links (oldest first within each group)
- Rate limiting: max 1 post per day across all platforms
- Before posting, checks Micro.blog and Bluesky for posts published today
- Posts automatically appear on whitneylee.com in the correct category
- Column H tracks Micro.blog URLs for update/delete operations
Past-dated posts (Column D < today) create TWO posts:
- Archive: Backdated + categorized (URL tracked in Column H)
- Social: Today's date + uncategorized (triggers social media cross-posting)
Current/future dates create ONE post normally. This dual-post strategy prevents social media links from breaking when backdating changes URLs.
- System runs alongside content sync
- Checks category activity and calculates inactivity
- Categories inactive for 6+ months are automatically hidden from navigation
- Categories become visible again when new content is added
- Keeps site navigation clean without manual intervention
- Podcast →
/podcast/ - Video →
/video/ - Blog →
/blog/ - Presentations →
/presentations/ - Guest →
/guest/
- Node.js 22 (Active LTS)
- Google Cloud service account with Sheets API access
- Micro.blog app token (Micropub API)
- Micro.blog XML-RPC token (for page management)
- vals for local secret management (
brew install helmfile/tap/vals)
- Install dependencies:
npm install-
Configure secrets in Google Secret Manager:
content_manager_service_account- Google service account JSONmicroblog-content-manager- Micro.blog app tokenmicroblog-xmlrpc-token- Micro.blog XML-RPC token
-
Run sync locally:
npm run syncAutomated workflow runs via GitHub Actions:
Daily Sync (.github/workflows/daily-sync.yml):
- Runs weekdays at 16:15 UTC (11:15 am CDT / 10:15 am CST, Mon-Fri)
- Syncs spreadsheet content to Micro.blog posts with rate limiting
- Manages category page visibility based on activity
Required secrets:
GOOGLE_SERVICE_ACCOUNT_JSON- Google service account JSONMICROBLOG_APP_TOKEN- Micro.blog Micropub API tokenMICROBLOG_XMLRPC_TOKEN- Micro.blog XML-RPC tokenMICROBLOG_USERNAME- Micro.blog usernameBLUESKY_HANDLE- Bluesky handle (for daily publish guard)BLUESKY_PASSWORD- Bluesky password (for daily publish guard)
Google Sheets ←→ GitHub Actions ←→ Micro.blog
↓
Column H (URL tracking)
Content Sync Flow:
- Read spreadsheet data (Columns A-H)
- Create posts for rows with empty Column H
- Regenerate posts with non-title URLs (timestamps/hashes)
- Update posts with content changes
- Delete orphaned posts not in spreadsheet
Page Visibility Flow:
- Read spreadsheet data to calculate category activity
- Identify categories inactive for 6+ months
- Update page visibility via XML-RPC API (5 pages)
- Hide inactive categories, show active ones
Show Name: [Title](URL)Example:
Software Defined Interviews: [Learning to learn, with Sasha Czarkowski](https://www.softwaredefinedinterviews.com/91)Edit these columns - changes auto-update on next sync:
- Column A: Name (title)
- Column B: Type (category)
- Column C: Show
- Column D: Date
- Column E: Location
- Column F: Confirmed (keynote)
- Column G: Link (affects publishing priority - see below)
Column G (Link) affects publishing order: Content with links posts first. Add links when videos/recordings become available to prioritize that content.
When you change Date, Name, Show, or Keynote: The old post is deleted and a new one is created with a new URL (Column H updates automatically).
Posts with timestamp URLs (e.g., /130000.html) or hex hashes (e.g., /8e237a.html) are automatically regenerated with content-based slugs during daily sync.
content-manager/
├── src/
│ ├── sync-content.js # Daily content sync with rate limiting
│ ├── update-page-visibility.js # Page visibility management
│ └── config/
│ └── category-pages.js # Page configuration
├── docs/
│ └── microblog-api-capabilities.md # API research
├── .github/workflows/
│ └── daily-sync.yml # Daily sync & visibility automation (combined)
├── prds/ # Project requirement documents
│ └── 8-rate-limited-publishing.md # Rate limiting specification
└── .vals.yaml # Local secret configuration
Past-dated posts create both an archive post (correct historical date, categorized) and a social post (today's date, uncategorized). This ensures social media syndication works while maintaining accurate blog chronology.
Use the URL from Column H (the archive post). The social post URL triggers cross-posting but isn't tracked.
Posts with today's date or future dates create ONE post normally. Dual-posting only applies to past dates.
ISC