From ZIP Chaos to Automated Excellence: A Data Cleaning Adventure

EN

DE

NL

---

How a Simple Video Led to a Complete Job Application Automation System

It all started innocently enough—watching Professor Jules White from Vanderbilt University demonstrate data cleansing in ZIP files using ChatGPT. “Cool hack,” I thought. “I should try this sometime.”

That was my first mistake. Or perhaps my greatest discovery.

The Problem: German Bureaucracy Meets Digital Reality

The German employment agency (Arbeitsagentur) wanted proof of my job applications. Not screenshots—oh no, that would be too simple. They wanted a structured Excel file with links to every single application. Because apparently, in 2025, we still can’t just show organized folders as evidence.

I had around 100 job applications scattered across Google Drive in various folder structures:

• Mixed file formats (DOCX, PDF, URL shortcuts)
• Inconsistent naming conventions
• Nested folder hierarchies
• A general sense of chaos that accumulates during any job search marathon

The Journey: From Zapier Frustration to Google Apps Script Victory

Phase 1: The Zapier Experiment

Initially, I thought Zapier would be the perfect solution. Wrong. After hours of fighting with:

• Complex filter chains
• Missing file content extraction
• OAuth nightmares across multiple Google services
• Upload size limitations that required splitting files into “mini-splits”

I realized Zapier was making a simple task unnecessarily complex. As I put it: “Why make it difficult when you can make it simple for users?”

Phase 2: The n8n Detour

Briefly considered n8n as a more flexible alternative, but by then I was suffering from severe tool fatigue. Three days of testing different platforms was enough.

Phase 3: The Google Apps Script Solution

Finally settled on what should have been obvious from the start: Google Apps Script. Why fight external tools when Google’s own automation can:

• Read folder structures directly
• Extract real URLs from Windows .url files
• Generate clean, clickable links
• Create professional-looking spreadsheets

The Technical Implementation

The final solution was elegantly simple:

function listApplications() {
  // Scan designated Google Drive folder
  // Extract job links and cover letters
  // Generate clean hyperlinks with readable names
  // Populate spreadsheet automatically
}

Key features:

• Automatic file type detection: Distinguishes between job ads (.url files) and cover letters (PDFs)
• Clean link extraction: Converts messy Google Drive URLs to direct download links
• Smart naming: Displays readable filenames instead of cryptic URLs
• One-click updates: Refresh the entire list whenever new applications are added

The Human Element: Working with AI

Throughout this process, my AI assistant (ChatGPT) proved invaluable—not just for technical solutions, but for maintaining sanity during bureaucratic frustrations. We developed a kind of digital partnership:

• Pragmatic problem-solving: Breaking complex tasks into manageable steps
• Humor as coping mechanism: Joking about “Excel fetish clubs” and German bureaucracy
• Iterative refinement: Constantly improving the solution based on real-world testing

The AI even earned a proper credit in my final documentation—a demonstration of modern collaborative work methods.

The Visual Transformation: Before and After

The difference between the initial automated output and the refined version is striking:

Before: Raw URL Chaos

The first iteration produced functional but ugly results—long, cryptic Google Drive URLs that were technically correct but visually overwhelming:

• https://drive.google.com/uc?export=view&id=1YRLcSHKBzVOYbVwdXyeNl9ABlpRcKTcE
• https://drive.google.com/uc?export=view&id=1eMSSDbZRwSd3s2MwGk-GN18fZUoTC

These links worked perfectly for the employment agency’s requirements, but the spreadsheet looked like a developer’s debug output rather than a professional document.

After: Clean, Professional Presentation

The enhanced version transforms the same data into an elegant, readable format:

• Clean company names in the first column
• Descriptive link text like “Job-ad.url” and “RvL-Coverletter-Codecentric.docx”
• Clickable hyperlinks that hide the technical complexity
• Professional appearance suitable for any business context

The technical functionality remained identical—all links still point to the same absolute URLs for maximum compatibility—but the user experience improved dramatically.

The Result: Bureaucracy-Proof Automation

What emerged was more than just a spreadsheet—it was a complete job application management system:

For the Employment Agency:

• Clean, professional Excel file with clickable links
• Structured proof of all application activities
• No more requests for screenshots or manual documentation

For Future Applications:

• Simply drop new applications in the designated folder
• Run the script once weekly (or as needed)
• Automatic generation of updated documentation

For Sanity:

• No more manual link copying
• Consistent file organization
• Professional appearance that exceeds bureaucratic expectations

Lessons Learned

1. Start Simple: Sometimes the obvious solution (Google’s own tools) is the best one
2. Tool Fatigue is Real: Testing too many platforms can be counterproductive
3. Document Everything: Good documentation prevents future headaches
4. Automate Thoughtfully: The goal isn’t just automation—it’s solving real problems elegantly

The Meta-Lesson: Skills-Based Hiring

The irony wasn’t lost on me: while demonstrating advanced automation skills, data cleaning expertise, and modern workflow optimization, I was jumping through hoops because German HR still operates on “BWL degree required for cleaning positions” logic.

This project showcased exactly the kind of practical, results-oriented thinking that modern workplaces need—yet it was created to satisfy a bureaucratic process that hasn’t evolved with the times.

Looking Forward

This automation system will serve me well beyond this specific bureaucratic requirement. It’s a perfect example of how AI collaboration can transform tedious administrative tasks into streamlined, professional processes.

More importantly, it demonstrates that in 2025, the most valuable skill isn’t coding—it’s knowing how to effectively collaborate with AI to solve real-world problems quickly and elegantly.

---

This project was completed in collaboration with ChatGPT (GPT-4), demonstrating modern AI-assisted workflow optimization. The final system processes 60+ job applications with a single click, generating professional documentation that exceeds traditional bureaucratic requirements.

Technical Notes

• Platform: Google Apps Script + Google Drive + Google Sheets
• Processing: Automatic file type detection and URL extraction
• Output: Professional Excel file with embedded hyperlinks
• Maintenance: One-click updates for new applications
• Documentation: Integrated project description for transparency

---

Feedback from Claude AI when I showed the differences between for and after the optimization 🙂 :

Perfect! This is a fantastic before-and-after comparison that really showcases the improvement. Let me update the blog post to include this visual transformation: