Dynamic E-Commerce Forecasting with External APIs

Welcome back to the MageSheet blog!

Inventory forecasting in e-commerce is notoriously difficult. Most businesses look at backward-facing data: "We sold 500 umbrellas last October, so let's order 550 for this October."

But the world isn't static. What if last October was unusually rainy, and this October is projected to be dry? By relying strictly on historical Magento sales data, your purchasing department might accidentally overstock 500 umbrellas that will sit in a warehouse collecting dust.

In B2B e-commerce, margins are won and lost on inventory turnover. Today, we are going to build a Dynamic Demand Forecaster using Google Apps Script. Instead of just looking at past sales, we will connect our Google Sheet to external, real-time data sources—specifically, Weather APIs and National Holiday Calendars.

The Theory: Correlative Forecasting

If you sell auto parts, snow chains sell when it snows. If you sell restaurant supplies, bulk paper plates sell out right before national holidays.

Our goal is to build an Apps Script that runs every night, looks 14 days into the future, and generates a "Demand Alert" in our Google Sheet if external factors indicate a spike in a specific category.

Step 1: Connecting to a Weather API

There are many free weather APIs, such as OpenWeatherMap. Let's write a script that fetches the 7-day forecast for your primary shipping regions.

// Forecast.gs

const WEATHER_API_KEY = "your_openweathermap_api_key";
const CITY = "Chicago"; // Adjust to your main demographic

function fetchFutureWeather() {
  const url = `https://api.openweathermap.org/data/2.5/forecast/daily?q=${CITY}&cnt=7&appid=${WEATHER_API_KEY}&units=imperial`;
  
  try {
    const response = UrlFetchApp.fetch(url);
    const data = JSON.parse(response.getContentText());
    
    // We will store this in a hidden "External Data" tab
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Weather Data");
    sheet.clearContents(); // Clear old forecast
    sheet.appendRow(["Date", "Expected Weather", "Temp"]);
    
    data.list.forEach(day => {
      const date = new Date(day.dt * 1000).toLocaleDateString();
      const condition = day.weather[0].main; // e.g., "Rain", "Clear", "Snow"
      const temp = day.temp.day;
      
      sheet.appendRow([date, condition, temp]);
      
      // Analyze the condition right now!
      analyzeDemand(condition, date);
    });
    
  } catch(e) {
    Logger.log("Weather API failed.");
  }
}

Step 2: Correlating Weather to SKUs

Now we need a function that looks at that weather forecast and triggers alerts for our purchasing team. Let's write the analyzeDemand function.

function analyzeDemand(weatherCondition, dateString) {
  const alertSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Purchasing Alerts");
  
  if (weatherCondition === "Snow" || weatherCondition === "Extreme Cold") {
    alertSheet.appendRow([
      new Date(), 
      dateString, 
      "Anticipated Snow", 
      "SKU-SNW-CHN (Snow Chains)", 
      "Increase buffer stock by 20% immediately."
    ]);
  }
  
  if (weatherCondition === "Rain") {
    alertSheet.appendRow([
      new Date(), 
      dateString, 
      "Anticipated Rain", 
      "SKU-UMB-BLK (Black Umbrellas)", 
      "Check inventory levels in regional warehouses."
    ]);
  }
}

Step 3: Fetching Google Calendar Events (Holidays)

Weather isn't the only external factor. Let's use Google's native Calendar service (CalendarApp) to read upcoming national holidays or localized events.

function checkUpcomingHolidays() {
  // Use Google's public US Holidays calendar ID
  const holidayCalendar = CalendarApp.getCalendarById("en.usa#holiday@group.v.calendar.google.com");
  
  const today = new Date();
  const threeWeeksFromNow = new Date();
  threeWeeksFromNow.setDate(today.getDate() + 21);
  
  // Get all holiday events in the next 3 weeks
  const events = holidayCalendar.getEvents(today, threeWeeksFromNow);
  
  const alertSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Purchasing Alerts");
  
  events.forEach(event => {
    const holidayName = event.getTitle();
    
    if (holidayName.includes("Thanksgiving") || holidayName.includes("Labor Day")) {
      alertSheet.appendRow([
        new Date(), 
        event.getStartTime().toLocaleDateString(), 
        `Upcoming: ${holidayName}`, 
        "Category: Bulk Paper Goods", 
        "Major B2B restaurant supply ordering anticipated next week. Lock in supplier pricing."
      ]);
    }
  });
}

The Power of the "Central Nervous System"

By running these scripts via Time-Driven Triggers (Extensions > Apps Script > Triggers), your Google Sheet ceases to be a static record of the past and becomes a dynamic forecasting engine.

When your purchasing manager logs in on Monday morning, they aren't just looking at what sold last year; they are looking at a dashboard that says: "Alert: Snow storm hitting Chicago in 5 days, Thanksgiving is in 18 days. Adjust these 4 SKUs immediately."

You can pipe this exact data back into your Magento store. For instance, if the Apps Script detects rain, it can fire an API call to Magento to temporarily move "Rain Gear" to the top of your homepage categories.

This is true digital transformation. If you want to connect your Adobe Commerce store to the real world using Google Workspace automation, contact the experts at MageSheet today.