A semi-automated system that transforms how I manage, analyse and plan social media content. Built in Google Sheets with Apps Script, it replaces manual reporting with intelligent automation for LinkedIn and Instagram - pulling data, generating insights, and sparking new content ideas.
🧠 Project Goal
This capstone project brings automation, structure and intelligence to the way I manage, analyse and plan social media content. Previously done manually, this system now provides a semi-automated solution for collecting data, analysing performance, and generating new content ideas across LinkedIn and Instagram. It reduces friction in my workflow and enables faster insights, clearer reports, and more strategic content decisions.
🎯 Problem It Solves
Before this project, reporting was entirely manual:
- Each post’s data had to be collected manually from the apps.
- Monthly reports were assembled post by post, with no structure to pull historical captions.
- Content planning was done in basic spreadsheet cells with no dynamic overview and no connection to performance
The process was time-consuming, error-prone and difficult to scale or learn from over time.
🔧 Solution Overview
This project uses Google Sheets + Apps Script to automate and streamline:
- Monthly reporting for LinkedIn and Instagram
- Post-level performance tracking
- Content idea generation (from newsletters, news headlines and prompts)
- Content planning calendar with status tracking
- Dynamic dashboards and content performance ranking
All scripts and formulas are integrated in a way that feeds multiple views, from raw data to dashboards, without redundant manual work.
💻 Tech Stack
- Google Sheets (structured for automation)
- Google Apps Script (JavaScript-based scripting for automation and content generation)
- Gmail API (used in Apps Script to pull newsletter data)
- NewsAPI.org (for headline-based content inspiration)
- Basic spreadsheet formulas & conditional formatting
- No-code/low-code AI integration (for ideation and planning)
🔍 Key Features
- LinkedIn Monthly Overview: Auto-populates high-level data, feeding a long-established LinkedIn Performance tab and LinkedIn Dashboard.
- LinkedIn Post Weekly: Post-level data feeds into the main views, tracking trends and engagement.
- Instagram Reporting: Structured the same way as LinkedIn - monthly and post-level, mostly automated.
- 🗓 Weekly Ideas Generator:
Pulls content ideas from two sources:
- News headlines via NewsAPI
- Relevant newsletters via Gmail search
Categorises into:
- News-based
- Newsletter
- Wild card prompts
Assigns status, pillar and platform
*** 📅 Content Planning Sheet:**
- Dropdowns for status, pillar, platform
- Formula-based highlight for top 20% performers
- Pulls from the calendar for a granular overview
- 📁 Idea Generator (Separate Doc):
- Dropdowns for pillar and platform
- Suggests hooks, captions, content types and hashtags
- Library built from months of idea curation
📈 Outcome
The current setup drastically reduces manual work and improves consistency in tracking and planning. It enables:
- Weekly and monthly insights with minimal effort
- Data-driven decisions for content creation
- An expandable system that I can adapt to new platforms
I also learned:
- How to write and debug Google Apps Scripts
- How to structure Sheets for multi-tab dependencies
- How AI can enhance ideation without replacing strategic judgement
✅ Testing and Ongoing Use
This system has been actively used and tested in my own workflow over the past months. I have iteratively improved it based on real needs and feedback from using it daily. It continues to evolve - I consider it a living tool rather than a finished product. As my content needs and platforms change, I plan to keep upgrading it.
