Zapier Automation: Automated Report Completion and Blank Tracking

For Medical Transcriptionists

Tools: Zapier + Google Sheets + Gmail | Time to build: 1-2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Google Sheets and Gmail for daily work — see Level 2 guides

What This Builds

Two connected automations that eliminate manual tracking for remote MTs: (1) When you send a completed report confirmation or upload a transcription, it automatically logs to a Google Sheet for invoicing. (2) When you send a physician a blank-clarification email, it logs the open blank; when they reply, it marks it resolved. No more manual spreadsheet updates, no more lost blanks, and invoicing becomes nearly automatic at week's end.

Prerequisites

• Zapier account — free tier available (zapier.com); free handles up to 100 tasks/month; Starter from {{tool:Zapier.price}} for more volume
• Gmail account for work email (automation works best with Gmail; Outlook requires Zapier paid plan)
• Google Sheets (free) for tracking
• Your email notifications contain consistent, recognizable text (e.g., "Report submitted" or "Transcription complete")

The Concept

Zapier is a connector — it watches for something to happen in one tool (like an email arriving in Gmail) and automatically does something in another tool (like adding a row to Google Sheets). You describe the rule once ("when X happens, do Y") and Zapier runs it automatically forever. For MTs, this means the tedious tracking tasks that eat 30 minutes per week happen invisibly in the background.

Build It Step by Step

Part 1: Set Up Your Google Tracking Sheet

Before building the automations, set up the destination spreadsheet:

1. Open Google Sheets (sheets.google.com) and create a new spreadsheet
2. Name it: "MT Work Tracker"
3. Create two tabs at the bottom: Completed Reports and Open Blanks

Completed Reports tab — add these column headers in row 1: | Date | Report Type | Physician | Line Count | Submitted Time | Invoice Status |

Open Blanks tab — add these column headers in row 1: | Date Sent | Physician | Report Description | Blank Description | Status | Date Resolved |

1. Save the sheet and copy its URL (you'll need it when setting up Zapier)

Part 2: Build Automation 1 — Report Completion Logger

This automation watches for report submission emails and logs them automatically.

1. Go to zapier.com and create a free account

2. Click Create Zap (a "Zap" is one automation)

3. Trigger (what starts the automation):

   • Search for and select Gmail
   • Choose trigger: New Email Matching Search
   • In the Search field, type the text that appears in your submission confirmation emails (e.g., "Report submitted" or "Transcription complete" or a subject line pattern)
   • Click Continue and authorize Zapier to access your Gmail
   • Test the trigger — Zapier should find a recent matching email

4. Action (what happens automatically):

   • Search for and select Google Sheets
   • Choose action: Create Spreadsheet Row
   • Connect to your Google account and select your "MT Work Tracker" spreadsheet
   • Select the Completed Reports tab
   • Map the email fields to your columns:

     • Date → Email received date
     • Report Type → You'll manually fill this (or use the email subject if it contains it)
     • Physician → From field (sender name)
     • Submitted Time → Email received time
   • Invoice Status → Type "Pending" as a default

5. Click Test to confirm a row gets added, then click Publish

What you should see: Every time a submission confirmation email arrives, a new row appears in your Completed Reports tab automatically — no manual entry.

Part 3: Build Automation 2 — Blank Tracking System

This automation watches for emails you send to physicians about blanks, then tracks when they reply.

Zap 2a — Log when you send a blank request:

1. Create a new Zap → Trigger: Gmail → New Email Sent
2. Add a filter step (Zapier's Filter app): Only continue if the email subject Contains a word you always use in blank emails (e.g., "clarification" or "re-dictate" or "audio blank")
3. Action: Google Sheets → Create Row in the Open Blanks tab

   • Date Sent → Send date
   • Physician → To field (recipient)
   • Report Description → Email subject
   • Blank Description → Email body (first 200 characters)
   • Status → "Open"

Zap 2b — Mark blank as resolved when physician replies:

1. Create another new Zap → Trigger: Gmail → New Email Matching Search

2. Search: Look for reply emails where you're the recipient and the subject contains "Re: [your blank keyword]"

3. Action: Google Sheets → Update Spreadsheet Row

   • Find the matching row by physician name
   • Update Status → "Resolved"
   • Update Date Resolved → Today's date

4. Publish both Zaps

Real Example: A Week of Automated Tracking

Monday through Friday:

• You complete and submit 8 reports → 8 rows auto-appear in Completed Reports
• You send 3 physician blank emails → 3 rows auto-appear in Open Blanks (Status: Open)
• Dr. Smith replies to one blank → that row auto-updates to Resolved

Friday afternoon invoicing:

• Open your Completed Reports tab: all 8 reports are listed with submission times
• Open Blanks: 2 still open (need follow-up), 1 resolved
• Copy the completed reports data → paste into your invoice template
• Total time for invoicing: 5 minutes vs. 35 minutes of manual log review

Time saved per week: 30-45 minutes of tracking and invoicing work.

What to Do When It Breaks

• Trigger doesn't fire → Check your Gmail filter text — it must exactly match what appears in your confirmation emails; try a simpler, shorter keyword
• Rows appear in wrong columns → Edit the Zap's Action step and re-map the fields; test with a new email
• Status doesn't update when physician replies → The reply subject line must contain the original subject text for Zapier to match it; check that your email client preserves "Re:" threading
• Zapier hits the free tier limit → The 100 tasks/month free tier covers ~50 completions + 50 emails; upgrade to Starter if you need more volume

Variations

• Simpler version: Just build Automation 1 (report completion logger) first — that alone saves 20+ minutes/week on invoicing tracking
• Extended version: Add a third Zap that emails you every Friday morning with a summary of that week's completed reports and any open blanks older than 3 days — your weekly status report is automatic

What to Do Next

• This week: Build Automation 1 (report completion logger) and run it for one full week; verify that every submitted report appears in the sheet
• This month: Add Automation 2 (blank tracking); after 30 days, check whether any blanks were lost or forgotten that the tracker caught
• Advanced: Connect the Google Sheet to a Google Data Studio report for a visual productivity dashboard that auto-updates weekly

Advanced guide for medical transcriptionist professionals. Free Zapier tier is sufficient for basic volume. Tool interfaces may change.