Weekly Excel Report

Send automated weekly reports with Excel spreadsheet attachments. Perfect for B2B analytics, sales reports, and dashboard summaries.

The Problem

Automating weekly reports typically requires:

Querying data from your database
Generating an Excel file (using libraries like ExcelJS or openpyxl)
Composing an email with a summary
Attaching the Excel file
Scheduling the job (cron, cloud functions)

The Solution

FormaMail generates Excel attachments from templates. You provide the data, we generate the Excel file and attach it to the email - all in one API call.

Prerequisites

FormaMail account with API key
Email template for the report summary
Excel attachment template

Step-by-Step Guide

Design Your Excel Template

Create an attachment template with these typical sections:

Summary Sheet:

Report period
Key metrics (total sales, users, etc.)
Charts (coming soon)

Data Sheet:

Raw data in tabular format
Columns defined in template
Data populated from variables

Define Your Variables

Variable	Type	Description
`reportPeriod`	string	”Week of Nov 18-24, 2024”
`totalSales`	number	Summary metric
`totalOrders`	number	Summary metric
`topProducts`	array	Top performing items
`dailyData`	array	Day-by-day breakdown

Create the Email Template

A summary email that highlights key findings:

Hi Team,
 
Here's your weekly sales report for {{reportPeriod}}.
 
**Key Highlights:**
- Total Sales: ${{totalSales}}
- Orders: {{totalOrders}}
- Top Product: {{topProducts.0.name}}
 
The full report is attached as an Excel spreadsheet.
 
Best,
Analytics Team

Send the Weekly Report

// Run this on a schedule (cron job, cloud function, etc.)
 
