Intermediate Excel and Google Sheets with App Scripts

Introduction to ERP Systems and Spreadsheets

ERP (Enterprise Resource Planning) is the central nervous system of a business – it connects departments (sales, finance, operations, HR, inventory, procurement) so everyone works from a common set of data.

<!-- Intuition: Without an ERP, each department keeps its own records, causing delays, errors, and inefficiencies. An ERP streamlines processes and provides real-time data for better decisions. -->

Popular ERPs in India

• Global: SAP, Oracle, NetSuite, Microsoft Dynamics
• Indian: Tally, Zoho, Odoo, ERP Next
• Large enterprises (Infosys, Tata Steel, Mahindra) use full‑fledged ERPs; many SMEs use modular systems (Tally, Zoho) or self‑built spreadsheets.

Core ERP Modules

The Real‑World Role of Spreadsheets

Even large enterprises rely on spreadsheets alongside ERPs. In 2025, over 71% of large enterprises globally still use Excel or Google Sheets for critical financial and operational analysis. In India, giants and startups alike use spreadsheets for:

• Reporting & reconciliation
• Quick analysis
• Custom operations

Spreadsheets are the bridge between formal ERP systems and human flexibility.

What This Module Covers

We will treat spreadsheets as data capture and processing tools – like a mini‑ERP. The module builds toward creating your own mini‑ERP.

1. Linking sheets – VLOOKUP, INDEX+MATCH, dynamic referencing
2. Logical functions – IF, AND, OR for decision‑making in sheets
3. Delivery fee calculator – a real‑life logistics calculator
4. Google Forms – capture user input directly into sheets (field teams, customer feedback, order management)
5. Google Apps Script – automate repetitive tasks, link external APIs, turn static sheets into live business systems

Exam tip: Spreadsheets are not a crutch – they are a fundamental tool even in ERP‑heavy enterprises. Understanding how to use them as a mini‑ERP (linking tables, automating logic) is a skill tested in many business‑analyst interviews.

Key takeaways

• ERP unifies departments with real‑time, shared data.
• Common modules: CRM, inventory, procurement, finance, HR, production.
• Spreadsheets are still used by >70% of large enterprises for critical work – they bridge ERP rigidity with human flexibility.
• This module transforms spreadsheets into a mini‑ERP using lookup functions, logical formulas, Google Forms, and App Script.

Data Linking Across Sheets

Data linking turns separate spreadsheet tables into a connected system — a lightweight ERP. By linking master data (products, customers) to transaction sheets, you avoid manual lookups, reduce errors, and enable real‑time calculations.

Context: A wholesale vegetable delivery company operates in Bangalore, Chennai, and Mumbai. Orders come via phone, form, or app. The goal is to quickly calculate order costs and delivery fees using linked sheets.

Product Master: Structure and Auto‑Generation

A product master stores the core product catalogue. It’s a dedicated sheet with columns:

Product Code is the primary key — a unique identifier that prevents errors from name variations (spacing, case). Always link tables using codes, not names.

Automatic Code Generation with Text Formulas

Instead of typing codes manually, use a formula that auto‑increments when a product row is added.

=IF(B2<>"", "P" & TEXT(ROW(B2)-1, "0000"), "")

How it works:

• IF(B2<>"", … , "") — only generate a code if the product name (B2) is non‑empty.
• ROW(B2) returns the current row number (2 in this example). Subtract 1 to start from 1.
• TEXT(ROW(B2)-1, "0000") formats the number as a 4‑digit zero‑padded string (1 → "0001").
• Concatenate "P" with the formatted number → "P0001".

Copy the formula down the column. When you insert or delete rows, the codes update automatically.

Exam tip: Always use a unique, system‑generated key (like P0001) instead of manual numbering. It survives row insertions and deletions.

Protecting Master Data

Prevent accidental edits to the product master by protecting the range.

1. Select the range containing the master data.
2. Go to Data > Protect ranges (or Review > Protect Sheet in Excel).
3. Set permissions so that only authorised users (e.g., you) can edit.

This keeps the master data clean and reliable for all linked sheets.

Customer Master: Structure and Email Uniqueness

A customer master stores customer details. Columns:

Customer IDs are generated the same way as product codes, using "C" instead of "P":

=IF(B2<>"", "C" & TEXT(ROW(B2)-1, "0000"), "")

Enforcing Unique Emails with Data Validation

To prevent duplicate customer registrations, add a data validation rule on the email column.

1. Select the email range (e.g., E2:E).
2. Go to Data > Data validation.
3. Set Criteria to Custom formula is.
4. Enter the following formula:

=COUNTIF($E$2:E, E2) = 1

What it does:

