Demo project showing a simple Python data-processing workflow. It loads mock JSON data, validates the structure, transforms the fields and exports the results into a clean CSV file. Created as a portfolio example for API-based automation work.

README.md

API to Sheets Demo

This script demonstrates a mock API to Google Sheets integration workflow. It loads JSON data from a file, processes it by lowercasing keys and adding a processed timestamp, and exports the results to a CSV file.

What it does

The script reads mock JSON data (simulating an API response), validates the data structure, transforms it, and outputs it to CSV format. This demonstrates a common workflow for integrating external APIs with spreadsheet applications.

Features

• Error Handling: Gracefully handles missing files, permission errors, and malformed JSON
• Data Validation: Validates JSON structure, ensures required fields (id, name, score) exist, and skips invalid records
• Logging: Provides clear feedback on processing status, record counts, and validation issues
• Production-Ready: Uses proper encoding, context managers, and error recovery patterns

How to run

python api_to_sheets.py

Output

The script generates output.csv in the root folder containing the processed data with lowercase keys and a processed timestamp for each record. The script will display:

• Number of records loaded
• Any validation warnings for skipped records
• Confirmation when CSV is written successfully

api-sheets-demo.py

"""
API to Sheets Demo - Mock JSON to CSV conversion script.

This module provides functionality to load JSON data, validate and process it,
and export the results to CSV format. Designed as a demonstration of API
integration patterns with production-ready error handling and validation.
"""

import json
import csv
from datetime import datetime


class SheetSync:
    """
    Handles loading, processing, and exporting data from JSON to CSV format.
    
    Provides data validation, error handling, and transformation capabilities
    for converting API-like JSON responses into structured CSV output.
    """
    
    REQUIRED_KEYS = ['id', 'name', 'score']
    
    def load_data(self, json_path):
        """
        Load and process JSON data from a file.
        
        Args:
            json_path (str): Path to the JSON file to load.
            
        Returns:
            list: Processed list of dictionaries with lowercase keys and
                  processed timestamps. Returns empty list on error.
        
        Raises:
            FileNotFoundError: If the JSON file doesn't exist.
            PermissionError: If file cannot be read due to permissions.
            json.JSONDecodeError: If JSON is malformed.
        """
        try:
            with open(json_path, 'r', encoding='utf-8') as f:
                data = json.load(f)
        except FileNotFoundError:
            print(f"Error: File '{json_path}' not found.")
            return []
        except PermissionError:
            print(f"Error: Permission denied reading '{json_path}'.")
            return []
        except json.JSONDecodeError as e:
            print(f"Error: Invalid JSON in '{json_path}': {e}")
            return []
        
        if not isinstance(data, list):
            print(f"Error: Expected JSON array, got {type(data).__name__}.")
            return []
        
        processed = []
        skipped = 0
        
        for item in data:
            if not isinstance(item, dict):
                print(f"Warning: Skipping non-dict item: {item}")
                skipped += 1
                continue
            
            required_keys_lower = [k.lower() for k in self.REQUIRED_KEYS]
            item_keys_lower = [k.lower() for k in item.keys()]
            
            if not all(key in item_keys_lower for key in required_keys_lower):
                print(f"Warning: Skipping item missing required keys: {item}")
                skipped += 1
                continue
            
            processed_item = {k.lower(): v for k, v in item.items()}
            processed_item['processed_timestamp'] = datetime.now().isoformat()
            processed.append(processed_item)
        
        if skipped > 0:
            print(f"Skipped {skipped} invalid record(s).")
        
        print(f"Loaded {len(processed)} record(s).")
        return processed
    
    def write_csv(self, data, output_path):
        """
        Write processed data to a CSV file.
        
        Args:
            data (list): List of dictionaries to write to CSV.
            output_path (str): Path where the CSV file will be written.
        
        Returns:
            bool: True if successful, False otherwise.
        
        Raises:
            PermissionError: If file cannot be written due to permissions.
        """
        if not data:
            print("Warning: No data to write. CSV file not created.")
            return False
        
        try:
            fieldnames = list(data[0].keys())
            
            with open(output_path, 'w', newline='', encoding='utf-8') as f:
                writer = csv.DictWriter(f, fieldnames=fieldnames)
                writer.writeheader()
                writer.writerows(data)
            
            print(f"Wrote CSV to {output_path}")
            return True
        except PermissionError:
            print(f"Error: Permission denied writing to '{output_path}'.")
            return False
        except Exception as e:
            print(f"Error writing CSV: {e}")
            return False


if __name__ == '__main__':
    sync = SheetSync()
    data = sync.load_data('data.json')
    sync.write_csv(data, 'output.csv')

data.json

[
  {"id": 1, "name": "Alice", "score": 42},
  {"id": 2, "name": "Bob", "score": 58},
  {"id": 3, "name": "Charlie", "score": 87},
  {"id": 4, "name": "Dana", "score": 77},
  {"id": 5, "name": "Eve", "score": 90}
]

output.csv