Introduction

TL;DR: Staff were finishing events and then someone had to manually calculate hours, split commissions between multiple workers, figure out travel bonuses, and generate invoices. I built an automated system using Google Forms and Apps Script that handles all of it - including PDF invoice generation and approval routing. Here's exactly how, with code you can steal.

The Problem: Post-Event Chaos

I was working as Event Lead at Wild Life Distillery in Canmore, Alberta. We ran three types of events: farmers markets, consumer events (festivals, corporate tastings), and liquor store tastings.

After every event, staff needed to submit a report. Simple enough, right?

Except the report triggered a cascade of manual work:

• Calculate hours worked — including handling overnight shifts and different time zones

• Split commissions — when multiple staff worked an event, commission needed to be divided based on hours worked

• Apply different pay rates — market events paid $18/hour plus commission, consumer events paid $25/hour flat

• Track travel bonuses — staff traveling to events got an extra hour added

• Generate monthly invoices — aggregate all contractor work into proper invoices

• Route approvals — invoices under $500 went to one manager, over $500 to another

• Create PDF invoices — professional documents for payment processing

Someone was doing all of this in spreadsheets. Manually. Every month.

It was slow, error-prone, and frankly soul-crushing work that nobody should be doing by hand.

The Solution: A Complete Automation Stack

I built a system using Google Forms, Google Sheets, and Google Apps Script that handles the entire workflow automatically.

Here's what it does:

1. Staff submit a post-event form (takes 2 minutes)

2. System automatically routes the submission based on event type

3. Calculates hours, commissions, and pay for each staff member

4. Handles commission splits when multiple people work the same event

5. Logs everything to a master tracker

6. Generates monthly invoices for contractors

7. Routes invoices to the appropriate approver

8. Creates professional PDF invoices with line-item detail

9. Emails PDFs to relevant people

All automatic. No manual calculation. No copy-pasting between spreadsheets.

The Architecture

The system is split into modular files, each handling a specific responsibility:

This modular approach means I can update commission rates without touching invoice logic, or change the PDF template without breaking event processing.

Parsing Time Values from Google Forms (The 26:08 Bug)

Here's something that'll save you hours of debugging: Google Forms time values can be weird.

When I pulled time values from form responses, some event types had a mysterious 26 minutes and 8 seconds offset. A 9:00 AM start would show up as 9:26:08 AM in the code.

I never figured out why. But I did figure out how to fix it:

parseFormTime: function(timeValue, applyConsumerOffset = false) {
  // Handle null/undefined/empty
  if (!timeValue || timeValue === '') {
    return new Date(NaN);
  }
  
  // Use a fixed reference date for consistent time handling
  const referenceDate = new Date(2000, 0, 1);
  
  // Case 1: Already a Date object (most common from Google Forms)
  if (timeValue instanceof Date && !isNaN(timeValue.getTime())) {
    let hours = timeValue.getHours();
    let minutes = timeValue.getMinutes();
    let seconds = timeValue.getSeconds();
    
    // Apply offset correction for Consumer/Liquor Store events
    // Forms have a weird 26:08 offset bug for these event types
    if (applyConsumerOffset) {
      const totalSeconds = (hours * 3600) + (minutes * 60) + seconds;
      const correctedSeconds = totalSeconds - (26 * 60) - 8;
      
      hours = Math.floor(correctedSeconds / 3600);
      minutes = Math.floor((correctedSeconds % 3600) / 60);
      seconds = correctedSeconds % 60;
    }
    
    // Create a clean date with the time components
    const date = new Date(referenceDate);
    date.setHours(hours, minutes, seconds, 0);
    return date;
  }
  
  // Case 2: String format: "8:00:00 AM" or "8:00 AM"
  const timeStr = String(timeValue).trim();
  const timeMatch = timeStr.match(/(\d{1,2}):(\d{2})(?::(\d{2}))?\s*(AM|PM)/i);
  
  if (timeMatch) {
    let hours = parseInt(timeMatch[1]);
    let minutes = parseInt(timeMatch[2]);
    let seconds = timeMatch[3] ? parseInt(timeMatch[3]) : 0;
    const meridiem = timeMatch[4].toUpperCase();
    
    // Convert to 24-hour format
    if (meridiem === 'PM' && hours !== 12) {
      hours += 12;
    } else if (meridiem === 'AM' && hours === 12) {
      hours = 0;
    }
    
    const date = new Date(referenceDate);
    date.setHours(hours, minutes, seconds, 0);
    return date;
  }
  
  // Failed to parse
  return new Date(NaN);
}

If you're pulling time values from Google Forms and they're consistently off by a weird amount, you might need a similar correction. Log the raw values, figure out the offset, and apply a correction.

Creating a Custom Menu

Google Sheets lets you add custom menus. This makes the system usable by non-technical staff:

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu('WLD Actions');
  
  // Test Mode submenu
  menu.addSubMenu(
    ui.createMenu('🧪 TEST MODE')
      .addItem('Check Test Mode Status', 'checkTestModeStatus')
      .addItem('Toggle Test Mode ON/OFF', 'toggleTestMode')
      .addSeparator()
      .addItem('Test: Import Form Entries', 'testImportNewFormEntries')
  );
  
  menu.addSeparator();
  menu.addItem('📥 Import New Form Entries', 'importNewFormEntries');
  
  // Monthly Invoice submenu
  menu.addSubMenu(
    ui.createMenu('💰 Monthly Invoices')
      .addItem('Generate ALL Monthly Invoices', 'generateAllMonthlyInvoices')
      .addItem('Generate Invoice for Selected Row', 'generateInvoiceForSelectedRow')
  );
  
  // System Setup submenu
  menu.addSubMenu(
    ui.createMenu('⚙️ System Setup')
      .addItem('Setup All Triggers', 'setupAllTriggers')
      .addItem('List Current Triggers', 'listCurrentTriggers')
      .addItem('Delete All Triggers', 'deleteAllTriggers')
  );
  
  menu.addToUi();
}

