Track Agency Billable Hours in Google Sheets with Automated Invoicing

Creative agencies bleed revenue in 15-minute increments. You did "a quick edit" for a client but forgot to log it. At the end of the month, that's $500 lost.

Harvest and Toggl are great, but your team effectively lives in Google Calendar. Why force them to open another tab?

The Simple Approach

Tag your Calendar events with the client name like [Nike] Design Review. The script scans your calendar, pulls out those tagged events, calculates how long they were, logs them to a spreadsheet, and multiplies by your hourly rate.

The Code: Time Scraper

1. Setup

• Calendar: Use your primary calendar.
• Sheet: "Timesheet" (Col A: Date, B: Client, C: Description, D: Duration, E: Amount).
• Protocol: Name events like [Nike] Website Mockup or [Acme] Strategy Call.

2. The Script

const RATE = 150; // Hourly Rate
 
function importBillableHours() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Timesheet');
  const calendar = CalendarApp.getDefaultCalendar();
  
  // Define Range (e.g., Last Week)
  const today = new Date();
  const start = new Date(today.getTime() - (7 * 24 * 60 * 60 * 1000));
  const events = calendar.getEvents(start, today);
  
  events.forEach(event => {
    const title = event.getTitle();
    
    // Check for [Client] tag
    if (title.startsWith('[')) {
      const client = title.substring(1, title.indexOf(']'));
      const desc = title.substring(title.indexOf(']') + 1).trim();
      const durationHrs = (event.getEndTime() - event.getStartTime()) / (1000 * 60 * 60);
      
      // Basic Duplication Check (by Event ID ideally, here simple logic)
      // appendRow([Date, Client, Desc, Duration, Amount])
      sheet.appendRow([
        event.getStartTime(),
        client,
        desc,
        durationHrs,
        durationHrs * RATE
      ]);
    }
  });
}

3. Invoice It

Combine this with our PDF Invoice Generator script. Run importBillableHours(). Review the rows. Then run generateInvoices().

Why This Wins

Frictionless. Your team is already putting meetings on the calendar. Now those meetings are the timesheet. Recovered Revenue. You capture every "Quick Call" that usually slips through the cracks.