Track menu cost ratios from delivery-note photos with Gemini, Google Sheets and Gmail

Quick Overview

This workflow reads supplier delivery-note photos from Google Drive with Google Gemini to keep ingredient prices up to date in Google Sheets, then recalculates menu item cost ratios every morning and emails you only the dishes that exceed their target ratios via Gmail.

How it works

1. Triggers either at 8:00 daily on a schedule or when a new delivery-note photo is created in a specific Google Drive folder.
2. For new photos, downloads the image from Google Drive and uses Google Gemini to extract invoice fields and line items as JSON.
3. Converts extracted quantities into base units (per gram/ml/piece), checks that line totals reconcile to the printed subtotal/total, and either updates the Ingredients tab in Google Sheets or emails you to manually verify the document.
4. For the daily run, reads the Ingredients, Recipes, and Menu tabs from Google Sheets.
5. Calculates each dish’s theoretical cost, current cost ratio, and over/OK status from the sheet data, then writes the latest results back to the Menu tab.
6. If any dishes are over target, sends the computed facts to Google Gemini to generate concise recommended actions per dish.
7. Emails a single alert through Gmail containing the over-target dishes and the AI-written actions, otherwise sends nothing.

Setup

1. Create a Google Sheets spreadsheet with the required tabs and columns (Ingredients, Recipes, Menu) and set the spreadsheet ID in the Config values.
2. Create a Google Drive folder for delivery-note photos and paste its folder ID into the Drive trigger configuration.
3. Add credentials for Google Sheets, Google Drive, Google Gemini (PaLM API), and Gmail, and set the notification email address in the Config values.
4. Ensure recipe ingredient names match the Ingredients tab exactly so cost calculations don’t skip rows.
5. Run the workflow once manually to confirm sheet access and email delivery before relying on the daily schedule and photo uploads.