Cellar Analytics

Real-time statistics and insights about your wine cellar

🔄 Connected to Database
🍾
- Active Bottles
🍇
- Varieties
🏰
- Wineries
⏳
- Oldest Vintage
Wine Varieties Distribution
Wineries Distribution (Top Wineries)
Vintage Distribution
Recently Consumed Log
No recently consumed wines yet. Mark a bottle consumed to start logging!
Wines by Rack Location

Cellar Navigator

Locate wines in your drawers/racks, add new entries, or mark drank bottles

🔍
Inventory Results (0 bottles)
Vintage Wine Details Location (AVA) Rack Position Actions
Use search or login to view inventory.
Cellar Grid Locator
Empty Slot
Occupied Slot
Selected Bottle
Click a bottle in the table or click any grid cell to explore details.

Google Sheet Synchronizer

Integrate your website database with your Google Sheets document

Database Sync Control

By default, the website pulls cellar data from your public Google Sheet CSV URL. If you configure a **Google Apps Script Web App**, changes made on this website (adding wines, marking as consumed) will immediately sync back to your Google Sheet!

SYNC CONFIGURATION STATUS
📥 **Read URL (Pull)**: Configured
📤 **Apps Script (Push)**: Checking...
Last Sync Result
No sync logs available. Run sync to generate logs.
Google Apps Script Setup Guide

To allow the dashboard to write additions and consumption status directly back to your Google Sheet:

  1. Open your Google Sheet at mycellarsearch.com
  2. Click Extensions > Apps Script.
  3. Delete any existing code, copy the code snippet below, and paste it.
  4. Click Deploy > New deployment.
  5. Select type Web App (gear icon).
  6. Set Execute as: Me and Who has access: Anyone.
  7. Click Deploy, authorize permissions, and copy the **Web App URL**.
  8. Paste this URL in your .env file under GOOGLE_SCRIPT_URL= and restart your server.
function doPost(e) {
  var sheetName = "Wine Cellar";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  if (!sheet) {
    return ContentService.createTextOutput(JSON.stringify({
      status: "error",
      message: "Sheet '" + sheetName + "' not found"
    })).setMimeType(ContentService.MimeType.JSON);
  }

  var data = JSON.parse(e.postData.contents);
  var action = data.action;
  
  if (action === "add") {
    // Append wine row
    // Vintage, Variety, Winery, Location, Rack, Column, Row
    sheet.appendRow([
      data.vintage,
      data.variety,
      data.winery,
      data.location,
      data.rack,
      data.col,
      data.row,
      "" // Empty consumed column
    ]);
    return ContentService.createTextOutput(JSON.stringify({status: "success", message: "Wine added"})).setMimeType(ContentService.MimeType.JSON);
  } 
  
  if (action === "consume") {
    // Find matching wine and mark X in Col H (index 8, column 8)
    var rows = sheet.getDataRange().getValues();
    var foundIdx = -1;
    
    // We scan from rows 17 downwards (row index 16 is 0-indexed index 16)
    for (var i = 16; i < rows.length; i++) {
      var r = rows[i];
      var matches = 
        String(r[0]).trim() == String(data.vintage).trim() &&
        String(r[1]).trim().toLowerCase() == String(data.variety).trim().toLowerCase() &&
        String(r[2]).trim().toLowerCase() == String(data.winery).trim().toLowerCase() &&
        String(r[4]).trim().toLowerCase() == String(data.rack).trim().toLowerCase() &&
        String(r[5]).trim() == String(data.col || "").trim() &&
        String(r[6]).trim() == String(data.row || "").trim() &&
        String(r[7]).trim().toUpperCase() != "X"; // Not already consumed
        
      if (matches) {
        foundIdx = i + 1; // 1-indexed row number
        break;
      }
    }
    
    if (foundIdx !== -1) {
      sheet.getRange(foundIdx, 8).setValue("X"); // Column H
      return ContentService.createTextOutput(JSON.stringify({status: "success", message: "Wine marked consumed"})).setMimeType(ContentService.MimeType.JSON);
    } else {
      return ContentService.createTextOutput(JSON.stringify({status: "error", message: "Matching active wine not found in Google Sheet"})).setMimeType(ContentService.MimeType.JSON);
    }
  }

  return ContentService.createTextOutput(JSON.stringify({status: "error", message: "Invalid action"})).setMimeType(ContentService.MimeType.JSON);
}