How to Automate Daily Meta Ads Reporting to Google Sheets

When you automate meta ads reporting to Google Sheets, you stop wasting 30 minutes every morning copying numbers from Ads Manager. I run this for every ad account I manage. The data lands in a shared sheet before anyone opens their laptop.

The setup takes about an hour. After that, it runs itself.

What You Need Before Starting

• A Meta Business account with a System User token
• A Google Cloud project with Sheets API enabled
• A service account JSON key for Google Sheets
• Python 3.8+ with requests, gspread, and google-auth installed

Step 1: Get Your Meta Access Token

Go to Meta Business Settings. Create a System User with ads_read permission. Generate a token. Store it in your .env file:

META_ACCESS_TOKEN=your_token_here
META_AD_ACCOUNT_ID=act_123456789

Step 2: Pull Yesterday's Data from Meta API

import requests
import os
from datetime import datetime, timedelta
from dotenv import load_dotenv

load_dotenv()

def fetch_meta_ads_data(date=None):
    if not date:
        date = (datetime.now() - timedelta(days=1)).strftime("%Y-%m-%d")
    
    token = os.getenv("META_ACCESS_TOKEN")
    account_id = os.getenv("META_AD_ACCOUNT_ID")
    
    url = f"https://graph.facebook.com/v19.0/{account_id}/insights"
    params = {
        "access_token": token,
        "fields": "campaign_name,adset_name,ad_name,spend,impressions,clicks,cpc,cpm,ctr,actions",
        "level": "ad",
        "time_range": f'{{"since":"{date}","until":"{date}"}}',
        "limit": 500,
    }
    
    resp = requests.get(url, params=params)
    data = resp.json().get("data", [])
    
    rows = []
    for ad in data:
        leads = 0
        purchases = 0
        actions = ad.get("actions", [])
        for action in actions:
            if action["action_type"] == "lead":
                leads = int(action["value"])
            if action["action_type"] == "purchase":
                purchases = int(action["value"])
        
        rows.append({
            "date": date,
            "campaign": ad.get("campaign_name", ""),
            "adset": ad.get("adset_name", ""),
            "ad": ad.get("ad_name", ""),
            "spend": float(ad.get("spend", 0)),
            "impressions": int(ad.get("impressions", 0)),
            "clicks": int(ad.get("clicks", 0)),
            "cpc": float(ad.get("cpc", 0)),
            "ctr": float(ad.get("ctr", 0)),
            "leads": leads,
            "purchases": purchases,
        })
    return rows

Step 3: Connect to Google Sheets

import gspread
from google.oauth2.service_account import Credentials

def get_sheet(sheet_id, worksheet_name="Daily Data"):
    creds = Credentials.from_service_account_file(
        "service-account.json",
        scopes=["https://www.googleapis.com/auth/spreadsheets"]
    )
    gc = gspread.authorize(creds)
    sh = gc.open_by_key(sheet_id)
    
    try:
        ws = sh.worksheet(worksheet_name)
    except gspread.WorksheetNotFound:
        ws = sh.add_worksheet(title=worksheet_name, rows=1000, cols=15)
        ws.append_row(["Date", "Campaign", "Ad Set", "Ad", "Spend", "Impressions", "Clicks", "CPC", "CTR", "Leads", "Purchases"])
    
    return ws

Step 4: Write Data to the Sheet

def push_to_sheet(sheet_id, rows):
    ws = get_sheet(sheet_id)
    
    sheet_rows = []
    for row in rows:
        sheet_rows.append([
            row["date"], row["campaign"], row["adset"], row["ad"],
            row["spend"], row["impressions"], row["clicks"],
            round(row["cpc"], 2), round(row["ctr"], 2),
            row["leads"], row["purchases"]
        ])
    
    if sheet_rows:
        ws.append_rows(sheet_rows)
    
    return len(sheet_rows)

Step 5: Schedule It Daily

Create a runner script and set it on cron:

if __name__ == "__main__":
    sheet_id = os.getenv("GOOGLE_SHEET_ID")
    rows = fetch_meta_ads_data()
    count = push_to_sheet(sheet_id, rows)
    print(f"Pushed {count} rows to Google Sheets")

# Run daily at 7 AM
0 7 * * * cd /path/to/project && python3 meta_to_sheets.py >> /var/log/meta_sheets.log 2>&1

The sheet fills up every day. Add a summary tab with pivot formulas to see weekly and monthly roll-ups without any extra code.

What to Build Next

Add conditional formatting to highlight ads that overspend or underperform. Then build a Slack notification that sends the top 3 and bottom 3 performers each morning.

Related Reading

• AI in Paid Advertising: The Complete Overview - how AI operations power ad accounts at scale
• AI-Powered Reporting That Actually Gets Read - building reports people actually open
• The Feedback Loop That Powers Everything - why feedback loops matter in ad optimization