• COUNTIF($E$2:E, E2) counts how many times the email in the current cell (E2, E3, …) appears in the entire column (from E2 downwards).
• The formula returns TRUE only when the count equals 1 (i.e., the email is unique).
• If the user tries to enter a duplicate email, the validation rejects the input.

Referencing note: The $E$2:E uses a mixed reference — the start of the range is locked ($E$2), but the end is relative (E), so as the rule applies to each cell, the range stays anchored to the top while the value being checked moves.

Exam tip: In data validation custom formulas, use mixed references ($ for the start) to keep the lookup range fixed while checking each row’s value.

Key Takeaways

• Linking sheets with unique codes (product code, customer ID) is the foundation of a spreadsheet‑based mini‑ERP.
• Auto‑generate codes using IF, TEXT, and ROW to avoid manual errors and handle row insertions.
• Protect master data ranges to prevent accidental edits.
• Enforce data integrity with data validation — e.g., =COUNTIF($E$2:E, E2)=1 ensures unique emails.
• Use mixed references ($E$2:E) to lock the start of a range while allowing the checked cell to move.

Data Linking using Spreadsheets – II

Building an order sheet with a calculation system. The goal: capture orders by selecting Customer ID and Product ID from validated lists, then automatically populate related fields (names, city, product name). This prevents manual entry errors and keeps data structured.

Data Validation for Dropdowns

Restrict inputs to valid IDs using dropdowns from a source range.

• Select the column (e.g., Customer ID).
• Data → Data validation → Add rule (Google Sheets) or Data Validation (Excel).
• Choose Dropdown from a range, then point to the ID column in the master sheet.
• Use absolute referencing ($A$1:$A$1000) so the range stays fixed when copied.
• Remove the $ on the last row (e.g., $A$1:$A) to make the range "infinite" (Google Sheets).

Apply a second validation for Product ID similarly.

Exam tip: Always use absolute referencing ($) for the lookup range in data validation when you intend to copy the validation down. Without $, the range shifts and breaks.

VLOOKUP for Single‑Row Lookups

VLOOKUP searches the first column of a table, moves horizontally to a specified column, and returns the corresponding value.

Syntax: =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: the ID to match (e.g., A2).
• table_array: the master data range (absolute, e.g., $A$1:$E$1000).
• col_index_num: the column number in the range (1‑based: 1 = first column, 2 = second, etc.).
• range_lookup: FALSE for exact match (always use for IDs).

Example – fill Customer Name =VLOOKUP(A2, CustomerMaster!$A$1:$E$1000, 2, FALSE)

• A2 = chosen Customer ID.
• CustomerMaster!$A$1:$E$1000 = master table (absolute).
• 2 = second column contains the name.

Copy down – the A2 reference changes (relative), but the table array stays fixed because of $.

Absolute vs. Relative Referencing

Shortcut: F4 (Windows) to toggle through absolute/relative modes.

Handling Missing Data – IFERROR

When no customer is selected, VLOOKUP returns #N/A. Wrap the formula in IFERROR to output a blank instead:

=IFERROR(VLOOKUP(...), "")

Apply the same pattern for city (col 3), product name, etc., referencing the appropriate master sheet and column index.

INDEX‑MATCH – Two‑Dimensional Lookups

INDEX‑MATCH is more flexible than VLOOKUP: it can look up values in any direction and handle two‑way lookups (row + column).

INDEX

=INDEX(reference, row_num, [column_num]) Returns the value at the intersection of a given row and column within reference.

MATCH

=MATCH(lookup_value, lookup_array, [match_type]) Returns the relative position of lookup_value in lookup_array (1‑based). Use 0 for exact match.

Combined

Replace row_num and column_num in INDEX with MATCH functions to dynamically find the correct row and column.

Workflow:

Example – delivery fee based on city and weight slab

To get the fee for city "CHN" and slab "10–25 kg":

• MATCH("CHN", city_header_row, 0) → column index 3.
• MATCH("10–25 kg", weight_slab_column, 0) → row index 4.
• INDEX(fee_table, row_index, col_index) → returns 120.

This works even if the lookup columns are not the leftmost (unlike VLOOKUP).

Key takeaways

• Data validation restricts inputs; always use absolute ranges when copying.
• VLOOKUP is simple for left‑to‑right lookups; set FALSE for exact match.
• IFERROR cleans up #N/A when no input is given.
• INDEX‑MATCH enables two‑dimensional lookups and is not limited to the first column.
• Absolute referencing ($) is critical for ranges that must not shift; use F4 to toggle.
• Automating lookups reduces manual entry errors (spelling, case, spacing).

Building a Delivery Fee Calculator

The goal is a calculator that takes city, quantity (kg), and product as inputs and outputs the final price (product cost + delivery fee). This mimics a mini-ERP system where data is linked across sheets.

Core components:

• Data validation for city (BLR, CHN, MUM), product (from product master), and customer (from customer master).
• Quantity slab – map a numeric quantity into a pre-defined weight range using IFS.
• Pricing table – lookup delivery fee based on city and slab using INDEX-MATCH.
• Product master – lookup price per kg using VLOOKUP.
• Final formula: $\text{final\_price} = (\text{price\_per\_kg} \times \text{quantity}) + \text{delivery\_fee}$

Using IFS for Quantity Slabs

IFS evaluates conditions sequentially – the first true condition returns its value. No need to check both lower and upper bounds; the order guarantees the correct range.

Example:

=IFS(
  A1<5, "Less than 5 kg",
  A1<10, "5 to 10 kg",
  A1<25, "10 to 25 kg",
  A1<100, "25 to 100 kg",
  TRUE, "Above 100 kg"
)

• If quantity = 10, condition A1<10 is false (since 10 is not less than 10), so it falls to A1<25 → "10 to 25 kg".
• If quantity = 9.9, A1<10 is true → "5 to 10 kg".

Exam tip: IFS stops at the first true condition. Always place narrower ranges first. Use TRUE as a catch-all final condition.

INDEX-MATCH for Two-Way Lookup

To retrieve the delivery fee from a pricing table where rows = city and columns = quantity slab:

• Row match: MATCH(city, city_range, 0)
• Column match: MATCH(slab, slab_range, 0)
• INDEX: INDEX(fee_range, row_num, col_num)

Worked example (using the example data):

• Quantity = 23 kg → slab = "10 to 25 kg". City = CHN.
• Row match on CHN → row 2; column match on "10 to 25 kg" → column 3.
• INDEX returns 120.

Always use exact match (0 or FALSE) in MATCH for pricing lookups.

VLOOKUP for Product Price per kg

From the product master table (columns: Product Name, Price per kg):

=VLOOKUP(product_name, product_master, 2, FALSE)

• Use product codes instead of names in production to avoid duplicates.

Adding Subscription Logic with IF, AND

Enhance the calculator: if the customer is free, use the standard delivery fee (from INDEX-MATCH). If premium, apply a flat rate per city regardless of weight.

Steps:

1. VLOOKUP customer → subscription type (free / premium).
2. Use IFS with AND:

   =IFS(
     subscription_type = "free", standard_delivery_fee,
     AND(subscription_type = "premium", city = "BLR"), 30,
     AND(subscription_type = "premium", city = "CHN"), 50,
     AND(subscription_type = "premium", city = "MUM"), 70
   )
   

3. The AND function returns TRUE only if all conditions are true.

Final calculator structure (user inputs only yellow cells):

• Customer (dropdown) → auto-populates city and subscription type via VLOOKUP.
• Product (dropdown) → auto-populates price per kg via VLOOKUP.
• Quantity (manual) → determines slab → delivery fee via INDEX-MATCH (then overridden by premium logic if needed).
• Final price = price_per_kg * quantity + delivery_fee_final

Exam tip: Use nested IF or IFS for multi-condition branching. AND/OR reduce nesting and improve readability.

Key Takeaways

• IFS evaluates conditions sequentially; use TRUE as a default.
• INDEX-MATCH performs two-dimensional lookups (row + column) without restructuring data.
• VLOOKUP is simpler for single-column lookups; prefer product codes over names.
• Combine IF/IFS with AND to implement conditional business rules (e.g., premium flat rate by city).
• Build a clean user interface: hide helper columns, mark input cells in yellow.
• All formulas can reference data from other sheets, creating an interconnected system that simulates an ERP.

Capturing Data using Google Forms

Google Forms integrated with Google Sheets provides a structured way to capture data from external users (e.g., sales or inventory teams) directly into a spreadsheet. Rather than manually entering data into a sheet, a form enforces consistency, reduces errors, and automatically timestamps entries. This feature is unique to Google Sheets and is not available in Excel (which requires custom VBA forms).

Creating a form from within a sheet links the responses to a dedicated sheet tab; every new submission appends a row instantly.

Creating a form from Sheets

• Go to Tools → Create a new form (or use the Forms menu in Sheets).
• The form is automatically linked to a new sheet tab named “Form Responses 1”.
• Rename the form and the response tab (e.g., “Inventory Purchase Form”).

Designing the form questions

• Dropdown question – predefined choices (e.g., product names). Useful for standardised data.
• Short answer – free text; apply a validation rule (e.g., number > 0) to enforce quality.
• Timestamp – added automatically by the form (not a question).

Exam tip: Always validate numeric fields (number > 0) to prevent invalid entries. Forms with validation are significantly more reliable than raw sheet input.

Publishing and collecting responses

• Click Publish → a responder link is generated.
• Anyone with the link can fill the form; each submission appends a row to the linked sheet.

Automatic logging in the sheet

• The response sheet logs: Timestamp, and each question’s answer as columns.
• Data can be immediately used for analysis (e.g., inventory purchases linked to product master).

Excel vs. Google Sheets

Key takeaways

• Google Forms provide a controlled, structured input method for Sheets – ideal for teams.
• Create a form from Tools → Create a new form; it auto-links to a response sheet.
• Use dropdown for fixed options and short answer with validation for numeric fields.
• Timestamps are captured automatically.
• Forms are a Google Sheets-only advantage over desktop Excel.
• The logged data (e.g., inventory purchases) can be linked to other sheets for further analysis (e.g., reorder level calculations).

Lead Time

Lead time is the time between placing an order and receiving the goods. It is a critical input for inventory planning: knowing how long replenishment takes determines when to reorder.

• Average lead time: the typical number of days from order to delivery (e.g., tomatoes and potatoes usually arrive in 2 days, oranges in 5 days).
• Max lead time: the longest possible delay (e.g., from an unreliable supplier). Used to manage risk – even if most orders arrive on time, a safety buffer is needed for late deliveries.

Both values are derived from historical data, supplier agreements, or experience. In the sample data:

Key takeaway: Lead time drives reorder timing; max lead time builds a risk cushion.

---

On-Hand Inventory

The quantity of each product currently in stock. Combined with lead time and expected sales, it determines when and how much to reorder.

• If inventory is low and lead time is long, a reorder must be placed earlier.
• On-hand inventory is a direct input for reorder point calculations.

---

Average Daily Sales

Average daily sales estimates how many units of a product are sold per day. This is used to forecast how much inventory will be consumed during the lead time.

Calculation method: Use a pivot table on the order master to first sum daily quantities per product, then average those daily totals.

Step 1: Build a pivot table that sums order quantities per product per day

• Row fields: Product ID and Product Name (with repeat row labels), and Purchase Date.
• Value field: Order Quantity, summarized by Sum.
• Result: for each product and each date, the total kgs sold that day.

Common mistake: Directly averaging order quantities gives the average order size, not average daily sales. Summing daily totals first corrects for multiple orders on the same day.

Step 2: Create a second pivot table on the first pivot table to compute average and standard deviation

• Row fields: Product ID and Product Name (filter blanks).
• Value fields:
  • Average of Sum of Order Quantity → average daily sales.
  • Standard Deviation of Sum of Order Quantity → measure of daily fluctuation.

Data flow:

Formally:

Let $Q_{p,d}$ be the sum of order quantities for product $p$ on day $d$ (from Step 1). Then for $n$ days:

$$\text{Average Daily Sales}_p = \frac{1}{n}\sum_{d} Q_{p,d}$$ $$\text{Std Dev (sample)}_p = \sqrt{\frac{1}{n-1}\sum_{d} \left(Q_{p,d} - \text{Avg}_p\right)^2}$$

Exam tip: Always ask: "What does the average represent?" The pivot table's average of raw order quantities is not average daily sales – it's the average order size. You must aggregate by day first.

---

Standard Deviation of Daily Sales

Standard deviation captures the fluctuation (inconsistency) of daily sales. A higher standard deviation means sales vary more from day to day.

• More fluctuation → need a larger buffer inventory (safety stock) to avoid stockouts on unexpectedly high sales days.
• Higher average daily sales also require more inventory because items move faster.

Key relationship:

• Average daily sales → base stock needed for lead time.
• Standard deviation → safety stock to cover variability.

Key takeaway: Use both average and standard deviation of daily sales to set inventory levels – not just the average alone.

---

Key takeaways for Concepts in Spreadsheets – I

• Lead time (average & max) drives reorder timing and risk management.
• On-hand inventory is the starting point for reorder decisions.
• Average daily sales must be computed as the average of daily sums, not of raw orders.
• Standard deviation of daily sales quantifies demand variability and determines safety stock.
• Pivot tables on pivot tables enable this two‑step aggregation.

Enhancing the Product Master with Sales Statistics

To compute inventory metrics, the product master table is extended with two derived fields from daily sales data:

• Average daily orders – obtained via a pivot table (grouping by product key) and then pulled into the master with =VLOOKUP(product_key, pivot_table_range, column_index, FALSE).
• Standard deviation of daily orders – same pivot approach, but selecting the STDEV.P (or STDEV.S) aggregation. The formula is copied across after anchoring the lookup range with absolute references (e.g., $A$1:$C$100).

Jackfruit example: average daily order = 12, standard deviation is high because order quantities in the source data include 50 and 25 – orders fluctuate significantly.

Why Safety Stock Is Necessary

Inventory faces two independent sources of uncertainty:

1. Demand fluctuation – daily orders vary around the average.
2. Supply lead time fluctuation – the time between placing an order and receiving it is not constant (e.g., average 2 days, max 5 days).

Without a buffer, a spike in demand or a delay in supply causes a stockout – inability to fulfil orders.

Safety Stock: A Rudimentary Calculation

Safety stock is the minimum inventory kept to absorb worst‑case scenarios.

Approach 1: Simple range method (illustrative)

$\text{Safety Stock} = (\text{Max Lead Time} - \text{Avg Lead Time}) \times (\text{Max Daily Orders} - \text{Avg Daily Orders})$

This covers the case where both extremes happen simultaneously.

Approach 2: Using standard deviation (practical)

Since we have only the average and standard deviation of daily orders, we approximate the “max” as $\text{Avg} + 2\sigma$ (covering ~95% of demand if normally distributed). Then:

$\text{Safety Stock} = (\text{Max Lead Time} - \text{Avg Lead Time}) \times 2\sigma_{\text{daily orders}}$

Why 2σ? In a normal distribution, the average ± 2σ contains about 95% of all values. This corresponds to a 95% service level – you will have enough stock 95% of the time.

This is a rudimentary method; professional settings use joint distributions of lead time and demand variability.

Reorder Point (ROP)

The reorder point is the inventory level at which a new order should be placed. It consists of two parts:

• Safety stock – always kept for worst‑case events.
• Cycle stock – quantity needed to cover average demand during the average lead time.

$\text{Reorder Point} = \text{Safety Stock} + (\text{Average Daily Orders} \times \text{Average Lead Time})$

When physical stock falls to this level, a replenishment order is triggered.

Worked Example (Illustrative)

Assume:

• Average daily orders = 12 units
• Std dev of daily orders = 5 units
• Average lead time = 2 days
• Max lead time = 5 days

Safety stock (using 2σ method): $\text{Safety Stock} = (5 - 2) \times (2 \times 5) = 3 \times 10 = 30 \text{ units}$

Reorder point: $\text{ROP} = 30 + (12 \times 2) = 30 + 24 = 54 \text{ units}$

Interpretation: When on‑hand inventory reaches 54 units, place a new order. The 30 units of safety stock will only be used if demand is unusually high or lead time is extended.

Inventory Decision Logic

The safety stock ensures that during the lead time, even if demand surges or the shipment is delayed, the inventory does not hit zero.

Key takeaways

• Use VLOOKUP and pivot tables to compute average and std dev of daily orders per product.
• Safety stock = (max lead time – avg lead time) × 2 × std dev of daily orders (rudimentary, ~95% service level).
• Reorder point = safety stock + (avg daily orders × avg lead time).
• Safety stock is a buffer against demand variability and lead time variability; reorder point triggers timely replenishment without dipping into the buffer.
• This is a simplified model; real‑world systems often use joint distributions and dynamic safety stock.

Inventory Management with Spreadsheet Functions

Inventory management answers two practical questions: when to order and how much to order. Holding too much stock ties up capital and incurs storage costs; holding too little risks stockouts. Spreadsheets provide tools to automate these decisions.

Economic Order Quantity (EOQ) – Concept

EOQ is a classic formula that balances ordering cost (delivery charges per order) and holding cost (storage, capital lockup). The optimal order quantity minimises total cost:

$\text{EOQ} = \sqrt{\frac{2DS}{H}}$

where $D$ = annual demand, $S$ = cost per order, $H$ = holding cost per unit per year. In practice, a simpler rule based on reorder point and on-hand inventory is often used.

Reorder Point & Safety Stock

• Reorder point – the inventory level that triggers a new order. It accounts for lead time and expected demand.
• Safety stock – extra buffer to cover demand fluctuations.
• Assume the reorder point is given per product (e.g., 50 for Mosambi, 28 for Tomato Hybrid, 69 for another product).

Calculating On-Hand Inventory

On-hand inventory at any moment is the difference between total purchases and total sales (orders):

$\text{On Hand} = \sum \text{Purchases} - \sum \text{Sales}$

Using SUMIF, you sum quantities by matching a product ID.

Negative on-hand indicates sold more than purchased – a data error (theft, recording mistake, or missing purchase). This must be corrected.

Extracting Product IDs from Combined Strings

If product data is stored as a combined string (e.g., "Orange P001"), extract the ID for lookup:

• LEFT – when ID has fixed length (e.g., 5 characters): =LEFT(A2,5)
• MID + FIND – when ID position varies:
  • Find the space: =FIND(" ", A2)
  • Extract from that position+1: =MID(A2, FIND(" ", A2)+1, LEN(A2)-FIND(" ", A2))

Exam tip: Use LEFT when product codes are fixed-length; use MID+FIND when they are variable-length. Always test edge cases (missing strings).

