Google Apps Script

Set Up a Google Sheets‑Backed Order Management Web App with Apps Script

A step‑by‑step guide for small‑business owners to turn a Google Sheet into a live order‑tracking web app using Google Apps Script, including deployment, triggers, permissions and a ready‑to‑use checklist.

You're ready to start selling online, but you don't have a full‑blown ERP system yet. The immediate problem is: how can I capture orders, track inventory and record manual payments without paying for a separate SaaS tool? The quickest answer is to turn a Google Sheet into a live order‑management web app using Google Apps Script.

What the script will do

Once deployed, the web app provides a simple form that any customer (or internal staff) can fill to create an order. The data is written to a master sheet, inventory is automatically reduced, and a confirmation email is sent. A second sheet keeps a log of manual payments so you can reconcile later.

Prerequisites

  1. Google account with access to Google Drive and Google Sheets.
  2. A new spreadsheet that will hold the data. We recommend three tabs: Orders, Products, and Payments.
  3. Basic familiarity with Google Sheets formulas (e.g., =SUM()) – no coding experience required.
  4. Optional: a Gmail address you control for sending order confirmations.

Step‑by‑step setup

1. Build the data tables

In the Products sheet list each SKU, product name, price and current stock. Example layout:

  • Column A – SKU (e.g., SKU001)
  • Column B – Product name
  • Column C – Unit price
  • Column D – Stock on hand

In the Orders sheet add headers for Timestamp, Order ID, SKU, Quantity, Total, Customer Email, Status. The Payments sheet will record Order ID, Amount Received, Payment Method and Date.

2. Open the Apps Script editor

From the spreadsheet choose Extensions → Apps Script. Delete any starter code and create a new file called Code.gs. Paste the following skeleton (comments explain each block):

Sample code: function doGet(e) { return HtmlService.createHtmlOutputFromFile('orderForm'); } function submitOrder(data) { var ss = SpreadsheetApp.getActiveSpreadsheet(); var orders = ss.getSheetByName('Orders'); var products = ss.getSheetByName('Products'); // 1️⃣ Find product row var prodRange = products.getRange('A2:A' + products.getLastRow()); var prodValues = prodRange.getValues(); var rowIndex = -1; for (var i = 0; i < prodValues.length; i++) { if (prodValues[i][0] === data.sku) { rowIndex = i + 2; break; } } if (rowIndex === -1) return {error: 'SKU not found'}; // 2️⃣ Check stock var stockCell = products.getRange('D' + rowIndex); var currentStock = stockCell.getValue(); if (data.qty > currentStock) return {error: 'Insufficient stock'}; // 3️⃣ Record order var newId = 'ORD' + new Date().getTime(); var total = data.qty * products.getRange('C' + rowIndex).getValue(); orders.appendRow([new Date(), newId, data.sku, data.qty, total, data.email, 'Pending']); // 4️⃣ Reduce stock stockCell.setValue(currentStock - data.qty); // 5️⃣ Send confirmation (optional) if (data.email) { MailApp.sendEmail(data.email, 'Order Confirmation', 'Your order ' + newId + ' for $' + total + ' has been received.'); } return {orderId: newId, total: total}; }

This code creates a public endpoint (doGet) that serves an HTML form (we will add next) and a submitOrder function that validates stock, writes the order, updates inventory and optionally emails the buyer.

3. Add the order form HTML

In the script editor choose File → New → HTML file and name it orderForm. Paste a minimal form:

Sample HTML:

Place an Order

SKU:
Quantity:
Email:
Submit function submit(){ var f=document.getElementById('orderForm'); google.script.run.withSuccessHandler(function(r){ if(r.error){document.getElementById('msg').innerText=r.error;} else{document.getElementById('msg').innerText='Order '+r.orderId+' recorded. Total $'+r.total;} }).submitOrder({sku:f.sku.value, qty:parseInt(f.qty.value), email:f.email.value}); }

The form runs entirely in the user's browser, then calls google.script.run to execute submitOrder on the server side.

4. Set up permissions and deployment

  1. Click Deploy → New deployment.
  2. Select Web app as the type.
  3. Give the deployment a name (e.g., "Order Management App").
  4. Execute the app as Me (your email) – this lets the script write to the spreadsheet and send emails.
  5. Set Who has access to Anyone (or Anyone with the link if you prefer a semi‑private form).
  6. Click **Deploy**. Google will ask you to review permissions – accept the scopes for Spreadsheet, Gmail and Script.

Copy the generated URL; that is the live order form you can share with customers or embed on a simple landing page.

5. Add triggers for routine tasks

While the core order flow works instantly, you may want automated daily reports or inventory alerts.

  • Open the Apps Script editor, choose **Triggers → Add Trigger**.
  • Select the function sendDailyReport (you can create it later) and set the event source to **Time‑driven**, running **Every day** at a convenient hour.
  • For low‑stock alerts, add a trigger that runs checkLowStock every hour.

Example of a low‑stock alert function (inline for reference):

Sample code: function checkLowStock(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var prod = ss.getSheetByName('Products'); var data = prod.getRange('A2:D' + prod.getLastRow()).getValues(); var alerts = []; data.forEach(function(row){ if(row[3] < 5){ alerts.push(row[0] + ' (' + row[1] + ') low stock: ' + row[3]); } }); if(alerts.length){ MailApp.sendEmail('[email protected]', 'Low Stock Alert', alerts.join('\n')); } }

Common pitfalls and how to fix them

  • Permission errors – If the web app can’t write to the sheet, re‑deploy and ensure you chose “Execute the app as **Me**”.
  • Incorrect SKU entry – Add a dropdown list in the HTML form using google.script.run to fetch valid SKUs, reducing typos.
  • Duplicate order IDs – The simple timestamp‑based ID works for low volume. For high traffic, consider a separate “Counters” sheet that increments atomically.
  • Script quota limits – Apps Script has daily execution limits (e.g., 90 minutes of CPU time). Monitor usage under **My Executions**; if you approach limits, upgrade to a Google Workspace account.

Setup checklist

  1. Create a new Google Sheet with Products, Orders, Payments tabs.
  2. Populate Products with SKU, name, price, stock.
  3. Open Apps Script, add Code.gs and orderForm.html with the code shown above.
  4. Deploy as a web app (execute as you, access: anyone with link).
  5. Test the form yourself – submit a test order, verify the row appears, stock decreases, and email arrives.
  6. Set up time‑driven triggers for daily reports and low‑stock alerts (optional but recommended).
  7. Document the URL and share with your team or embed on your site.
  8. Backup the spreadsheet weekly (File → Make a copy) to protect against accidental data loss.

When to call a professional

If you anticipate any of the following, consider hiring a developer or a Nextpath Solutions consultant:

  • More than 50 orders per day – you may need a database migration (e.g., Cloud SQL) for performance.
  • Complex pricing rules (bundles, discounts) – custom logic can become hard to maintain.
  • Integration with external payment gateways – requires secure server‑side handling beyond Apps Script’s limits.
  • Regulatory compliance (PCI‑DSS, GDPR) – a professional can audit data handling and storage.

Conclusion

Using Google Apps Script you can launch a functional order‑management web app in a few hours, without paying for a separate SaaS platform. The approach gives you full control over inventory, email notifications and reporting, while keeping costs low (only Google Workspace if you need higher quotas). Follow the checklist, watch for the common pitfalls, and you’ll have a reliable system that grows with your business.

View the Apps Script ecommerce source‑code package. Need help deploying or customizing the workflow? Ask us about deployment or custom workflow support.