📚 Internal Newsletter System - Complete Setup Guide

This guide will walk you through setting up your own automated internal newsletter system. The system creates weekly newsletters, tracks their status, and displays them in your own custom web app.

📁 Initial Setup

Step 1: Create a Folder Structure

1. Go to your Google Drive
2. Create a new folder called "Internal Newsletter System"
3. Inside this folder, create a subfolder called "Newsletters"

Step 2: Copy the Template Document

1. Open the template document: Internal Newsletter Template
2. Click "Make a copy"
3. Save it to your "Internal Newsletter System" folder
4. Rename it to "Internal Newsletter Template"

Step 3: Copy the Tracking Spreadsheet

1. Open the tracking spreadsheet: Newsletter Tracking Sheet
2. Click "Make a copy"
3. Save it to your "Internal Newsletter System" folder
4. Rename it to "Newsletter Tracking"

🔐 Script Authorization

When you first run any function in the Apps Script, you'll see a security warning. This is normal and expected.

Why You See This Warning

Google shows this warning for ANY custom script to protect users from potentially harmful code. The warning appears because:

1. The script isn't published on the Google Workspace Marketplace
2. Google hasn't verified the script's developer
3. The script requires access to your Google Drive and Spreadsheets

Why It's Safe to Proceed

This script is completely safe because:

• ✅ It's YOUR copy of the script running in YOUR Google account
• ✅ It only accesses YOUR Google Drive files
• ✅ No data is sent to external services
• ✅ All operations happen within your Google Workspace

How to Authorize the Script

1. When you see "Google hasn't verified this app", click "Advanced" (bottom left)
2. Click "Go to Newsletter Automation (unsafe)"
3. Review the permissions and click "Allow"

These permissions are necessary for the script to:

• Create newsletter documents from your template
• Track newsletter status in your spreadsheet
• Create a web app to display published newsletters

⚙️ Configuration

After authorization, you need to update the script with your specific file IDs:

Step 1: Get Your File IDs

1. Template Document ID:
2. Open your copied template document
3. Look at the URL: https://docs.google.com/document/d/YOUR_TEMPLATE_ID/edit
4. Copy the YOUR_TEMPLATE_ID portion
5. Spreadsheet ID:
6. Open your copied tracking spreadsheet
7. Look at the URL: https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit
8. Copy the YOUR_SPREADSHEET_ID portion
9. Newsletter Folder ID:
10. Open your "Newsletters" folder
11. Look at the URL: https://drive.google.com/drive/folders/YOUR_FOLDER_ID
12. Copy the YOUR_FOLDER_ID portion

Step 2: Update the Script

1. In your tracking spreadsheet, click Extensions > Apps Script
2. Find the configuration section at the top of the script:

// ============================================================================
// 📋 REQUIRED CONFIGURATION - REPLACE WITH YOUR IDs
// ============================================================================

const TEMPLATE_DOC_ID = 'YOUR_TEMPLATE_ID'; // Your template document ID
const SPREADSHEET_ID = 'YOUR_SPREADSHEET_ID'; // Your tracking spreadsheet ID
const NEWSLETTER_FOLDER_ID = 'YOUR_FOLDER_ID'; // Your newsletter folder ID
const SHEET_NAME = 'Sheet1'; // Name of the sheet tab in your spreadsheet

1. Replace the placeholder IDs with your actual IDs
2. Click Save (disk icon)

Step 3: Customize Appearance (Optional)

You can customize the web app's appearance by modifying these variables:

// ============================================================================
// 🎨 WEB APP CUSTOMIZATION - CHANGE THESE TO MATCH YOUR BRAND
// ============================================================================

// Page Content
const WEB_APP_TITLE = 'Product Marketing Newsletters';
const WEB_APP_SUBTITLE = 'Stay updated with our latest internal communications';
const FOOTER_CREDIT_TEXT = 'PMM Jetpack';
const FOOTER_CREDIT_URL = '<https://productivepmm.com/jetpack>';

// Color Scheme (use any valid CSS colors)
const PRIMARY_COLOR = '#0066cc';        // Main accent color (links, hover effects)
const PRIMARY_HOVER = '#0052a3';        // Darker shade for hover states
const TEXT_COLOR = '#333333';           // Main text color
const LIGHT_TEXT_COLOR = '#666666';     // Subtitle and secondary text

