Automation Recipe: Never Miss an Appraisal Deadline Again

What This Builds

A semi-automated deadline tracking workflow where entering a new appraisal order into a Google Sheet automatically calculates the due date, creates a Google Calendar event with inspector and report-due milestones, and sends you a reminder email 24 hours before each deadline. using Google's built-in automation (Apps Script) prompted and written by Claude. After setup, managing deadlines takes 60 seconds per order instead of the mental overhead of tracking 10+ open orders across different AMC portals.

Prerequisites

• Gmail and Google Calendar account (free)
• Google Sheets familiarity (see Level 2 guide)
• Claude account (free tier sufficient for generating the script)
• Your existing AMC order tracking sheet (or build one first. see Level 3 guide: "Build an AMC Order Tracking System")
• Comfortable copying code into Google Apps Script (we'll walk through it)

The Concept

Google Apps Script is Google's built-in scripting language that connects Google Sheets, Calendar, Gmail, and other Google products. It runs automatically on a schedule or when triggered by an event (like you adding a new row to a spreadsheet). It sounds intimidating, but Claude will write the entire script for you. Your job is to tell Claude what you need, copy the output, and paste it into Google Sheets.

Think of it as your order management system hiring an assistant who never forgets to check the calendar and always reminds you 24 hours before something is due.

Build It Step by Step

Part 1: Use Claude to Write Your Automation Script

Step 1: Open Claude. Paste this prompt (fill in your specifics):

I need a Google Apps Script that connects to a Google Sheet where I track appraisal orders. Write me a complete, working script that:

1. Runs when a new row is added to a Sheet called "Orders"
2. The Sheet has these columns: A=Order Date, B=AMC Name, C=Property Address, D=Fee, E=Due Date, F=Status, G=Calendar Event ID (this column is for the script to fill in)
3. When a new row is added: creates a Google Calendar event titled "[AMC Name] - [Property Address] - DUE" on the Due Date, set as all-day
4. Also creates a second calendar event "REMINDER: [Property Address] report due TOMORROW" on the day before the Due Date, set as all-day
5. Sends me an email reminder to [my-email@gmail.com] the morning of the due date with: property address, AMC name, fee, and a link to the Order tracking sheet
6. Writes the Calendar Event ID back to column G so I can track it

Make the script complete and ready to paste into Google Apps Script. Include comments explaining each section.

What Claude gives you: A complete Google Apps Script, approximately 60-80 lines of code, with comments explaining each part. You don't need to understand the code, just copy it.

Part 2: Install the Script

Step 1: Open your Google Sheets order tracker. Go to Extensions → Apps Script. A new browser tab opens showing a code editor.

Step 2: Delete any existing code in the editor. Paste Claude's script. Click the Save button (disk icon) and name the project "Appraisal Deadline Tracker."

Step 3: Set up the trigger. In the Apps Script editor, click the clock icon (Triggers) in the left sidebar. Click "+ Add Trigger":

• Function: onSheetEdit (or whatever function name Claude used. it will tell you)
• Event source: From spreadsheet
• Event type: On edit
• Click Save

Step 4: Authorize the script. Google will ask you to grant permissions for the script to access Calendar, Gmail, and Sheets. Click through the authorization screens. This is your own script running on your own account.

What you should see: The trigger appears in your triggers list. The script is now active.

Part 3: Test It

Open your Google Sheet. Add a new order row with all fields filled in, including a future Due Date. Wait 10-15 seconds.

Check Google Calendar: A new all-day event should appear on the Due Date. A second event should appear the day before.

Check Gmail: In your Sent or Drafts folder, verify the script sent or queued a confirmation email.

Troubleshooting: If nothing happens, go to Extensions → Apps Script → Executions to see if the script ran and if there were errors. Copy any error message and ask Claude: "My Apps Script got this error: [paste error]. Fix the script."

Part 4: Customize for Your Workflow

Once the basic version works, use Claude to add enhancements:

Add turn-time calculation:

Modify the script: when a new row is added, automatically calculate the Due Date in column E as: Order Date (column A) + [7] business days. I work Monday-Friday, skip federal holidays.

Add color coding:

Add conditional formatting: when Status (column F) is changed to "Submitted," change the entire row background color to green. When Status is "Revision," change to orange.

Add weekly summary email:

Add a separate function that runs every Monday morning and emails me a summary of: orders due this week, orders currently in revision status, and total invoiced amount for the month so far.

Real Example: First Week Running the Automation

Monday: You receive 3 new AMC orders. You enter them in your Sheet (2 minutes total). The script automatically:

• Creates 6 calendar events (2 per order: due date + day-before reminder)
• Sends 3 confirmation emails with order details

Thursday: You open your calendar and see "REMINDER: 456 Oak Street report due TOMORROW" as an all-day event. Without the automation, you might have forgotten this order was due Friday, you were focused on the revision request from another AMC.

Friday morning: You receive an email at 8am: "Order due today: 456 Oak Street, [AMC Name], $425 fee." You submit the report by noon.

What you get: Zero missed deadlines. Zero mental overhead tracking due dates across 4-6 AMC portals. The system remembers so you don't have to.

Setup: You triggered this outcome by entering 3 rows in a spreadsheet on Monday. 2 minutes of total effort.

What to Do When It Breaks

• Calendar events not creating → Check Apps Script Executions log for errors; most common issue is authorization (re-run authorization in Apps Script editor)
• Wrong due date calculated → Verify the date format in your Sheet matches what Claude's script expects (MM/DD/YYYY vs. YYYY-MM-DD); ask Claude to fix the date parsing
• Getting too many reminder emails → Ask Claude to modify the script to send only one reminder email per order (add a check: if Column G already has a Calendar Event ID, skip creating duplicate events)
• Script stopped running after Google password change → Re-authorize the script: Extensions → Apps Script → Run the function once manually to trigger re-authorization

Variations

• Simpler version: Skip the email reminders entirely, just create Calendar events. This alone eliminates deadline misses and takes 30 minutes to set up.
• Extended version: Add a Slack webhook integration so reminders appear in a Slack channel; connect to a Zapier workflow that also sends an SMS reminder

What to Do Next

• This week: Build the basic version (Calendar events only); verify it works for 5 new orders
• This month: Add the email reminder; track whether you catch any near-misses you would have otherwise missed
• Advanced: Use Claude to add a weekly Monday summary email showing your full active workload at a glance

Advanced guide for property appraiser professionals. These techniques use Google Apps Script and require some tolerance for technical setup.