If you’ve ever copy-pasted data from a website into a spreadsheet, you’ve felt the pain of manual work. APIs exist to give you structured data, but writing the code to call them feels intimidating if you’ve never done it. In this post, I’ll show you how to write a Python script that pulls data from a public API, cleans it up, and outputs a CSV file—all in about 20 minutes.
First, let’s pick a real API to work with. A simple one is the JSONPlaceholder API (https://jsonplaceholder.typicode.com/posts), which returns fake blog posts. In your terminal, create a new directory and inside it, a file called `fetch_posts.py`. Open it in your editor.
Now, the core code. We’ll use the `requests` library to make the HTTP call, which is a standard Python package. If you don’t have it, install it with `pip install requests`. The script looks like this:
“`python
import requests
import csv
url = “https://jsonplaceholder.typicode.com/posts”
response = requests.get(url)
data = response.json() # Parse the JSON response
# Check if the request worked
if response.status_code == 200:
print(f”Fetched {len(data)} posts”)
else:
print(“Failed to fetch data”)
exit(1)
# Write to CSV
with open(“posts.csv”, “w”, newline=””, encoding=”utf-8″) as f:
writer = csv.writer(f)
writer.writerow([“id”, “title”, “body”])
for post in data:
writer.writerow([post[“id”], post[“title”], post[“body”]])
“`
Run the script with `python fetch_posts.py`. You’ll see `Fetched 100 posts` and a new file `posts.csv` appears in your directory. Open it and you’ve got clean, tabular data. That’s your first API automation.
But let’s make it more useful. Real-world APIs often require authentication, pagination, and error handling. Let’s add a simple retry mechanism. If the API fails, we’ll wait and try again. We’ll wrap the request in a loop:
“`python
import time
for attempt in range(3):
response = requests.get(url)
if response.status_code == 200:
break
else:
print(f”Attempt {attempt+1} failed, retrying…”)
time.sleep(2)
else:
print(“Could not fetch data after 3 attempts”)
exit(1)
“`
This handles transient errors gracefully. Also, if you need to handle pagination (many APIs return results in pages), you can loop through `?page=1`, `?page=2`, etc., and concatenate the results.
Once you’ve got the raw data, the real value is in cleaning and transforming it. For example, you might want to filter posts that contain a keyword, or strip HTML tags from the body. Python’s string methods and list comprehensions make this quick. Add these lines before writing to CSV:
“`python
filtered = [post for post in data if “qui” in post[“title”]]
“`
Now you have a script that does something concrete. You can schedule it with cron (on a Mac/Linux) or Task Scheduler (on Windows) to run daily. That’s the beauty of automation: you write it once, and it works in the background.
This pattern—call an API, parse JSON, save to CSV—applies to dozens of use cases: pulling analytics data, syncing CRM records, fetching weather forecasts, even scraping job listings. The key is to start with a small, concrete task. Once you’ve done one, you’ll see opportunities everywhere.
If you’d rather not write this from scratch, our [API Automation Starter Pack](https://kintly.space/scripts/api-automation-starter) includes a robust version with error handling, pagination, and configurable parameters. But honestly, the 20-minute version above gets you 80% of the value. Go build something.