📝 Creating Your First Newsletter

Manual Creation

1. In your spreadsheet, click Extensions > Apps Script
2. Click the Select function dropdown and choose testCreateNewsletter
3. Click Run
4. You'll see a new newsletter document created in your Newsletters folder
5. A new row will be added to your tracking spreadsheet

What Happens

• A copy of your template is created with the title "Internal Newsletter - Week of [date]"
• The copy is saved in your Newsletters folder
• A new row is added to your tracking spreadsheet with:
• Week of: The Monday of the current week
• Title: The newsletter title
• Newsletter Link: A link to the new document
• Video Link: Empty (for you to add later)
• Status: "Not Published"

📢 Publishing Newsletters

When a newsletter is ready to be shown in the web app:

1. Open your tracking spreadsheet
2. Find the row for the newsletter you want to publish
3. In the "Status" column, change "Not Published" to "Published"
4. (Optional) Add a video link in the "Video Link" column

The newsletter will now appear in the web app!

Supported Video Platforms

You can add videos from:

• YouTube (e.g., https://www.youtube.com/watch?v=VIDEO_ID)
• Loom (e.g., https://www.loom.com/share/VIDEO_ID)
• Vimeo (e.g., https://vimeo.com/VIDEO_ID)

⏰ Setting Up Automation

To automatically create newsletters every week:

1. In your spreadsheet, click Extensions > Apps Script
2. Click the Select function dropdown and choose setupWeeklyTrigger
3. Click Run

This sets up a trigger to create a new newsletter every Monday at 9:00 AM.

To customize the schedule, modify these variables:

// ============================================================================
// ⚙️ AUTOMATION SETTINGS - CUSTOMIZE YOUR SCHEDULE
// ============================================================================

const NEWSLETTER_TITLE_FORMAT = 'Internal Newsletter - Week of'; // Prefix for newsletter titles
const AUTOMATION_DAY = ScriptApp.WeekDay.MONDAY;                 // Day of week to create newsletters
const AUTOMATION_HOUR = 9;                                      // Hour (24-hour format) to create newsletters

🌐 Deploying the Web App

To make your newsletters accessible via a web app:

1. In your spreadsheet, click Extensions > Apps Script
2. Click Deploy > New deployment
3. Select Web app as the deployment type
4. Configure the deployment:
5. Description: "Newsletter Web App"
6. Execute as: "Me"
7. Who has access: Choose appropriate access level
8. "Only myself" (just for you)
9. "Anyone within [your organization]" (for internal use)
10. "Anyone" (public access)
11. Click Deploy
12. Copy the Web app URL that appears

You now have a web app that displays all newsletters marked as "Published"!

🎨 Customization Options

Newsletter Template

Modify your template document to change the format of all future newsletters.

Web App Appearance

Customize colors, fonts, and text by modifying the variables in the "WEB APP CUSTOMIZATION" section.

Newsletter Creation Schedule

Change the day and time for automatic newsletter creation in the "AUTOMATION SETTINGS" section.

🔧 Troubleshooting

Newsletter Not Created in Correct Folder

Run the checkTemplateLocation function to verify your template document location.

Web App Not Showing Newsletters

1. Check that newsletters are marked as "Published" in the spreadsheet
2. Verify the spreadsheet ID in the script configuration
3. Create a new deployment of the web app

Authorization Issues

If you see "You do not have permission to access the requested resource":

1. Make sure you're signed in with the same Google account that owns the script
2. Try reauthorizing the script by running any function

❓ FAQ

Is this script safe to use?

Yes! The script only accesses your own Google Drive files and doesn't send data anywhere else. The security warning is standard for any custom script that hasn't been verified by Google.

Can I share this with my team?

Yes! You can:

1. Share the web app URL with anyone who needs to view newsletters
2. Share the spreadsheet with editors who need to manage newsletters
3. Share this documentation to help others set up their own system

How do I add videos to newsletters?

Add video URLs to the "Video Link" column in your spreadsheet. The system supports YouTube, Loom, and Vimeo links.

Can I customize the newsletter format?

Yes! Modify your template document, and all future newsletters will use that format.

How do I stop automatic newsletter creation?

Run the removeAllTriggers function to remove all scheduled triggers.

🚀 Need Help?

If you encounter any issues or have questions about the newsletter system, please contact support@productivepmm.com.

Happy newsletter creating! 📝