← All posts · 2026-08-08 · Sophia Excel & Automation

Google Sheets daily inventory report email automation

Introduction – cutting the morning spreadsheet ritual

Most people looking for a way to automate a daily inventory report in Google Sheets share the same problem. Even when yesterday's stock was closed out properly, someone still has to rebuild the previous day's inbound and outbound picture every morning and email it to the team and to management. The person doing it also has a warehouse to run, and copying, filtering and sorting the same report shape day after day eats a real chunk of the morning.

This post covers how to hand that job to Google Sheets and Apps Script email automation, written from actual operating practice. We will build a previous-day inbound/outbound summary, a list of pending receipts and a near-full location list as an HTML table, then send it at the same time every day with a time-driven Apps Script trigger. The goal is a setup you can put into production by adjusting only the REPORT_CONFIG block to your own sheet structure, running a test, and registering the trigger.

If you already followed Build a Warehouse Inventory System in Google Sheets with Apps Script and How to Auto-Aggregate Inventory in Google Sheets | Apps Script Part 3 to record movements and aggregate stock, treat this post as the step that turns those results into "a daily report that simply shows up every morning."

Why a daily inventory report deserves automation

The first thing automation removes is repetitive work and the mistakes that come with it. When a person changes the date filter by hand every morning, rebuilds a pivot and copies the result into an email, it is easy to be off by one day or to leave a status out of the filter. Reporting on today's data when the report is supposed to cover yesterday is another common slip.

Define the logic once in Apps Script and the reference date and the aggregation method stay identical forever. Bake in a rule such as "at 7 a.m. today, aggregate every movement from 00:00 to 24:00 yesterday," and the script keeps calculating the same way through weekends and holidays. Staff can rotate or take vacation; as long as the Google Sheets trigger stays in place, customers and internal teams receive the same report in the same format at the same hour.

Standardizing the email body as an HTML table also keeps it readable on phones and in webmail. A morning briefing usually revolves around three things: how many receipts and shipments happened yesterday, which receipts are still open, and which locations are close to full. Fit exactly that on one screen and a manager can read the subject line and the first fold and immediately know the day's workload and likely bottlenecks.

Sheet structure and assumptions behind the previous-day aggregation

Before writing any Apps Script, be explicit about which sheets you read and what is in them. The explanation below assumes the following layout. Sheet names and column headers differ from site to site, so adjust the REPORT_CONFIG block and the header names at the top of the code to match your own structure.

• IO_LOG sheet: one row per movement, with Date, Type (Inbound/Outbound), Item, Qty and Status (Done/Pending). The date column must actually be stored as a date, not as text.
• LOCATION_STATUS sheet: location name plus usage as a value between 0 and 1 (0.87 means 87%). Locations above the saturation threshold (0.9 and up, for example) get called out separately in the report.

The code in this post fixes "reference date = the day before the run." If the trigger fires at 7 a.m. on Monday, the code aggregates Sunday's movements. To do that it computes today - 1 day as the reference date and prints that same date in both the HTML and the email subject.

The second assumption is the range of the usage value. The Usage column on the LOCATION_STATUS sheet is assumed to hold a decimal between 0 and 1. If you already record usage as a percentage (95, for example), you have to change the percentage conversion in the code. Spelling assumptions like these out in a comment and in the config values helps whoever maintains the script later.

Building the daily summary HTML – the buildDailySummary function

This code reads yesterday's receipt and shipment counts, the pending receipts and the near-full locations, and turns them into an HTML summary table.

Where to paste: Google Sheets → Extensions → Apps Script → at the very bottom of Code.gs.

What to do after pasting: save (Ctrl+S or ⌘S), then later run testBuildDailySummary once and check the log.

// → change only this block to match your environment
const REPORT_CONFIG = {                                            // → configuration bundle
  SHEET_LOG: 'IO_LOG',                                      // → movement log sheet name
  SHEET_LOC: 'LOCATION_STATUS',                             // → location status sheet name
  REPORT_RECIPIENTS: '[email protected],[email protected]', // → recipients (comma separated)
  REPORT_SENDER_NAME: 'Warehouse Daily Inventory Report',   // → sender display name
  LOCATION_FULL_THRESHOLD: 0.9,                             // → saturation level (90%, 0-1 scale)
  TIMEZONE: 'America/New_York'                              // → set your warehouse time zone
};

