Weekly hospital formulary safety and supply digest from FDA data

Build me an agent workflow that sends a weekly safety and supply digest for the drugs on my hospital formulary. Trigger it on a cron every Monday at 7:00 AM US Eastern.

Read my formulary from a Google Sheets tab using Get Values. The sheet has one row per drug with these columns: brand name, generic name, therapeutic class, and the clinical lead's email. Make the spreadsheet ID, tab name, and range configurable on the workflow.

For each drug in the sheet, the agent should call openFDA three times:

1) Search Drug Shortages for the current shortage status and any newly resolved shortages. Match on generic name and brand name using the normalized openfda fields. Capture the status, reason, and affected presentations.

2) Search Drug Recall Enforcement Reports for any Class I or Class II recalls in the past seven days. Filter by recall_initiation_date in the last seven days and classification in (Class I, Class II).

3) Search Drug Adverse Events for new serious outcome reports in the past seven days. Filter by receivedate in the last seven days and serious outcome flags for death, hospitalization, or life-threatening events. Note the reaction terms and counts.

Then synthesize a single readable digest as the email body. Group items by therapeutic class. Lead with the highest-severity items (Class I recalls and death outcomes first, then Class II recalls and hospitalization outcomes, then shortage status changes, then everything else). Call out any shortage that changed status since last week (use the workflow's persistent memory or last-run state to compare). Flag any adverse event signal that looks unusually clustered (same reaction term spiking week over week, or multiple serious reports against the same drug in a short window) and explain why it caught the agent's attention.

Include a short coverage line at the top of the digest: 'Checked N drugs. M had new safety or supply signals.' For drugs with no signals, do not list them individually.

Send the finished digest with Gmail's Send a Message to the recipient email address configured on the workflow. The subject should be 'Formulary safety and supply digest — week of <Monday date>'. Send the body as readable HTML with clear class headings, severity labels, and links back to the relevant FDA records where useful.

On the first run, treat the previous week as having no recorded state and just publish the current snapshot so the agent has a baseline to compare against next week.

Additional information

What does this prompt do?

Reads your hospital formulary from a Google Sheet tab where each row lists brand name, generic name, therapeutic class, and the clinical lead's email.
For every drug, pulls the latest FDA shortage status, any Class I or Class II recalls from the past seven days, and serious adverse event reports from the past seven days.
Writes a single readable digest grouped by therapeutic class, with the highest-severity items at the top and a callout for shortage status changes since last week.
Flags any adverse event signal that looks unusually clustered so your team can investigate before it becomes a bigger problem.
Delivers the briefing as one Gmail message every Monday at 7am ET to the address you set on the workflow.

What do I need to use this?

A Google account with your formulary tracked in a Google Sheet. One row per drug with columns for brand name, generic name, therapeutic class, and the clinical lead's email.
A Gmail account the digest is sent from.
The email address or addresses you want the Monday briefing delivered to.

How can I customize it?

Change the delivery time, the day of the week, or the cadence. Some teams prefer a Friday afternoon recap instead of Monday morning.
Adjust the lookback window for recalls and adverse events. Seven days is the default, but two weeks or a full month works for less time-sensitive teams.
Route therapeutic-class sections to the clinical lead listed for that class in your formulary sheet, instead of sending one big email to a shared inbox.
Add a priority column to the sheet to mark high-watch drugs that always lead the digest, even when there is no new signal.

Frequently asked questions

Do I need an FDA account or API key to run this?

No. The FDA's public data feeds for shortages, recalls, and adverse events are free and require no signup, so the workflow can pull everything it needs without any extra credentials.

What happens for drugs with no shortages, recalls, or safety reports that week?

They are quietly omitted from the digest. A short tally at the top of the email tells you how many formulary drugs were checked and how many came back clean, so you know the coverage was complete.

Can I send the digest to more than one person?

Yes. You can add as many recipients as you want, and you can route therapeutic-class sections to the clinical leads listed in your formulary sheet so each lead only sees what is relevant to them.

How current is the FDA data behind the digest?

FDA shortage and recall data refresh weekly, and adverse event reports refresh roughly quarterly. The digest always pulls the latest available snapshot when it runs on Monday morning.

Does this work for outpatient pharmacies, P and T committees, or specialty clinics?

Yes. The formulary sheet is just a list of drugs you care about, so this works for hospital P and T committees, infusion centers, specialty pharmacies, and outpatient clinics.