chapter

menu7. Loops and Repetition

monaco
appsscript

7 Loops and Repetition

Before you start. This chapter assumes you are comfortable with Chapters 4 through 6: declaring variables, the primitive types (numbers, strings, booleans), if / else if / else decisions with comparison operators, and arrays — including reading a range from a sheet with getValues() and writing one back with setValues().

Every program you have written so far has done each task exactly once. You declared a variable once, evaluated a condition once, read a cell once. That was the right pace for learning the vocabulary of the language. But real supply chain work is almost never about doing something once. It is about doing the same thing to hundreds of products, or repeating a calculation period after period until some target is reached.

This chapter introduces the language feature that makes that possible — the loop — and shows how a handful of lines can carry as much work as you ask of them.

7.1 Why Loops Matter

Supply chains are repetitive by their very nature. A catalog has thousands of SKUs. A production line has a series of workstations. A planning horizon has fifty-two weeks. A manifest has hundreds of shipments. Almost any analysis you would want to run — flag the items that need reordering, find the slowest station, project demand forward — is the same small piece of logic applied over and over.

You have already met that “small piece of logic.” In Chapter 5 you wrote a decision that compares an inventory level to a reorder point. That decision is correct. The problem is scale. Imagine a workbook with 500 products. You could copy your if statement 500 times, once per row. Three things go wrong immediately:

  1. It is tedious. Five hundred near-identical blocks is a lot of typing.
  2. It is error-prone. Somewhere around row 217 you will paste a cell reference wrong, and you will never find it.
  3. It cannot be maintained. When the logic changes — say, the company adds a “critical stockout” category — you now have 500 edits to make.

And often you do not even know the count in advance. How many rows of data are in this week’s import? How many weeks until this product runs out? You cannot copy-paste a block of code a number of times you do not know yet.

A loop solves all of this. A loop pairs a block of code with a rule for how many times that block runs. You write the logic once; the loop applies it as many times as the situation demands.

Every loop answers two questions:

  • What do I repeat? — the block of code called the body.
  • When do I stop? — the condition that is checked over and over.

Repetition in supply chain work tends to come in two natural shapes, and JavaScript gives you a loop built for each:

  • “Do this for every item in a known list.” Process every SKU in the catalog; check every workstation; total every line of an order. The number of repetitions is known up front. This is the job of the for loop (Section 7.4).
  • “Keep going until something changes.” Keep depleting inventory until it hits zero; keep reading rows until you reach a blank one. The number of repetitions is not known in advance — it is often the very thing you are trying to discover. This is the job of the while loop (Section 7.2).

One more idea ties this chapter to the last one. Arrays (Chapter 6) gave you a way to hold many values; loops give you a way to act on many values. They are partners. A column in a spreadsheet is, in effect, an array. A loop is how you touch every cell in it.

7.2 The while Loop

A while loop is the simpler of the two loops to read. In plain English it says: “as long as this condition is true, keep running this block.”

while (condition) {
  // body: the code to repeat
  
}

When the program reaches the loop, it checks the condition. If the condition is true, it runs the body once, then returns to check the condition again. If the condition is still true, it runs the body again. This continues until a check finds the condition false, at which point the program skips past the loop and continues with whatever comes next.

7.2.1 The four parts of a working loop

Every correct while loop has four moving parts. Three of them are inside the loop’s structure; one comes just before it.

  1. Initialization — before the loop, you set up the variable(s) the condition depends on.
  2. Condition — checked at the top of every pass; decides whether the body runs again.
  3. Body — the work you want repeated.
  4. Update — somewhere inside the body, you change something the condition depends on, so the loop can eventually stop.

If any of the four is missing or wrong, the loop misbehaves. The fourth — the update — is the one beginners forget, and forgetting it is serious. We will return to that.

7.2.2 A worked example: weeks of supply

Here is a supply chain question with a number for an answer: a product has 100 units on hand and sells 25 units per week — how many weeks until it stocks out? Notice that the answer (4) is also the number of times we will repeat a calculation. That is the signature of a while loop.

function weeksOfSupply() {
  let inventory = 100;      // initialization
  let weeklyDemand = 25;
  let weeks = 0;

  while (inventory > 0) {   // condition
    inventory = inventory - weeklyDemand;   // body
    weeks = weeks + 1;                      // update
    console.log("After week " + weeks + ", inventory = " + inventory);
  }

  console.log("The product stocks out in week " + weeks);
}

weeksOfSupply()

In the Apps Script editor, select weeksOfSupply from the function dropdown and click Run. The console.log output appears in the execution log at the bottom of the editor — the same kind of output you met in Chapter 2.

7.2.3 Tracing the loop by hand

The single most valuable habit you can build in this chapter is the hand-trace: stepping through a loop on paper, one pass at a time, writing down every variable as it changes. Here is the trace for weeksOfSupply.

Pass inventory before inventory > 0? inventory after weeks
1 100 true 75 1
2 75 true 50 2
3 50 true 25 3
4 25 true 0 4
— 0 false → stop — 4

The loop ran four times, and weeks ended at 4. Look closely at one thing: nowhere in the code did you write the number 4. The loop discovered it. That is the whole reason a while loop exists. When the number of repetitions is itself the answer — or simply unknown until the work is done — while is the right tool.

Working With Your AI Tutor A hand-trace is how a loop stops feeling like magic. If the table above is still fuzzy, paste the weeksOfSupply function into your AI tutor and ask: “Trace this loop one pass at a time and show me every variable in a table after each pass.” Then change the starting inventory to 130, predict the new answer yourself, and ask the tutor to check your prediction.

7.2.4 The cardinal danger: the infinite loop

A loop that never stops is called an infinite loop, and it is the defining bug of this chapter. It happens when the update never moves the condition toward false.

Look again at weeksOfSupply. What if weeklyDemand were 0? Then inventory = inventory - 0 never changes inventory, the condition inventory > 0 is true forever, and the loop runs without end. (Apps Script will eventually halt the script on its own after several minutes and report nothing useful — not a result, just a stopped script.)

There are three classic ways to write an infinite loop:

  • Forget the update entirely. No weeks = weeks + 1, no change to anything the condition tests.
  • Update the wrong thing. You change a variable, but not one the condition depends on.
  • Update in the wrong direction. The condition wants the variable to shrink, and your body grows it.

The discipline that prevents all three: every time you write a while loop, stop and ask one question — “What does my body change that my condition checks, and does that change move toward false?” If you cannot answer that clearly, you have a bug.

7.3 7.3 Example: Using a while Loop to Process Data in a Sheet

In Section 7.2 the numbers were hard-coded into the program. That is fine for learning the mechanics, but it is not how real tools work. In practice the data lives in the sheet, and the program reads it, processes it, and writes a result back.

7.3.1 Setting up the sheet

Create a sheet named Stockout and lay it out as a small label-and-value form:

{"label":"sample"}

Column A holds labels for a human reader; column B holds the values the program will use. Cell B5 is empty — that is where the answer goes.

7.3.2 The program

function weeksUntilStockout() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stockout");

  let inventory = sheet.getRange("B2").getValue();          // read input
  const weeklyDemand = sheet.getRange("B3").getValue();     // read input
  let weeks = 0;

  while (inventory > 0) {
    inventory = inventory - weeklyDemand;
    weeks = weeks + 1;
  }

  sheet.getRange("B5").setValue(weeks);                     // write the result
  console.log("Code Finished.  Check sheet for results")
}

The structure is exactly the loop from Section 7.2, with two changes at the edges. The inputs are read from the sheet with getValue() instead of being typed into the code, and the result is written back with setValue() instead of printed to the log. The loop in the middle is untouched.

With 480 units and demand of 60 per week, the loop runs eight times and cell B5 fills with 8.

This is where the value of a while loop becomes concrete. You did not know “8” when you wrote the program — and you did not need to. Change B2 to 540 and run it again: the loop simply runs nine times instead of eight. The loop adapts to the data. That is something a for loop, which commits to a count up front, cannot do as naturally.

7.3.3 Guarding against the infinite loop

Section 7.2 warned that a weekly demand of 0 produces an infinite loop. With the demand now coming from a cell, a user could easily type 0. A robust tool defends against that, and you already have the tool to do it — the if/else decision from Chapter 5:

function weeksUntilStockout() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Stockout");

  let inventory = sheet.getRange("B2").getValue();
  const weeklyDemand = sheet.getRange("B3").getValue();

  if (weeklyDemand <= 0) {
    sheet.getRange("B5").setValue("No demand — will not stock out");
  } else {
    let weeks = 0;
    while (inventory > 0) {
      inventory = inventory - weeklyDemand;
      weeks = weeks + 1;
    }
    sheet.getRange("B5").setValue(weeks);
  }
}

The if runs before the loop and decides whether the loop should run at all. This pattern — a decision that protects a loop from bad input — is everywhere in production code.

7.3.4 A second while pattern: walking down rows

There is another, very common reason to use while with a sheet: you want to process every row of data, but you do not know how many rows there are. This week’s import might have 40 rows; next week’s might have 312.

Suppose a sheet named Receipts lists received products down column A, starting in row 2, with the data ending wherever it ends. You can walk down the rows until you reach a blank cell:

function countReceipts() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Receipts");
  let row = 2;
  let count = 0;

  while (sheet.getRange(row, 1).getValue() !== "") {
    count = count + 1;
    row = row + 1;
  }

  console.log("Found " + count + " receipts.");
}

Trace the four parts: row starts at 2 (initialization); each pass reads cell (row, 1) and checks that it is not the empty string "" (condition); the body counts the row (body); and row = row + 1 moves the cursor down (update). When the read finally lands on a blank cell, the value is "", the condition becomes false, and the loop stops. The “unknown count” is the number of data rows — discovered, not declared.

An honest note on performance. Reading one cell per pass, as countReceipts does, is perfectly fine while you are learning and for small sheets. For large sheets it is slow, because each getRange().getValue() is a separate trip to the spreadsheet. The professional approach is to read the whole column at once with getValues() (Chapter 6) and then loop over the resulting array — which is exactly the job the for loop in Section 7.4 was built for. (Note also that walking until a blank cell assumes the data has no blank rows in the middle; a gap would stop the loop early.)

7.4 7.4 The for Loop

Look back at the loops you have written. Many of them follow an identical shape: set up a counter, check the counter against a limit, do some work, bump the counter. That shape is so common that JavaScript gives it its own dedicated syntax — the for loop.

for (initialization; condition; update) {
  // body
}

A for loop is not a new idea. It is the same while loop you already understand, with three of its four parts gathered onto a single line. Compare them directly. Here is a loop that prints “Step 0” through “Step 4,” written both ways:

// As a while loop
let i = 0;                       // initialization
while (i < 5) {                  // condition
  console.log("Step " + i);      // body
  i = i + 1;                     // update
}
// As a for loop — same behavior
for (let i = 0; i < 5; i++) {    // initialization; condition; update
  console.log("Step " + i);      // body
}

The three parts in the parentheses, separated by semicolons, are initialization, condition, and update — in that order. The body is what remains in the braces. Everything you learned about the four parts of a while loop still applies; the for loop just keeps them in one tidy place, where they are hard to forget.

Two small pieces of new notation appear above:

  • i++ is shorthand for i = i + 1. The two are identical; i++ is simply the form you will see most often in real code, especially in for loops.
  • i is the conventional name for a loop counter. It is short for “index,” and you will see it constantly.

7.4.1 Looping over an array

The single most important use of the for loop is walking through an array. Recall from Chapter 6 that an array of length 5 has indices 0, 1, 2, 3, 4 — counting starts at 0, and the last index is one less than the length. The standard array loop is built around exactly that fact:

