API Documentation
Access podcast data via TSV endpoints for Google Sheets integration
📊 Google Sheets Integration
All endpoints return data in TSV (Tab-Separated Values) format, designed to work seamlessly with Google Sheets IMPORTDATA() function.
iTunes IDs: These endpoints use Apple iTunes podcast IDs (not internal database IDs). Find iTunes IDs using the All Channels endpoint or from Apple Podcasts URLs (e.g., id1288641267).
1. Channel Episodes
Get all episodes for a specific channel using iTunes podcast ID, sorted by publish date (newest first).
ENDPOINT
GET /api/channels/itunes/{itunes_id}/episodes.tsv
Path Parameters:
itunes_id: Apple iTunes podcast ID (e.g.,1288641267)
Columns Returned:
episode_id,
episode_title,
pub_date,
duration_seconds,
status,
channel_id,
channel_title,
itunes_id
Example URL:
https://apple.openrouted.com/api/channels/itunes/1288641267/episodes.tsv
📗 Google Sheets Formula:
=IMPORTDATA("https://apple.openrouted.com/api/channels/itunes/1288641267/episodes.tsv")
💡 Replace 1288641267 with your iTunes ID, or reference a cell like A1:
=IMPORTDATA("https://apple.openrouted.com/api/channels/itunes/"&A1&"/episodes.tsv")
2. Recent Episodes (Last 30 Days)
Get episodes published in the last 30 days, optionally filtered by iTunes podcast IDs.
ENDPOINT
GET /api/episodes/recent.tsv?itunes_ids={ids}
Query Parameters:
itunes_ids(optional): Comma-separated iTunes podcast IDs (e.g.,1288641267,987654321)- If omitted, returns episodes from ALL channels
Columns Returned:
episode_id,
episode_title,
pub_date,
duration_seconds,
status,
channel_id,
channel_title,
itunes_id
Example URLs:
All channels:
https://apple.openrouted.com/api/episodes/recent.tsv
Specific channels (iTunes IDs):
https://apple.openrouted.com/api/episodes/recent.tsv?itunes_ids=1288641267,987654321
📗 Google Sheets Formulas:
All channels:
=IMPORTDATA("https://apple.openrouted.com/api/episodes/recent.tsv")
Specific channels from cell range (e.g., A1:A3 contains iTunes IDs):
=IMPORTDATA("https://apple.openrouted.com/api/episodes/recent.tsv?itunes_ids="&JOIN(",",A1:A3))
3. All Channels
Get a list of all channels with their metadata and statistics. Use this endpoint to find iTunes IDs for other API calls.
ENDPOINT
GET /api/channels.tsv
Columns Returned:
channel_id,
channel_title,
itunes_id,
status,
episode_count,
transcribed_count,
last_check_at
Example URL:
https://apple.openrouted.com/api/channels.tsv
📗 Google Sheets Formula:
=IMPORTDATA("https://apple.openrouted.com/api/channels.tsv")
💡 Use this to get channel IDs for other API calls!
📝 Important Notes
- TSV Format: Data is tab-separated with headers in the first row
- Transcripts Not Included: These endpoints return episode metadata only, not full transcripts
- For Transcripts: See individual episode pages for transcript search API
- Google Sheets Limits: IMPORTDATA() has a 50 concurrent import limit per spreadsheet
- Caching: Google Sheets caches imported data for ~1 hour
- Status Values:
backfill,new,queued_transcribe,transcribed,error