// → builds the previous-day summary as HTML
function buildDailySummary() {                              // → start building the summary
  const tz = REPORT_CONFIG.TIMEZONE;                               // → time zone
  const now = new Date();                                   // → current time
  const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000); // → 24 hours earlier
  const ymd = Utilities.formatDate(yesterday, tz, 'yyyy-MM-dd'); // → formatted reference date

  const ss = SpreadsheetApp.getActiveSpreadsheet();         // → current spreadsheet
  const logSheet = ss.getSheetByName(REPORT_CONFIG.SHEET_LOG);     // → movement log sheet
  const locSheet = ss.getSheetByName(REPORT_CONFIG.SHEET_LOC);     // → location status sheet

  if (!logSheet) {                                          // → sheet missing
    throw new Error('IO_LOG sheet not found.');             // → error message
  }
  if (!locSheet) {                                          // → sheet missing
    throw new Error('LOCATION_STATUS sheet not found.');    // → error message
  }

  const logData = logSheet.getDataRange().getValues();      // → all movement data
  const logHeader = logData[0];                             // → header row
  const logRows = logData.slice(1);                         // → data rows

  const idxDate = logHeader.indexOf('Date');                // → date column position
  const idxType = logHeader.indexOf('Type');                // → inbound/outbound column
  const idxStatus = logHeader.indexOf('Status');            // → status column
  const idxItem = logHeader.indexOf('Item');                // → item column (used by the table)
  const idxQty = logHeader.indexOf('Qty');                  // → quantity column (used by the table)

  if (idxDate === -1 || idxType === -1 || idxStatus === -1 ||  // → required columns check
      idxItem === -1 || idxQty === -1) {                       // → including the table columns
    throw new Error('IO_LOG needs Date / Type / Status / Item / Qty columns.'); // → guidance
  }

  let inCount = 0;                                          // → yesterday's receipts
  let outCount = 0;                                         // → yesterday's shipments
  let pendingReceipts = [];                                 // → pending receipt list

  logRows.forEach(row => {                                  // → walk every row
    const rowDate = row[idxDate];                           // → date value
    if (!(rowDate instanceof Date)) {                       // → is it a real date
      return;                                               // → if not, skip
    }
    const rowYmd = Utilities.formatDate(rowDate, tz, 'yyyy-MM-dd'); // → yyyy-mm-dd
    if (rowYmd !== ymd) {                                   // → not yesterday
      return;                                               // → skip
    }
    const type = row[idxType];                              // → type value
    const status = row[idxStatus];                          // → status value

    if (type === 'Inbound') {                               // → inbound row
      inCount++;                                            // → count it
      if (status === 'Pending') {                           // → still open
        pendingReceipts.push({                              // → add to the list
          item: row[idxItem],                              // → item
          qty: row[idxQty],                                // → quantity
          status: status                                   // → status
        });
      }
    } else if (type === 'Outbound') {                       // → outbound row
      outCount++;                                           // → count it
    }
  });

  const locData = locSheet.getDataRange().getValues();      // → all location data
  const locHeader = locData[0];                             // → location header
  const locRows = locData.slice(1);                         // → location rows
  const idxLocName = locHeader.indexOf('Location');        // → location name column
  const idxUsage = locHeader.indexOf('Usage');             // → usage column

  let fullLocations = [];                                   // → near-full locations

  if (idxLocName > -1 && idxUsage > -1) {                   // → both columns present
    locRows.forEach(row => {                                // → walk every location
      const usage = row[idxUsage];                          // → usage value (0-1 assumed)
      if (typeof usage === 'number' &&                      // → numeric and
          usage >= REPORT_CONFIG.LOCATION_FULL_THRESHOLD) {        // → at or above threshold
        fullLocations.push({                                // → add to the list
          name: row[idxLocName],                            // → location name
          usage: usage                                      // → usage
        });
      }
    });
  }

  let html = '';                                            // → HTML accumulator

  html += '<h2>Daily inventory summary</h2>';               // → heading
  html += '<p>Reference date: ' + ymd + '</p>';             // → reference date

  html += '<h3>Movement counts</h3>';                       // → sub heading
  html += '<table border="1" cellspacing="0" cellpadding="4">'; // → table start
  html += '<tr><th>Type</th><th>Count</th></tr>';           // → header row
  html += '<tr><td>Inbound</td><td>' + inCount + '</td></tr>'; // → inbound row
  html += '<tr><td>Outbound</td><td>' + outCount + '</td></tr>'; // → outbound row
  html += '</table>';                                       // → table end

  html += '<h3>Pending receipts</h3>';                      // → sub heading
  if (pendingReceipts.length === 0) {                       // → nothing pending
    html += '<p>No pending receipts.</p>';                  // → message
  } else {
    html += '<table border="1" cellspacing="0" cellpadding="4">'; // → table start
    html += '<tr><th>Item</th><th>Qty</th><th>Status</th></tr>';  // → header row
    pendingReceipts.forEach(r => {                          // → each pending row
      html += '<tr>';                                       // → row start
      html += '<td>' + r.item + '</td>';                    // → item
      html += '<td>' + r.qty + '</td>';                     // → quantity
      html += '<td>' + r.status + '</td>';                  // → status
      html += '</tr>';                                      // → row end
    });
    html += '</table>';                                     // → table end
  }

  html += '<h3>Near-full locations</h3>';                   // → sub heading
  if (fullLocations.length === 0) {                         // → none over threshold
    html += '<p>No location is above the saturation threshold.</p>'; // → message
  } else {
    html += '<table border="1" cellspacing="0" cellpadding="4">'; // → table start
    html += '<tr><th>Location</th><th>Usage</th></tr>';     // → header row
    fullLocations.forEach(l => {                            // → each location row
      const pct = Math.round(l.usage * 100);                // → convert to percent
      html += '<tr>';                                       // → row start
      html += '<td>' + l.name + '</td>';                    // → location name
      html += '<td>' + pct + '%</td>';                      // → usage
      html += '</tr>';                                      // → row end
    });
    html += '</table>';                                     // → table end
  }

  return html;                                              // → return the HTML
}

