#!/usr/bin/env python3 """ Create and populate all service tabs for NC and LL Google Sheets. """ import json, requests from pathlib import Path # ── Auth ────────────────────────────────────────────────────────────────────── TOKEN_FILE = Path.home() / ".config/google-workspace-mcp/profiles/emerson-north/tokens.json" CREDS_FILE = Path.home() / ".config/google-workspace-mcp/profiles/emerson-north/credentials.json" def get_access_token(): tokens = json.loads(TOKEN_FILE.read_text()) import time if tokens.get("expiry_date", 0) / 1000 > time.time() + 60: return tokens["access_token"] # Refresh creds = json.loads(CREDS_FILE.read_text()) web = creds.get("web") or creds.get("installed", {}) r = requests.post("https://oauth2.googleapis.com/token", data={ "client_id": web["client_id"], "client_secret": web["client_secret"], "refresh_token": tokens["refresh_token"], "grant_type": "refresh_token", }) r.raise_for_status() new = r.json() tokens["access_token"] = new["access_token"] import time as t2 tokens["expiry_date"] = int((t2.time() + new["expires_in"]) * 1000) TOKEN_FILE.write_text(json.dumps(tokens, indent=2)) print(" Token refreshed.") return tokens["access_token"] def sheets_request(token, method, path, body=None): base = "https://sheets.googleapis.com/v4/spreadsheets" headers = {"Authorization": f"Bearer {token}", "Content-Type": "application/json"} url = base + path if method == "POST": r = requests.post(url, headers=headers, json=body) elif method == "PUT": r = requests.put(url, headers=headers, json=body) else: r = requests.get(url, headers=headers) if not r.ok: print(f" ERROR {r.status_code}: {r.text[:200]}") return r def add_sheets(token, sheet_id, tab_names): """Add multiple tabs to a spreadsheet via batchUpdate.""" requests_list = [{"addSheet": {"properties": {"title": name}}} for name in tab_names] r = sheets_request(token, "POST", f"/{sheet_id}:batchUpdate", {"requests": requests_list}) if r.ok: print(f" Created tabs: {', '.join(tab_names)}") return r.ok def write_values(token, sheet_id, tab, data): """Write 2D array to a tab starting at A1.""" rows = len(data) cols = max(len(row) for row in data) end_col = chr(ord('A') + cols - 1) range_str = f"{tab}!A1:{end_col}{rows}" body = {"values": data, "majorDimension": "ROWS"} r = sheets_request(token, "PUT", f"/{sheet_id}/values/{requests.utils.quote(range_str)}?valueInputOption=RAW", body) if r.ok: print(f" Wrote {rows} rows to {tab}") return r.ok def get_existing_tabs(token, sheet_id): r = sheets_request(token, "GET", f"/{sheet_id}") if not r.ok: return [] data = r.json() return [s["properties"]["title"] for s in data.get("sheets", [])] # ── Sheet data ───────────────────────────────────────────────────────────────── NC_SHEET = "13CljMtzYauS9p5A4cK2YW30098ISFcxPDdCWsWK6etM" LL_SHEET = "1Oh2oA4yuRs1YDIs3sIW_zjd9ls-N3LPpGkU8hdVqDoM" NC_DASHBOARD = [ ["Key", "Value"], ["stat_followers", "178.3K"], ["stat_posts", "24"], ["stat_engagement", "7.2%"], ["stat_revenue", "$4.5K"], ["tt_followers", "152K"], ["tt_growth", "+1K"], ["ig_followers", "26.3K"], ["ig_growth", "+300"], ["yt_followers", "—"], ["yt_growth", "—"], ["this_week_title", "6 scripts written and ready — film when you're free."], ["this_week_meta", "ADHD in the ER · Hookah Myths · Chronic Stress · Sleep Hygiene · ADHD & Black Men · TikTok Live"], ["activity_1_dot", "red"], ["activity_1_title", "ADHD in the ER — What I See Every Shift"], ["activity_1_meta", "Apr 7 · Script · Ready to Film"], ["activity_2_dot", "red"], ["activity_2_title", "Hookah Myths Debunked"], ["activity_2_meta", "Apr 6 · Script · Ready to Film"], ["activity_3_dot", "red"], ["activity_3_title", "Chronic Stress — What the ER Taught Me"], ["activity_3_meta", "Apr 5 · Script · Ready to Film"], ["activity_4_dot", "green"], ["activity_4_title", "April Content Calendar"], ["activity_4_meta", "Apr 4 · Calendar · Live"], ["pipeline_1_label", "April Content Calendar"], ["pipeline_1_status", "On Track"], ["pipeline_2_label", "TikTok Scripts (×6)"], ["pipeline_2_status", "In Progress"], ["pipeline_3_label", "Newsletter Launch"], ["pipeline_3_status", "Draft"], ["pipeline_4_label", "Partnership Outreach"], ["pipeline_4_status", "Prospecting"], ] LL_DASHBOARD = [ ["Key", "Value"], ["stat_followers", "153"], ["stat_posts", "29"], ["stat_engagement", "5.8%"], ["stat_revenue", "—"], ["tt_followers", "79"], ["tt_growth", "+5"], ["ig_followers", "74"], ["ig_growth", "+4"], ["yt_followers", "—"], ["yt_growth", "—"], ["this_week_title", "Newsletter sends Thursday. Blog post live this week."], ["this_week_meta", "Everything on schedule — no action needed."], ["activity_1_dot", "red"], ["activity_1_title", "April Campaign Scripts (×3)"], ["activity_1_meta", "Apr 4 · Delivered"], ["activity_2_dot", "red"], ["activity_2_title", "Instagram Carousel — Services"], ["activity_2_meta", "Apr 2 · Delivered"], ["activity_3_dot", "green"], ["activity_3_title", "SEO Page — Sandy Springs"], ["activity_3_meta", "Mar 28 · Live"], ["activity_4_dot", "red"], ["activity_4_title", "April Report Deck"], ["activity_4_meta", "Apr 1 · Delivered"], ["pipeline_1_label", "Content Calendar — Week 2"], ["pipeline_1_status", "On Track"], ["pipeline_2_label", "Newsletter — April Sequence"], ["pipeline_2_status", "In Progress"], ["pipeline_3_label", "Blog Post — Sandy Springs"], ["pipeline_3_status", "Draft"], ["pipeline_4_label", "Campaign — Spring Launch"], ["pipeline_4_status", "Prospecting"], ] SCRIPTS_HEADER = [["Title", "Platform", "Hook", "Date", "Status", "Type", "Notes"]] SCHEDULE_HEADER = [["Date", "Platform", "Caption", "Status", "Link"]] NEWSLETTER_HEADER = [["Subject", "Send Date", "Status", "Audience", "Open Rate", "Click Rate", "Notes"]] PRODUCTS_HEADER = [["Name", "Category", "Price", "Status", "Link", "Description", "Notes"]] PARTNERSHIPS_HEADER = [["Brand", "Contact", "Status", "Deal Type", "Value", "Deadline", "Notes"]] CONTRACTS_HEADER = [["Brand", "Title", "Start Date", "End Date", "Value", "Status", "Notes"]] REVENUE_HEADER = [["Source", "Amount", "Month"]] RESOURCES_HEADER = [["Title", "Type", "URL", "Notes"]] BLOG_HEADER = [["Title", "Slug", "Status", "Publish Date", "Keywords", "Notes"]] CAMPAIGNS_HEADER = [["Name", "Type", "Status", "Start Date", "End Date", "Notes"]] # NC sample data NC_SCRIPTS = SCRIPTS_HEADER + [ ["ADHD in the ER — What I See Every Shift", "TikTok", "What nurses see that doctors miss...", "2026-04-07", "Ready to Film", "Educational", ""], ["Hookah Myths Debunked", "TikTok", "Everything you think you know about hookah is wrong...", "2026-04-06", "Ready to Film", "Myth-Bust", ""], ["Chronic Stress — What the ER Taught Me", "TikTok", "I've coded patients who had no idea they were about to die...", "2026-04-05", "Ready to Film", "Story", ""], ["Sleep Hygiene for Night Shift Workers", "TikTok", "Night shift nurses age faster — here's why...", "2026-04-10", "Ready to Film", "Tips", ""], ["ADHD & Black Men — The Misdiagnosis Crisis", "TikTok", "I see this every single shift...", "2026-04-11", "Ready to Film", "Educational", ""], ["TikTok Live Framework for Nurses", "TikTok / Instagram", "How to go live and actually grow from it...", "2026-04-12", "Ready to Film", "Strategy", ""], ] NC_SCHEDULE = SCHEDULE_HEADER + [ ["2026-04-07", "TikTok", "ADHD in the ER", "Scheduled", ""], ["2026-04-08", "Instagram", "April Content Calendar posted", "Live", ""], ["2026-04-10", "TikTok", "Sleep Hygiene for Night Shift", "Draft", ""], ["2026-04-14", "TikTok / Instagram", "Hookah Myths", "Draft", ""], ] NC_NEWSLETTER = NEWSLETTER_HEADER + [ ["Healthcare Creator Starter Kit", "2026-05-01", "Draft", "All Subscribers", "", "", "First newsletter draft"], ] NC_PRODUCTS = PRODUCTS_HEADER + [ ["Healthcare Creator Starter Kit", "Digital Product", "FREE", "Pre-launch", "", "Intro guide for nurses who want to create content", ""], ["Nurse Creator Course", "Course", "$297", "Pre-launch", "", "Full video course on building a healthcare creator brand", ""], ] NC_PARTNERSHIPS = PARTNERSHIPS_HEADER + [ ["TBD Brand #1", "", "Prospecting", "Sponsorship", "", "", ""], ["TBD Brand #2", "", "Prospecting", "Affiliate", "", "", ""], ] NC_CONTRACTS = CONTRACTS_HEADER + [ # Empty for now — no active contracts ] NC_REVENUE = REVENUE_HEADER + [ # Empty for now ] NC_RESOURCES = RESOURCES_HEADER + [ ["Brand Strategy Deck", "Presentation", "https://deck.emersonnorth.com/nc-brand", ""], ["April Report", "Presentation", "https://deck.emersonnorth.com/nc-april", ""], ["Onboarding Guide", "Document", "", "Getting started with the portal"], ] # LL sample data LL_SCRIPTS = SCRIPTS_HEADER + [ ["Is Your Child Mouth Breathing?", "Instagram / Facebook", "This one sign could mean everything for your child's development...", "2026-04-04", "Delivered", "Educational", "Part of April campaign"], ["What is Myofunctional Therapy?", "Instagram", "Most dentists refer to us — here's why...", "2026-04-04", "Delivered", "Explainer", "Part of April campaign"], ["5 Signs Your Child Needs a Consult", "Instagram", "If you see any of these, don't wait...", "2026-04-04", "Delivered", "Tips", "Part of April campaign"], ] LL_SCHEDULE = SCHEDULE_HEADER + [ ["2026-04-04", "Instagram / Facebook", "April Campaign Scripts", "Live", ""], ["2026-04-02", "Instagram", "Services Carousel", "Live", ""], ["2026-04-10", "Newsletter", "Is Your Child Mouth Breathing?", "Scheduled", ""], ["2026-04-14", "Instagram", "Sandy Springs SEO post", "Draft", ""], ] LL_BLOG = BLOG_HEADER + [ ["Myofunctional Therapy in Sandy Springs", "myofunctional-therapy-sandy-springs", "Live", "2026-03-28", "myofunctional therapy, Sandy Springs, orofacial myology", "SEO page"], ["5 Signs Your Child is Mouth Breathing", "signs-child-mouth-breathing", "Draft", "", "mouth breathing, children, myofunctional therapy", ""], ] LL_NEWSLETTER = NEWSLETTER_HEADER + [ ["Is Your Child Mouth Breathing? Here's What to Look For", "2026-04-10", "Scheduled", "All Subscribers", "", "", "Thu 9AM ET send"], ["Welcome to Lasting Language", "2026-03-15", "Sent", "All Subscribers", "42%", "8%", "Welcome sequence #1"], ] LL_CAMPAIGNS = CAMPAIGNS_HEADER + [ ["Spring Awareness Campaign", "Social Media", "In Progress", "2026-04-01", "2026-04-30", "3-script mouth breathing awareness push"], ["Summer Enrollment Push", "Email + Social", "Draft", "2026-06-01", "2026-06-30", "Back-to-school angle"], ] LL_PRODUCTS = PRODUCTS_HEADER + [ ["Parent's Guide to Myofunctional Therapy", "Digital Product", "FREE", "Pre-launch", "", "Educational PDF for parents", ""], ["Virtual Consultation", "Service", "$150", "Active", "https://lastinglanguage.com/book", "30-minute online consult", ""], ] LL_REVENUE = REVENUE_HEADER + [ # Empty for now ] LL_RESOURCES = RESOURCES_HEADER + [ ["April Report", "Presentation", "https://deck.emersonnorth.com/lastingapril", ""], ["Scheduling Deck", "Presentation", "https://deck.emersonnorth.com/ll-scheduling", ""], ["Onboarding Guide", "Document", "", "Getting started with the portal"], ] # ── Tab definitions ──────────────────────────────────────────────────────────── NC_TABS = { "Dashboard": NC_DASHBOARD, "Scripts": NC_SCRIPTS, "Schedule": NC_SCHEDULE, "Newsletter": NC_NEWSLETTER, "Products": NC_PRODUCTS, "Partnerships": NC_PARTNERSHIPS, "Contracts": NC_CONTRACTS, "Revenue": NC_REVENUE, "Resources": NC_RESOURCES, } LL_TABS = { "Dashboard": LL_DASHBOARD, "Scripts": LL_SCRIPTS, "Schedule": LL_SCHEDULE, "Blog": LL_BLOG, "Newsletter": LL_NEWSLETTER, "Campaigns": LL_CAMPAIGNS, "Products": LL_PRODUCTS, "Revenue": LL_REVENUE, "Resources": LL_RESOURCES, } # ── Main ────────────────────────────────────────────────────────────────────── def setup_sheet(token, sheet_id, name, tabs_data): print(f"\n▶ {name} ({sheet_id})") existing = get_existing_tabs(token, sheet_id) print(f" Existing tabs: {existing}") # Create missing tabs to_create = [t for t in tabs_data if t not in existing] if to_create: add_sheets(token, sheet_id, to_create) # Write data to all tabs for tab, data in tabs_data.items(): if data: write_values(token, sheet_id, tab, data) else: print(f" Skipped {tab} (no data)") if __name__ == "__main__": token = get_access_token() print("Access token obtained.") setup_sheet(token, NC_SHEET, "Nurse Charles", NC_TABS) setup_sheet(token, LL_SHEET, "Lasting Language", LL_TABS) print("\n✓ Done.")