async function sendWeeklyReport() {
  // 1. Query your data
  const reportData = await getWeeklyAnalytics();
 
  // 2. Send via FormaMail
  const response = await fetch('https://api.formamail.com/api/emails/send', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${process.env.FORMAMAIL_API_KEY}`,
      'Content-Type': 'application/json'
    },
    body: JSON.stringify({
      templateId: "weekly-report-email",
      to: [
        { email: "sales-team@company.com", name: "Sales Team" },
        { email: "leadership@company.com", name: "Leadership" }
      ],
      variables: {
        reportPeriod: "Week of Nov 18-24, 2024",
        totalSales: reportData.totalSales,
        totalOrders: reportData.totalOrders,
        averageOrderValue: reportData.aov,
        topProducts: reportData.topProducts.slice(0, 5),
        dailyData: reportData.dailyBreakdown
      },
      attachments: [{
        attachmentTemplateId: "weekly-report-excel",
        outputFormats: ["excel"],
        filename: `sales-report-${getWeekNumber()}.xlsx`
      }]
    })
  });
 
  console.log('Weekly report sent:', await response.json());
}

Full Code Example

Node.js with Scheduled Job

const cron = require('node-cron');
 
// Run every Monday at 9 AM
cron.schedule('0 9 * * 1', async () => {
  console.log('Sending weekly report...');
 
  try {
    // Get last week's date range
    const endDate = new Date();
    endDate.setDate(endDate.getDate() - 1); // Yesterday
    const startDate = new Date(endDate);
    startDate.setDate(startDate.getDate() - 6); // 7 days ago
 
    // Query your analytics
    const analytics = await db.query(`
      SELECT
        DATE(created_at) as date,
        COUNT(*) as orders,
        SUM(total) as revenue
      FROM orders
      WHERE created_at BETWEEN $1 AND $2
      GROUP BY DATE(created_at)
      ORDER BY date
    `, [startDate, endDate]);
 
    const topProducts = await db.query(`
      SELECT product_name, SUM(quantity) as units_sold, SUM(total) as revenue
      FROM order_items oi
      JOIN orders o ON o.id = oi.order_id
      WHERE o.created_at BETWEEN $1 AND $2
      GROUP BY product_name
      ORDER BY revenue DESC
      LIMIT 10
    `, [startDate, endDate]);
 
    // Calculate totals
    const totalRevenue = analytics.reduce((sum, day) => sum + day.revenue, 0);
    const totalOrders = analytics.reduce((sum, day) => sum + day.orders, 0);
 
    // Send via FormaMail
    await fetch('https://api.formamail.com/api/emails/send', {
      method: 'POST',
      headers: {
        'Authorization': `Bearer ${process.env.FORMAMAIL_API_KEY}`,
        'Content-Type': 'application/json'
      },
      body: JSON.stringify({
        templateId: "weekly-report-email",
        to: [
          { email: "sales@company.com" },
          { email: "ceo@company.com" }
        ],
        variables: {
          reportPeriod: `${formatDate(startDate)} - ${formatDate(endDate)}`,
          totalRevenue: totalRevenue.toFixed(2),
          totalOrders: totalOrders,
          averageOrderValue: (totalRevenue / totalOrders).toFixed(2),
          topProducts: topProducts.rows,
          dailyBreakdown: analytics.rows.map(row => ({
            date: formatDate(row.date),
            orders: row.orders,
            revenue: row.revenue.toFixed(2)
          }))
        },
        attachments: [{
          attachmentTemplateId: "weekly-report-excel",
          outputFormats: ["excel"],
          filename: `weekly-report-${formatDateShort(startDate)}.xlsx`
        }]
      })
    });
 
    console.log('Weekly report sent successfully');
  } catch (error) {
    console.error('Failed to send weekly report:', error);
  }
});
 
function formatDate(date) {
  return date.toLocaleDateString('en-US', {
    month: 'short',
    day: 'numeric',
    year: 'numeric'
  });
}
 
function formatDateShort(date) {
  return date.toISOString().split('T')[0];
}

Python with AWS Lambda

import json
import os
import boto3
from datetime import datetime, timedelta
import httpx
 
def lambda_handler(event, context):
    """AWS Lambda function triggered by CloudWatch Events (weekly schedule)"""
 
    # Calculate date range
    end_date = datetime.now() - timedelta(days=1)
    start_date = end_date - timedelta(days=6)
 
    # Query your data source (example with DynamoDB)
    dynamodb = boto3.resource('dynamodb')
    table = dynamodb.Table('orders')
 
    # ... query logic ...
 
    # Prepare report data
    report_data = {
        'reportPeriod': f"{start_date.strftime('%b %d')} - {end_date.strftime('%b %d, %Y')}",
        'totalRevenue': 15420.50,
        'totalOrders': 127,
        'topProducts': [
            {'name': 'Widget Pro', 'units': 45, 'revenue': 4500},
            {'name': 'Gadget Plus', 'units': 32, 'revenue': 3200},
        ],
        'dailyBreakdown': [
            {'date': 'Mon', 'orders': 18, 'revenue': 2100},
            {'date': 'Tue', 'orders': 22, 'revenue': 2450},
            # ... more days
        ]
    }
 
    # Send via FormaMail
    response = httpx.post(
        'https://api.formamail.com/api/emails/send',
        headers={
            'Authorization': f"Bearer {os.environ['FORMAMAIL_API_KEY']}",
            'Content-Type': 'application/json'
        },
        json={
            'templateId': 'weekly-report-email',
            'to': [{'email': 'team@company.com'}],
            'variables': report_data,
            'attachments': [{
                'attachmentTemplateId': 'weekly-report-excel',
                'outputFormats': ['excel'],
                'filename': f"weekly-report-{start_date.strftime('%Y-%m-%d')}.xlsx"
            }]
        }
    )
 
    return {
        'statusCode': 200,
        'body': json.dumps('Weekly report sent')
    }

Excel Template Tips

Multiple Sheets

Your Excel template can include multiple sheets:

Summary - Key metrics and highlights
Daily Breakdown - Day-by-day data
Top Products - Product performance
Raw Data - Complete dataset for further analysis

Data Tables

Use the {{#each}} helper to populate rows:

| Date | Orders | Revenue |
|------|--------|---------|
{{#each dailyBreakdown}}
| {{date}} | {{orders}} | ${{revenue}} |
{{/each}}

Formatting Numbers

Pre-format numbers in your code or use template helpers:

variables: {
  revenue: 15420.50,
  revenueFormatted: '$15,420.50',
  percentChange: '+12.5%'
}

Scheduling Options

Platform	How to Schedule
AWS Lambda	CloudWatch Events rule
Google Cloud Functions	Cloud Scheduler
Azure Functions	Timer trigger
Vercel	Cron jobs (vercel.json)
Railway/Render	Built-in cron
Self-hosted	node-cron, systemd timer

Next Steps

B2B Invoice Email Order Confirmation + Receipt