See llms.txt for all machine-readable content.

Back to Templates

Predict port demurrage risk with Google Sheets, Gemini Flash and Slack

Quick overview

Youtube Video: https://youtu.be/a10IgTQQUSs

This workflow runs every three hours to read shipment data from Google Sheets, pulls live port-call, AIS, weather, and news signals, and uses Google Gemini to predict port congestion and demurrage risk per container, logging results back to Google Sheets and sending Slack and Gmail alerts.

How it works

  1. Runs on a schedule every three hours (or manually) and reads shipment rows from a Google Sheets “Shipments” sheet.
  2. Filters to active shipments with a container number that are not delivered and are within 10 days of their free-time deadline, then deduplicates them by discharge port (UN/LOCODE).
  3. For each distinct port, fetches upcoming port calls and vessels at anchor from the Digitraffic APIs and compares the current anchored-vessel count to the previous run stored in Redis.
  4. Fetches local marine and wind forecasts from Open-Meteo and recent disruption headlines from Google News RSS for the same port.
  5. Retrieves all shipments for that port from Google Sheets and packages shipment details and live signals into a prompt for Google Gemini, enforcing a structured JSON response with demurrage risk metrics and recommended actions.
  6. Enriches each shipment prediction with customer contacts and estimated demurrage cost, appends the results to a Google Sheets “Prediction_Log” sheet, and sends Slack alerts for HIGH/CRITICAL risks (and emails the client for CRITICAL cases).

Setup

  1. Create a Google Sheets service account credential in n8n with access to the spreadsheet, and ensure the “Shipments” and “Prediction_Log” sheets exist in the provided document ID.
  2. Set up a Redis credential and make sure the Redis instance is reachable so the workflow can store and retrieve anchored-vessel counts per port.
  3. Add a Google Gemini (PaLM) API credential for the Gemini chat model used by the agent.
  4. Add Slack credentials and update the target channel ID(s) for the HIGH/CRITICAL alert messages.
  5. Add a Gmail OAuth2 credential and ensure each shipment row contains valid client_name and client_email values for CRITICAL email notifications.