Decision: How Much to Order

If on-hand inventory is below the reorder point, order enough to bring it up to the reorder point; otherwise order zero.

$$\text{Order Quantity} = \begin{cases} \text{Recorder Point} - \text{On Hand} & \text{if On Hand} \leq \text{Reorder Point} \\ 0 & \text{otherwise} \end{cases}$$

Examples:

• Mosambi: On-hand = 41, Reorder Point = 50 → Order 9
• Tomato Hybrid: On-hand = 9, Reorder Point = 28 → Order 19
• Another product: On-hand = 29, Reorder Point = 69 → Order 40

Additional Use: Theft Detection

If the spreadsheet-calculated on-hand differs from a physical count (and no recent purchases/sales explain it), it may indicate theft or loss. Regular reconciliation becomes a control mechanism.

Key takeaways

• Inventory management requires knowing when (reorder point) and how much (order quantity).
• On-hand inventory = total purchases minus total sales, computed using SUMIF.
• Negative on-hand is a red flag – must be resolved.
• Order quantity rule: if below reorder point, order the difference; otherwise, zero.
• Spreadsheet functions like LEFT, MID, and FIND extract product IDs for lookups.

Recap: Core Concepts in Spreadsheets

The module covered two broad workflows: data ingestion and cleaning from forms, and inventory management using derived metrics to produce actionable reorder decisions. All tools (text formulas, pivot tables, lookup functions, conditional logic) work together to turn raw entries into a dynamic inventory system.

Data from Forms and Text Cleaning

• Forms feed raw data (purchases, sales) directly into a sheet — multiple teams can submit to the same master table.
• Text formulas clean or extract substrings: LEFT, MID, FIND, LEN (length) are the core set. Use them to parse structured entries (e.g., extract order IDs, dates).

Inventory Metrics – The Decision Chain

The inventory logic builds layer by layer:

How the numbers work (illustrative, no real numbers given)

• From a pivot table, obtain average daily consumption $\mu_c$ and its standard deviation $\sigma_c$.
• Set safety stock as: $\text{Safety Stock} = 2 \cdot \sigma_c \cdot \text{Max Lead Time}$ (The “max” taken as $2\times$ standard deviation.)
• Compute reorder point: $\text{Reorder Point} = \mu_c \cdot \text{Avg Lead Time} + \text{Safety Stock}$
• How much to order: $\text{Order Quantity} = \text{Reorder Point} - \text{On-Hand Inventory}$

Exam tip: The two‑pivot‑table approach is the key skill: one for average consumption, another (successive) for variation. Always double‑check that the second pivot table uses the same source data with appropriate aggregation (STDEV).

Key Functions & References

• SUMIF – conditional sum (e.g., total sales for a product).
• VLOOKUP – fetch data from another table by key (e.g., product name → unit cost).
• IF – logical branching in formulas.
• Absolute vs. relative reference – use $ to lock rows/columns when copying formulas across a range (e.g., $A$1 for a fixed lookup value).

Pivot Tables – Two Successive Uses

1. First pivot: group sales data by day → get average daily consumption.
2. Second pivot: group the same data by day → get standard deviation of daily consumption (using the STDEV aggregation).

This demonstrates how the same raw data can yield both central tendency and dispersion with a small shift in pivot configuration.

Practical Extensions (mentioned, not covered)

• Send automatic email nudges when order quantity > 1.
• Integrate external APIs for price updates or delivery tracking.
• Handle multiple line items per order, perishability (inventory age), FIFO/LIFO lot tracking, and Economic Order Quantity (EOQ) .

Key Takeaways

• Forms + text formulas let you collect and clean data automatically.
• Inventory decisions rely on lead time, consumption (mean & std dev), safety stock, and reorder point.
• Two successive pivot tables extract both average and variation from the same dataset.
• SUMIF, VLOOKUP, IF and absolute references are the building blocks for dynamic spreadsheets.
• The system outputs a concrete order quantity — not just tracking, but actionable insight.

Automation in Google and Excel - I

Automation transforms a static spreadsheet into an intelligent system that acts, reacts, and communicates automatically. The most accessible entry point is macros — essentially a robot that records your manual actions (formatting, data cleanup, copy-paste) and replays them with a single click. No coding required to begin.

What is a Macro?

A macro is a stored sequence of operations. You record a macro once, then trigger it to repeat the exact same steps on new data. This is ideal for repetitive tasks like:

• Adding bold headings and cell colors
• Setting borders
• Cleaning or restructuring data

Behind the scenes: Every macro is generated as code. In Microsoft Excel the code is written in VBA (Visual Basic for Applications). In Google Sheets macros produce Google Apps Script (JavaScript-based). The recording tool writes the code for you.