Emojis in menu items make them easier to scan. Group related functions into submenus. Add separators between sections.

Generating Professional PDF Invoices

When an invoice is approved, the system generates a professional PDF with the company logo, line-item detail, and proper formatting.

The trick is to build HTML, then convert it to PDF:

generateInvoicePDF: function(rowNumber, sendEmail = false) {
  // ... gather invoice data ...
  
  // Get line items from tracker
  const lineItems = this.getContractorLineItems(trackerSheet, name, startDate, endDate);
  
  // Build line items HTML
  let lineItemsHtml = '';
  lineItems.forEach(item => {
    const eventDate = Utilities.formatDate(new Date(item.date), Session.getScriptTimeZone(), "MMM d");
    const lineTotal = parseFloat(item.hourlyPay) + parseFloat(item.commission);
    const hourlyRate = item.eventType === 'Market' ? '$18.00' : '$25.00';
    
    lineItemsHtml += `
      <tr>
        <td style="padding: 8px; border-bottom: 1px solid #ddd;">${eventDate}</td>
        <td style="padding: 8px; border-bottom: 1px solid #ddd;">${item.eventName}</td>
        <td style="padding: 8px; text-align: center;">${hourlyRate}</td>
        <td style="padding: 8px; text-align: center;">${item.hours}</td>
        <td style="padding: 8px; text-align: right;">$${parseFloat(item.commission).toFixed(2)}</td>
        <td style="padding: 8px; text-align: right; font-weight: bold;">$${lineTotal.toFixed(2)}</td>
      </tr>
    `;
  });
  
  // Build full HTML document
  const html = `
    <!DOCTYPE html>
    <html>
      <head>
        <style>
          body { font-family: 'Helvetica', sans-serif; margin: 40px; }
          .header { border-bottom: 3px solid #2c5f2d; padding-bottom: 20px; }
          .invoice-title { font-size: 32px; color: #2c5f2d; }
          table { width: 100%; border-collapse: collapse; }
          th { background-color: #2c5f2d; color: white; padding: 12px; }
        </style>
      </head>
      <body>
        <!-- Header with logo, invoice details, line items, totals -->
      </body>
    </html>
  `;
  
  // Convert HTML to PDF blob
  const blob = HtmlService.createHtmlOutput(html)
    .getBlob()
    .setName(`${invoiceNum}.pdf`);
  
  // Save to Drive folder
  const folder = DriveApp.getFolderById(PDF_FOLDER_ID);
  const pdfFile = folder.createFile(blob);
  
  // Update sheet with PDF link
  approvalsSheet.getRange(rowNumber, 13).setValue(pdfFile.getUrl());
  
  // Email if requested
  if (sendEmail) {
    MailApp.sendEmail({
      to: 'admin@company.com',
      subject: `Invoice ${invoiceNum} - ${name}`,
      body: 'Please find attached invoice for processing.',
      attachments: [blob]
    });
  }
}

The HtmlService.createHtmlOutput().getBlob() method is the magic. Build your HTML with inline styles (external stylesheets won't work), convert to blob, save to Drive.

Results

Hours of manual work eliminated every month

• Zero commission calculation errors since launch

• Professional PDF invoices generated automatically

• Clear approval routing with full audit trail

• System is still running months after I left the company

The HR team now manages everything themselves. They update the form when needed, approve invoices through the sheet, and PDFs generate automatically.

Code You Can Steal

Here are the most reusable pieces from this project:

Time Parsing with Offset Correction Use this when pulling time values from Google Forms. Adjust the offset if your forms have different weirdness.

Commission Split Calculator The formula for splitting commission based on hours worked. Works for any number of staff members.

Test Mode System The pattern of defaulting to test mode and using PropertiesService for persistent storage. Use this in any automation that sends emails.

HTML to PDF Conversion HtmlService.createHtmlOutput(html).getBlob() — remember to use inline styles and attach a descriptive filename.

onEdit Trigger Pattern Check sheet name, check column, check row, then act. Early returns keep the code clean.

Lessons Learned

1. Modular file structure matters. When I needed to change the commission rate, I only touched CommissionUtils.gs. Everything else kept working.

2. Default to safe mode. Test mode being the default saved me from sending embarrassing test emails to real people multiple times during development.

3. Log everything. I created a Logs sheet that captures every action. When something goes wrong (and it will), you need to see what happened.

4. Build for handoff. I knew I wouldn't be there forever. Every function has clear comments. The menu system is self-explanatory. Documentation exists.

5. Google Forms time values are weird. Just accept it and build correction logic.

Final Thoughts

This project took about 3 weeks of part-time work to build, test, and document. It now saves hours of manual work every month and has eliminated an entire category of errors.

The best part? It's all built on tools the company already had. Google Workspace. No additional licenses. No SaaS subscriptions. Just Apps Script gluing everything together.

If you're looking at a similar problem — manual data processing, complex calculations, PDF generation, approval workflows — this stack can probably solve it. The code examples above should get you started.

Got questions about this project or building something similar? Get in touch — I'm always happy to talk automation.

About the Author

Chad Rossi is an IT automation specialist based in Canmore, Alberta. He specializes in Google Workspace automation, Microsoft 365 administration, and eliminating manual processes that make people hate their jobs. When he's not building automation systems, he's photographing wildlife or treating complex IT problems like Dark Souls boss fights — tough, but worth it.

View my full experience →