// → check the buildDailySummary output from the editor
function testBuildDailySummary() {                          // → for testing
  const html = buildDailySummary();                         // → build the summary
  Logger.log(html);                                         // → print to the log
}

How to confirm it works: run testBuildDailySummary in the Apps Script editor and check that the execution log prints an HTML string containing the reference date and the table tags.

Sending the report with MailApp.sendEmail – the sendDailyReport function

This code takes the HTML summary you just built and sends it out through Apps Script's MailApp.sendEmail.

Where to paste: directly underneath the buildDailySummary function above.

What to do after pasting: save, run testSendDailyReport once, and check that the email arrives in your inbox.

// → emails the daily inventory report
function sendDailyReport() {                                // → start sending
  const tz = REPORT_CONFIG.TIMEZONE;                               // → time zone
  const now = new Date();                                   // → current time
  const yesterday = new Date(now.getTime() - 24 * 60 * 60 * 1000); // → 24 hours earlier
  const subjectDate = Utilities.formatDate(yesterday, tz, 'yyyy-MM-dd'); // → date for the subject
  const subject = '[Warehouse] Daily inventory report - ' + subjectDate; // → email subject

  const htmlBody = buildDailySummary();                     // → build the report HTML

  const recipients = REPORT_CONFIG.REPORT_RECIPIENTS               // → recipient list
    .split(',')                                             // → split on commas
    .map(s => s.trim())                                     // → trim spaces
    .filter(s => s);                                        // → drop empties

  if (recipients.length === 0) {                            // → nobody to send to
    throw new Error('Add at least one address to REPORT_RECIPIENTS.'); // → guidance
  }

  const options = {                                         // → mail options
    name: REPORT_CONFIG.REPORT_SENDER_NAME,                        // → sender display name
    htmlBody: htmlBody                                      // → HTML body
  };

  recipients.forEach(to => {                                // → for each recipient
    MailApp.sendEmail(to, subject, 'This message requires an HTML-capable mail client.', options); // → send
  });
}

// → run the send manually for testing
function testSendDailyReport() {                            // → for testing
  sendDailyReport();                                        // → execute the send
}

How to confirm it works: run testSendDailyReport and check the inbox of the address you put in REPORT_CONFIG for an email titled "[Warehouse] Daily inventory report - <yesterday's date>".

Sending at the same time every day – setting up a time-driven trigger

Now let's remove the need for anyone to press Run. The code below creates a time-driven Apps Script trigger that fires sendDailyReport every morning. One thing worth knowing up front: atHour(7) does not mean exactly 7:00. Apps Script time-driven triggers run somewhere inside the hour you specify, so in practice it fires between 7 and 8 (nearMinute() is likewise a ±15 minute window around the minute you give it). If your process needs minute-level precision, a trigger alone is not enough — print the actual run time inside the report itself.

Where to paste: anywhere below the previous functions.

What to do after pasting: run createDailyTrigger exactly once.

// → creates the trigger that runs sendDailyReport every day
function createDailyTrigger() {                             // → start creating the trigger
  const functionName = 'sendDailyReport';                   // → target function name

  const triggers = ScriptApp.getProjectTriggers();          // → existing triggers
  triggers.forEach(t => {                                   // → inspect each one
    if (t.getHandlerFunction() === functionName) {          // → same function
      ScriptApp.deleteTrigger(t);                           // → delete, then recreate
    }
  });

  ScriptApp.newTrigger(functionName)                        // → create a new trigger
    .timeBased()                                            // → time driven
    .atHour(7)                                              // → the 7 a.m. hour (7-8)
    .everyDays(1)                                           // → every day
    .inTimezone(REPORT_CONFIG.TIMEZONE)                            // → in the configured zone
    .create();                                              // → done
}

