Eighty recurring clients, two billing cycles, one folder of pre-made PDFs. Pick a date and run it. The part worth watching is what happens when the PDF isn't where it should be — because that is the case that decides whether an automation like this is safe to leave alone.
Deliberately messy, the way a real folder is: near-duplicates, a draft, a wrong-month file, and two clients whose PDF simply isn't there yet.
Exact invoice-number matching, never "the first file that looks right." The folder contains INV-2026-0417.pdf, INV-2026-0417 (1).pdf and INV-2026-0417-DRAFT.pdf. A filename contains match would happily grab any of the three, and one of them is a draft. The matcher requires the invoice number to be the whole token, refuses when more than one candidate survives, and treats a missing file as a stop rather than a reason to improvise. Nothing gets sent on a guess.
Idempotency, so a re-run cannot double-send. Every send is keyed on client + invoice number and written back to the sheet before the next row is processed. Run the same date twice and the second run sends nothing — it recognises the work as done. That matters more than it sounds: the realistic failure is not the automation misfiring once, it is you re-running it after a partial failure and eighty clients getting a second copy. Press Run dispatch twice and watch the second run go quiet.
One correction on the brief, offered up front rather than after you've paid: files on a desktop or in iCloud Drive aren't reachable by a cloud automation — there is no API for them. The PDFs need to live in Google Drive, which is a one-time folder move, not extra build work. Better to know now.