for (let i = 0; i < items.length; i++) {
  // items[i] is the current element
}

Read the three parts carefully. Start i at 0 because that is the first index. Continue while i is less than items.length — not less than or equal. An array of length 5 has no index 5; reading items[5] gives back undefined. Using <= instead of < is the famous off-by-one error, and it is worth checking for every single time you write an array loop.

7.4.2 Worked example: flagging items that need reordering

Now the synthesis. This example uses variables, primitive types, if / else if / else, comparison operators, arrays, getValues(), setValues() — and the for loop to tie them all together.

Create a sheet named Inventory with a row of headers and one row per product:

A B C D
1 SKU On Hand Reorder Point Status
2 SKU-100 480 150
3 SKU-101 90 150
4 SKU-102 0 75
5 SKU-103 220 200
6 SKU-104 60 120

Column D is empty. The program will fill it with one of three statuses for every product:

function flagReorders() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inventory");

  const lastRow = sheet.getLastRow();
  const numItems = lastRow - 1;        // subtract the header row

  // Read the On Hand and Reorder Point columns in one batch
  const data = sheet.getRange(2, 2, numItems, 2).getValues();
  let statuses = [];

  for (let i = 0; i < data.length; i++) {
    let onHand = data[i][0];           // column B for this row
    let reorderPoint = data[i][1];     // column C for this row
    let status = "";

    if (onHand <= 0) {
      status = "STOCKOUT";
    } else if (onHand <= reorderPoint) {
      status = "REORDER";
    } else {
      status = "OK";
    }

    statuses.push([status]);           // wrap in [ ] — see note below
  }

  // Write the whole Status column back in one batch
  sheet.getRange(2, 4, statuses.length, 1).setValues(statuses);
}

Walk through what happens:

  • getLastRow() reports the last row that has data, so numItems is the product count, however many that is. Add a product to the sheet and the program adapts with no edit.
  • getRange(2, 2, numItems, 2).getValues() reads a block starting at row 2, column 2 (B), spanning numItems rows and 2 columns. The result, data, is a two-dimensional array: data[i] is one product’s row, data[i][0] is its On Hand, data[i][1] is its Reorder Point.
  • The for loop visits every row index from 0 to data.length - 1.
  • Inside the loop, the if / else if / else chain — straight from Chapter 5 — assigns one of three statuses. Order matters: STOCKOUT is tested first because a stocked-out item is also below its reorder point, and you want the more urgent label to win.
  • statuses.push([status]) adds the result to an array. Each pushed value is itself a one-element array — [status], not status — because setValues() expects a two-dimensional array, and a single column is a stack of one-cell rows.
  • setValues() writes the entire column back in one operation.

Run it, and column D fills: OK, REORDER, STOCKOUT, OK, REORDER. The decisive point: this same function processes 5 products or 5,000 with not one character changed. That is the payoff the copy-paste approach of Section 7.1 could never deliver.

7.4.3 Worked example: finding the bottleneck

The for loop is also the natural home for a pattern called the running comparison — scanning a list to find its largest or smallest value. In supply chain terms, that is exactly how you find a bottleneck.

A process is a sequence of steps, and each step has a capacity — the most it can produce per hour. A chain is only as strong as its weakest link, and a process line is only as fast as its slowest step. The bottleneck is the step with the minimum capacity, and that minimum is also the throughput of the entire line.

function findBottleneck() {
  const stepCapacities = [120, 95, 140, 80, 110];   // units/hour, steps 1–5

  let bottleneck = stepCapacities[0];   // assume step 1 is slowest, for now
  let bottleneckStep = 1;

  for (let i = 1; i < stepCapacities.length; i++) {
    if (stepCapacities[i] < bottleneck) {
      bottleneck = stepCapacities[i];
      bottleneckStep = i + 1;           // human-friendly step number
    }
  }

  console.log("Bottleneck is step " + bottleneckStep +
              " at " + bottleneck + " units/hour.");
  console.log("The whole line can produce at most " +
              bottleneck + " units/hour.");
}