Recording a Macro – Demonstration

1. Select a range (e.g., a row) that you want to format.
2. In Google Sheets: Extensions → Macros → Record macro.
3. Choose Relative reference (explained below).
4. Perform the formatting actions (e.g., bold, italic, green text, gray background).
5. Click Save – name the macro (e.g., Color Row) and assign an optional shortcut key (e.g., Ctrl+Shift+Alt+1).
6. To reuse: select another row, then run the macro via Extensions → Macros → [macro name] or use the keyboard shortcut.

Relative vs. Absolute References in Macros

The choice during recording determines whether the macro always acts on the same cells or adapts to the current selection.

Exam tip: For reusable macros that work anywhere in the sheet, always use relative references during recording. Absolute references lock the action to one location, making the macro nearly useless for repetitive tasks.

The Code Behind the Magic

When you record a macro, the tool automatically writes the corresponding script. In Excel it becomes VBA; in Google Sheets it becomes an Apps Script function. You can view and edit the code later – opening the door to more advanced automation without starting from scratch.

Key takeaways

• Macros automate repetitive formatting, data cleanup, and similar tasks without coding.
• Record a macro by performing the steps once; replay it anytime.
• Relative references let the macro work on whatever cell/row you select.
• Absolute references fix the macro to the original recorded location.
• Behind every macro is runnable code (VBA for Excel, Apps Script for Google Sheets) that can be edited for finer control.

What is Google App Scripts?

Google App Scripts is a JavaScript‑based scripting platform that extends Google Workspace products, especially Google Sheets. It allows you to write custom functions, automate processes, connect sheets with external APIs, react to events, send emails, and generate documents. Access it via Extensions → App Scripts inside a Sheet.

Creating Custom Functions

App Scripts lets you write your own formulas that behave exactly like built‑in ones. Example – a customCubic function:

function customCubic(number) {
  return number * number * number;
}

• Save the script, then deploy (or skip deployment for personal use).
• In any cell, type =customCubic(3) → returns 27.
• Any JavaScript logic is usable; custom functions can reference cell ranges and perform complex calculations.

Exam tip: Custom functions must be pure JavaScript. They appear in the autocomplete as you type the function name. If you share the sheet, the script must be deployed properly.

Working with Sheets: Classes and Methods

App Scripts uses classes (e.g., SpreadsheetApp, Sheet, Range) and methods to interact with spreadsheets. The official developer documentation is the definitive guide.

Example from the docs – reading product names:

function logProductNames() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getDataRange();
  var data = range.getValues();
  for (var i = 0; i < data.length; i++) {
    Logger.log('Product name: ' + data[i][0] + ', Number: ' + data[i][1]);
  }
}

• SpreadsheetApp.getActiveSheet() – gets the current sheet.
• getDataRange() – captures all data in the sheet.
• getValues() – returns a 2D array of cell values.
• Iterate over rows and access columns by index (data[i][0], data[i][1]).

Besides reading/writing cells, App Scripts can send emails, interact with other Google services, and connect to external APIs – all through documented classes and methods.

Triggers – Running Scripts Automatically

Logic written in App Scripts can be set to run automatically using triggers. Triggers fire based on events (e.g., on edit, on form submit) or on a time‑driven schedule (e.g., every hour). This enables fully automated workflows without manual execution.

Key Resources

• App Scripts guides – comprehensive walkthroughs.
• Sheets–specific documentation – covers classes, methods, and examples.
• For those with coding experience, the official docs are the primary reference.

Key takeaways

• Google App Scripts is a JavaScript platform for extending Google Sheets.
• Custom functions are written in JavaScript and behave like built‑in formulas.
• Scripts interact with Sheets via classes (SpreadsheetApp, Sheet, Range) and methods.
• Triggers enable scripts to run automatically on events or time‑based schedules.
• Official developer documentation is the essential resource for learning and implementation.

What Triggers Are

Triggers automate the execution of Apps Script functions based on specific events or schedules. Instead of manually running a macro, a trigger lets the system call the function automatically.

• Time-driven triggers – run code at a fixed schedule (e.g., every evening at 5 PM).
• Event-driven triggers – run code when an action occurs (e.g., form submitted, cell edited, sheet opened).

Types of Simple Triggers in Google Sheets

Exam tip: onEdit() captures the edited range via an e parameter—use e.range to apply logic only to certain columns.

Worked Example: Daily Inventory Email Alert

Goal: Automatically email the inventory manager every evening if any product’s “how much to order” (column index 11) is greater than 0.

Step-by-step script logic:

1. Get the Product Master sheet (by name).
2. Fetch all data as a 2D array using getRange().getValues().
3. Loop through rows. For each row:
   • Read product name (column index 1) and “how much to order” (column index 11).
   • If quantity > 0, append a line to a message string.
