Tell customers when their shipment arrives, before they ask

Every six hours on a cron trigger, send proactive arrival notices to the customers whose cargo is on the water, so my team stops fielding "where is my shipment" emails.

Start by reading my shipment tab in Google Sheets with Get Values. Each row is one booking and carries the vessel IMO number, the discharge port, the customer name, the customer email address, the booking reference, a notified flag, and the arrival date that was last communicated to that customer. Treat every row whose booking is still open as in scope. Skip rows that have been closed out, already delivered, or are missing an IMO number or a discharge port.

For each open shipment, call MarineTraffic Vessel ETA to Port using that row's vessel IMO and its discharge port to get the current predicted arrival. MarineTraffic returns times in UTC, so convert to a normal calendar day before you use it in anything customer facing.

Then decide, per booking, which of three buckets it falls into. If the vessel is now inside 72 hours of arrival and the row has not been notified yet, it belongs in a first arrival notice. If the row was already notified but the arrival has since moved by more than 12 hours in either direction compared with the date last communicated, it belongs in a revised ETA update instead. Everything else is left alone and nothing is sent.

Group by customer before sending anything. If one customer has three bookings that qualify in the same run, they receive a single email covering all three, never three separate notices. Send each email with Gmail Send a Message.

Write the emails in short, plain English, the way a person on the shipping desk would write them. Quote the booking reference and the expected berthing day in friendly form, for example "Thursday 14 August". Do not paste raw tracking fields into the message: no AIS jargon, no MMSI numbers, no IMO numbers, no speeds in knots, no coordinates, no raw UTC timestamps and no field names lifted from the data. A first notice should simply say the shipment is due, name the port, give the day, and offer a contact for questions. A revised update should say plainly that the arrival has shifted, give the new expected day, and avoid sounding alarmed when the change is routine.

After an email sends successfully, stamp the sheet back with Update Values: set the notified flag on every row that was included in that email, and write the arrival date you just communicated into the row. This write is the idempotency guard and it is load bearing, because the workflow reruns against the same sheet every six hours and without the stamp it would re-email the same customer forever. Only stamp rows that were actually included in an email that sent successfully. If a send fails, leave that row untouched so the next run retries it rather than silently skipping the customer. For revised updates, overwrite the stored arrival date with the new figure so the next 12 hour comparison is made against what the customer was most recently told.