Two design choices are worth pausing on:

  • Why seed bottleneck with the first element and start the loop at i = 1? You need something to compare against on the first pass. Treating step 1 as the provisional answer, then checking steps 2 onward, does exactly that. (An alternative is to seed with a deliberately huge number and loop from i = 0; both are valid.)
  • Why i + 1? The array is indexed from 0, but people number process steps from 1. bottleneckStep = i + 1 translates the machine’s count into a human’s.

For the data above, the loop reports step 4, at 80 units/hour — and that 80 is the ceiling on the entire line’s output, no matter how fast the other four steps run.

7.4.4 Choosing between for and while

Because a for loop is just a packaged while loop, the two are interchangeable in principle. The choice is about making your intent obvious to the next person who reads the code — often a future you.

  • Reach for for when the number of repetitions, or the collection, is known up front. “Do this for every SKU.” “Repeat for all twelve months.” “Visit every process step.” If you can phrase the task with the words “for each,” a for loop will read naturally.
  • Reach for while when the number of repetitions depends on something that unfolds as the loop runs. “Keep depleting until the product stocks out.” “Keep reading rows until a blank one.” If you phrase the task with “until” or “as long as,” a while loop will read naturally.

7.5 Chapter Summary

  • A loop pairs a block of code with a rule for how many times it runs, letting you write logic once and apply it many times — across many items, or across many repetitions.
  • A while loop runs its body as long as a condition stays true. Every correct while loop has four parts: initialization before the loop, and a condition, a body, and an update working together.
  • A while loop is the right choice when the number of repetitions is unknown in advance — and is often the very answer you are computing (weeks until stockout, number of data rows).
  • An infinite loop happens when the update never moves the condition toward false. Always ask: what does my body change that my condition checks, and does it move toward stopping?
  • A for loop is the same four parts as a while loop, gathered onto one line: for (initialization; condition; update). It is the right choice when the count or the collection is known up front.
  • The standard pattern for looping an array is for (let i = 0; i < arr.length; i++). Use <, not <= — the off-by-one error is the most common bug in array loops.
  • Loops and arrays are partners: arrays hold many values; loops act on them. Combined with getValues() and setValues(), a single loop can process a spreadsheet of any size.

7.6 Practice Problems

Working With Your AI Tutor Treat these as conversations, not quizzes. A productive prompt is not “give me the answer to problem 3” but “Here is my code for problem 3 and the error I’m getting — ask me questions until I can find the bug myself.” The goal is to leave each problem able to write the loop unaided.

1. Average demand (forecasting). A sheet named Demand holds twelve monthly demand values in cells B2:B13. Write a for loop that computes the average monthly demand and writes it to B15. Hint: keep a running total inside the loop, then divide by the number of values after the loop ends.

2. Moving average (forecasting). Harder. Using the same Demand sheet, compute a 3-month moving average and write each result into column C. Hint: think carefully about which rows can have a 3-month average at all — the first two cannot.

3. Weeks of supply for every product (inventory). Combine Sections 7.3 and 7.4. A sheet named Inventory has columns for SKU, On Hand, and Weekly Demand. For every product, compute the weeks until stockout and write it into a new column. Hint: this is your first nested loop — a while loop (the stockout simulation) running inside a for loop (one pass per product).

4. Step utilization (process flow capacity). Given the step capacities [120, 95, 140, 80, 110], the line throughput equals the bottleneck (Section 7.4). For each step, compute its utilization = throughput ÷ step capacity, expressed as a percentage. Which step comes out at 100%, and why does that make sense?

5. Fix the infinite loop (debugging). The loop below never stops. Explain precisely why, then fix it so it counts down from 500 to 0.

let units = 500;
while (units > 0) {
  console.log(units);
}

Hint: review the four parts of a while loop from Section 7.2. One of them is missing.