4. If message is not empty, send email using MailApp.sendEmail(to, subject, body).

function inventoryMail() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Product Master');
  var data = sheet.getDataRange().getValues();
  var message = '';

  for (var i = 0; i < data.length; i++) {
    var product = data[i][1];
    var qty = data[i][11];
    if (qty > 0) {
      message += product + ' needs to be ordered. Quantity needed: ' + qty + '\n';
    }
  }

  Logger.log(message);  // debug in Apps Script console

  if (message !== '') {
    MailApp.sendEmail('iimbspreadsheets@gmail.com', 'Inventory to Order', message);
  }
}

Deploying the trigger:

1. In Apps Script editor, deploy as a web app (not add-on).
2. Authorize the script to access Gmail and Sheets.
3. Set up trigger via Edit → Current project’s triggers:
   • Function: inventoryMail
   • Event source: Time-driven
   • Type: Day timer, e.g., 5 PM–6 PM
   • Save and re-deploy.

Result: The script runs automatically every day, checks the sheet, and emails the manager.

Why Triggers Matter

• Turn a one‑time macro into recurring automation (“set and forget”).
• Reduce manual checking – the system alerts you only when action is needed.
• Combine with other Google services: MailApp, CalendarApp, DocsApp, URL Fetch (to call external APIs).

Beyond This Example

• Pull data from an API and write to Sheets.
• Generate automated reports and email as PDF.
• Validate data entries on edit.
• Merge Sheets with Google Docs to create personalized documents.
• Build internal tools that behave like standalone applications.

For Excel users: equivalent functionality exists via VBA, Power Automate, and Office Scripts.

Key takeaways

• Triggers = run code automatically on a schedule (time‑driven) or on user actions (event‑driven).
• MailApp.sendEmail() lets you send notifications directly from Sheets.
• Deploy as a web app and authorise once; triggers run under your account.
• A trigger combined with a logic loop (e.g., inventory check) creates a real‑time alert system.
• Apps Script is extensible to APIs, document generation, and custom tools.

What is URL Fetch?

URL Fetch is a built‑in Apps Script class (UrlFetchApp) that allows a spreadsheet to make HTTP requests to external APIs. Think of it as a bridge between your sheet and the rest of the internet — if a service exposes an API, your spreadsheet can talk to it.

let response = UrlFetchApp.fetch(url, options);

This works exactly like fetch() in JavaScript. It enables a spreadsheet to not only store data but also to intelligently interact with real‑time services and machine‑learning models.

---

Common use cases for URL Fetch

Exam tip: URL Fetch is what turns a passive spreadsheet into an active system that can read, write, and think by leveraging external APIs.

---

Worked example: AI‑powered email replies from Google Forms

Scenario: A photography business collects customer queries via Google Forms. We want every form submission to:

1. Trigger an Apps Script function.
2. Send the query to a free LLM (Grok) via URL Fetch.
3. Use the LLM’s reply to send a personalised, professional email back to the customer.
4. Log the response in the spreadsheet.

Step‑by‑step process

Key code concepts (not full code, but the logic)

• Trigger: onFormSubmit(e) — automatically runs when the linked form receives a response.
• Reading data: The event object e gives access to the active sheet; the last row contains the new submission (email in column B, query in column C).
• Building the request: Construct a JSON payload with:
  • model: e.g., llama3 (free via Grok cloud).
  • messages: a system message (you are a professional email generator) and the user query.
  • authorization: Bearer token (your API key from Grok cloud).
• Calling the API: UrlFetchApp.fetch(url, options) sends a POST request to Grok’s completions endpoint.
• Parsing the response: Extract the text content from the nested JSON response.
• Sending email: MailApp.sendEmail(email, subject, body) with the AI‑generated text.
• Logging: Write the response back into the sheet for audit.

What the user sees

The customer receives a warm, personalised email moments after submitting the form.

Exam tip: Free LLM APIs (Grok, some Gemini tiers) give a limited number of requests per day — always check the quota before deploying in production. For real‑world use, consider paid tiers or rate‑limiting.

---

Key takeaways

• URL Fetch (UrlFetchApp) connects Google Sheets to any external API — a bridge to real‑time data and AI.
• Useful for: live currency, stock prices, CRM sync, messaging, and LLM‑powered automation.
• The worked example combines onFormSubmit trigger, URL Fetch to Grok, and MailApp to auto‑generate and send personalised email replies.
• The system can log the AI response back into the sheet for record‑keeping.
• This demonstrates moving beyond logic‑based automation (IF formulas) to intelligent content generation using large language models.
• Always authorise the script once (prompted on first run) and set up the trigger via the Apps Script editor (Edit → Current project’s triggers).