See llms.txt for all machine-readable content.

Back to Templates

Track grant deadlines and spend down with OpenAI, Google Sheets, Gmail and Telegram

Created by

Created by: Khairul Muhtadin || khmuhtadin
Khairul Muhtadin

Last update

Last update 7 hours ago

Categories

Share

Quick Overview

This workflow tracks grant awards and drawdowns in Google Sheets, uses OpenAI to generate reporting calendars and reminder drafts, and sends updates via Gmail and Telegram, with scheduled weekly deadline reminders and a monthly portfolio review for high-burn or near spend-by awards.

How it works

  1. Receives a new grant award via a POST webhook and normalizes the payload into consistent fields like funder, program, amount, term, reporting cadence, and key contacts.
  2. Validates that required award fields are present, then uses OpenAI to convert the award terms into a JSON reporting calendar with milestones, due dates, buffer days, and a compliance risk note.
  3. Appends the award to the Google Sheets “Awards” tab, writes each milestone as a row in the “Deadlines” tab, emails the calendar to the grant lead via Gmail, and posts a new-award alert to Telegram.
  4. If an award payload is invalid, emails an admin via Gmail, alerts the team in Telegram, and logs the rejection to the Google Sheets “Errors” tab.
  5. Receives a grant spend entry via a POST webhook, validates it, looks up the matching award in Google Sheets, logs the spend in “SpendLog,” and updates the award’s running spend total in “Awards.”
  6. When burn is at least 80% or the spend-by date is within 45 days, uses OpenAI to draft a short finance note, emails it to the grant lead via Gmail, and posts an alert to Telegram.
  7. Runs every Monday morning to find pending deadlines due within 30 days, uses OpenAI to draft reminder emails with a data checklist, emails the grant lead, alerts Telegram, logs the reminder in “ReminderLog,” and marks the deadline as “reminder sent” in Google Sheets.
  8. Runs on the first of each month to review the full awards portfolio for items needing attention (burn ≥80% or spend-by within 45 days), then uses OpenAI to create a digest and emails it to the finance lead via Gmail.

Setup

  1. Create a Google Sheets document with tabs named Awards, Deadlines, SpendLog, ReminderLog, and Errors, and update the workflow’s Google Sheets document ID if you use a different file.
  2. Add credentials for OpenAI, Google Sheets, Gmail, and Telegram, and update the Telegram chat ID and admin/finance email addresses used for alerts.
  3. Copy the two webhook URLs (grant-award and grant-spend) and configure your award intake source and finance/spend system to POST the expected fields to them.
  4. Ensure the Awards tab includes an award_id column (used for matching) and a spent_to_date column (used for burn calculations).
  5. Adjust the alert thresholds (80% burn, 45 days to spend-by, 30-day reminder window) in the workflow conditions to match your compliance policy.