How to confirm it works: open the Triggers panel on the left of the Apps Script editor and check that sendDailyReport is registered to run daily in the 7 a.m. hour.

Install and test order, plus trigger tips

Rather than going straight to production, work through four stages. First, add the REPORT_CONFIG block at the top of your existing code in the Apps Script editor and change the sheet names, TIMEZONE and REPORT_RECIPIENTS to match your environment. For the initial test, put only your own address in the recipient list; add the team mailing list or multiple recipients after it is verified.

Second, run testBuildDailySummary and confirm the previous-day HTML summary is built correctly. Review the reference date, the movement counts and the pending-receipt table in the execution log. If yesterday clearly has data but the counts come back as zero, the most likely causes are a date column stored as text or headers that differ from the names the code looks for (Date / Type / Status).

Third, run testSendDailyReport to confirm the Apps Script email automation works. Corporate accounts sometimes restrict outbound mail by policy, so start with addresses inside your own domain and also check that the message is not filed as spam. This is the plain MailApp.sendEmail pattern, so once authorization is granted it normally works without further tuning.

Finally, run createDailyTrigger to create the time-driven trigger. If you want to watch it fire, open the Triggers panel, set the run time five to ten minutes from now, confirm the email actually arrives, then move it back to your operating hour (7 a.m., for example). That one extra step avoids the classic "the schedule is set but no mail ever comes" situation.

Field notes – fix the structure, push settings into REPORT_CONFIG

Run inventory automation in Google Sheets for a while and you notice that sheet structure and rules matter more than the code. The example in this post finds column positions by header name — indexOf('Date'), indexOf('Type') and so on. That approach survives inserting a column in the middle, but it breaks the moment somebody renames a header. So it is worth agreeing a rule with the team: "Do not change the column headers on the IO_LOG sheet. If you need new information, add a new column at the far right."

The other habit is collecting operating parameters such as thresholds and reference dates into the REPORT_CONFIG object at the top of the file. Put the saturation threshold (LOCATION_FULL_THRESHOLD), the report time zone (TIMEZONE) and the recipient list (REPORT_RECIPIENTS) in one object and copying the script to another warehouse or another customer only means editing REPORT_CONFIG. If you want to go further, read the REPORT_CONFIG values from a "Settings" tab in the sheet so operators can adjust thresholds without touching code.

When you run several time-driven Apps Script triggers, review the trigger list on the account from time to time. Duplicate triggers on the same function send the same report twice. The createDailyTrigger in this post prevents that by deleting every existing trigger for that function before creating a new one.

Common errors and how to fix them

A few patterns show up again and again once email automation and triggers are deployed for real. The first is authorization. The first time you run testSendDailyReport or createDailyTrigger you may see a warning that says "This app isn't verified." That is the normal warning for an Apps Script project you built in-house: click Advanced → "Go to (project name) (unsafe)" and, after approving once, the same account is not asked again.

The second is a header mismatch. If your date column is actually titled "Movement Date" while the code looks for "Date", then idxDate === -1 and you get the "needs Date / Type / Status / Item / Qty columns" error. All five are required on purpose: without Item and Qty the pending-receipts table renders as empty cells, which reads as "the script ran but the report is wrong." Failing loudly at the start is kinder than degrading silently. Either rename the sheet header to the name the code uses, or change the indexOf('Date') call to the real header. Once the structure lines up, an internal agreement to avoid renaming column headers is what keeps it stable.

Finally, Apps Script email sending has a daily quota. One or two daily inventory reports rarely come close to it, but if the same account also runs several other sending scripts, check the per-account limits in the Google Workspace admin console or the official documentation first. Setting a failure-notification address on the trigger helps you notice a problem early instead of a day later.

Wrapping up – try automating yesterday's inventory report today

A previous-day inventory report is hard to remove from warehouse operations, but because its content and format barely change from day to day it is an unusually good fit for automation. With Google Sheets and a time-driven Apps Script trigger, the reference-date calculation, the aggregation, the HTML conversion and the email all run from logic you define once, and the person who used to assemble it can spend that time on data accuracy and the floor instead.

The first step is small. Open your inventory spreadsheet and compare the column headers on the IO_LOG sheet with the names used here (Date, Type, Status, Item, Qty), then paste the REPORT_CONFIG block and the buildDailySummary, sendDailyReport and createDailyTrigger functions into the Apps Script editor in that order. Run testBuildDailySummary and testSendDailyReport to verify the results, and once they look right, register the daily trigger and watch yesterday's inventory report arrive in your inbox by itself the next morning.