menu4. Variables and Data

monaco

4 Variables and Data

4.1 What Variables Are and Why They Matter

As you begin writing programs that do more than print a message or perform a single calculation, you need a way to store information — something your code can remember, reuse, and change as it runs. That’s exactly what variables are for. A variable is a named container that holds a piece of data. Think of it as a labeled box: you put something inside, give the box a name, and later you can open it, change what’s inside, or use its contents in a calculation.

We’ll spend this chapter on one small operations situation, so the values you store always mean something. The BYU-Idaho University Store buys classroom clickers from a supplier. Demand runs about 3,600 units a year; it costs $70 to place and receive an order; holding one clicker in stock costs $2.20 a year; and the clickers arrive in cases of 20 for $360. By the end of the chapter those figures will become the variables in a real inventory calculation — how many to order at a time. For now, just meet the numbers.

In JavaScript and Google Apps Script, variables let you keep track of values such as numbers, text, dates, or even entire lists of data. Without variables, every program would be rigid and repetitive. With them, your code becomes flexible, dynamic, and able to respond to different situations.

A simple example looks like this:

let annualDemand = 3600

Here, annualDemand is the variable’s name, and 3600 is the value stored inside it. Once you’ve created the variable, you can use it anywhere in your script:

console.log(annualDemand)

To see this execute in Google Apps Script, do the following:

1. Open a new Google Sheet
2. From the menu bar, choose “Extensions” then “Apps Script”

This opens the Google Apps Script editor with a new script file named “Code.gs”:

Notice that there’s already some code here in the Code.gs file. What you see is the basic structure of a function. We’ll talk more about functions later, but it’s the structure that lets us group multiple lines of code to execute together. To run code, write (or paste) the lines between the braces:

Now your code is ready to execute. Click the “Run” button.

This runs the function and opens the execution log:

There are three entries in the execution log: the first and last just show the times the function began and finished. The remaining item displays the value of the variable on the line that logged it. You can modify the code in the myFunction function to run any of the examples in this section.

Now let’s continue with variables. Modify the code as follows:

let onHand = 500
onHand = 460
onHand = onHand - 20
console.log(onHand)

The first statement defines onHand and gives it a value of 500 — the clickers currently in stock. The second statement changes it to 460, perhaps after a shipment of orders went out. The third statement sets a new value, but to calculate it the computer must first read the current one: at this point onHand is 460, so onHand - 20 evaluates to 440, and that becomes the new value. The final statement prints 440 into the execution log. Give it a try.

This ability to store and update information is at the heart of programming. It’s how you track stock as it moves, remember inputs, process spreadsheet data, or build tools that adapt to whatever information they’re given.

Variables matter because they let your programs:

• Reuse values without rewriting them
• Respond to changing data
• Store results from calculations
• Organize information in a clear, meaningful way
• Build more complex logic that depends on what’s happening in the program

In Google Apps Script, variables become especially powerful because they can hold values pulled directly from a spreadsheet — a column of demand figures, a list of items, or a single cell’s content. Once that data is in a variable, your script can analyze it, transform it, or write new results back into the sheet.

As you move through this chapter, you’ll learn to create variables, choose good names for them, and work with different kinds of data. These skills are the foundation for everything you’ll build next.

4.2 Primitive Types: Strings, Numbers, Booleans

Every programming language needs a way to represent basic kinds of information. In JavaScript — and therefore in Apps Script — these fundamental building blocks are called primitive types. They’re the simplest forms of data your program can work with, and they show up everywhere: in calculations, text, decisions, and interactions with spreadsheet values.

Understanding these types will help you write clearer code and avoid common mistakes as you start working with real data from Google Sheets.

4.2.1 Strings: Working With Text

A string is any piece of text — words, symbols, even empty space. Strings are written inside quotes:

let item = "classroom clicker"
let supplier = "campus electronics supplier"
let note = ""

Strings are useful for labels and descriptions, messages you log or display, data pulled from spreadsheet cells, and building dynamic text. You can combine them with the + operator:

let label = "Reorder report for: " + item

In Apps Script, strings are especially common because spreadsheet data often arrives as text — even when it looks like a number.

4.2.2 Numbers: Doing Math and Calculations

Numbers in JavaScript represent both whole numbers and decimals:

let orderingCost = 70
let holdingCost = 2.20
let casePrice = 360

You can perform all the usual arithmetic. For instance, a case holds 20 clickers, so the cost of a single unit is the case price divided by 20:

let unitCost = 360 / 20        // $18 per clicker
let weeklyDemand = 3600 / 52   // about 69 clickers a week

In Google Sheets, numbers are everywhere — totals, counts, costs, demand — so being comfortable with numeric operations is essential. Apps Script can read numbers directly from cells, use them in calculations, and write new results back.

4.2.3 Booleans: True or False Values

A boolean represents one of two possible values:

let isPreferredSupplier = true
let isStockedOut = false

Booleans are the backbone of decision-making in your programs. They’re used in conditions, comparisons, and logic:

let onHand = 120
let belowReorderPoint = onHand < 150   // becomes true

Whenever your script needs to choose between two paths — place an order or not, flag an item or skip it — a boolean is involved. You’ll put these to work in the next chapter.

4.2.4 Why Primitive Types Matter

These three types — strings, numbers, and booleans — form the foundation of almost everything you’ll do in Apps Script. They let your programs store and manipulate text, perform calculations, make decisions, interpret spreadsheet data, and build flexible scripts. As you begin working with Google Sheets, you’ll see these types constantly: a cell might hold a string, a number, or something that becomes a boolean when you compare it. Understanding how they behave makes your scripts more reliable and easier to reason about.

4.3 Working With Variables in Apps Script

Now that you’ve seen the basic data types, it’s time to look more closely at how variables actually behave inside your programs — especially once you start pulling information out of a spreadsheet and transforming it with code.

4.3.1 Declaring Variables With let

In modern JavaScript (and Apps Script), the most common way to create a variable is with the keyword let:

let totalCost = 0
let item = "clicker"
let isReady = true

When you declare a variable with let, you’re telling the computer: create a container with this name, and store this value inside it. You can change the value later:

totalCost = totalCost + 526
item = "classroom clicker"
isReady = false

This flexibility is what makes variables powerful — they let your program evolve as it runs.

4.3.2 Choosing Good Variable Names

A variable name should describe what the value represents. Clear names make your code easier to read — especially when you come back to it later.

Good examples: annualDemand, orderingCost, unitCost, isPreferredSupplier.

Less helpful examples: x, data1, thing.

Apps Script doesn’t care what you name your variables, but you will. Good names make your scripts feel organized and intentional.

4.3.3 Variables and Spreadsheet Data

In Apps Script, variables often hold values pulled directly from a Google Sheet. For example:

let sheet = SpreadsheetApp.getActiveSheet()
let cell = sheet.getRange("A1")
let value = cell.getValue()

Here sheet stores a reference to the active sheet, cell stores a reference to cell A1, and value stores whatever is in A1 — say, the annual demand figure for an item.

The first time you run code that accesses spreadsheet data, Apps Script will ask you to confirm that you want to allow it. You can imagine that someone with bad intent could try to trick a person into running code that reads sensitive data or sends email. So every time you run code that uses a feature of the Apps Script environment for the first time, you’ll see a warning about the service being invoked. It can be a little annoying, but it’s there to protect people who paste in code they don’t fully understand. Apps Script asks for approval each time a new service is introduced.

The first time you run the code above, you’ll see a prompt like this:

When you choose “review permissions,” you advance to the next step:

Click the gray “advanced” link on the left side of the window.

Then click the gray “Go to Untitled project (unsafe)” link in the bottom left. This brings up the prompt telling you the code is going to access data from your sheet:

When you click “Continue” you authorize this script to run with the permissions listed. Apps Script won’t prompt you again for those permissions in this project — though if you add code that needs different permissions, you’ll go through a similar approval. Also, sometimes the environment runs the script right after you approve access and sometimes it doesn’t; if you don’t see the expected output, just run it again.

Once the data is in a variable, you can use it like any other value:

console.log("The value in A1 is: ", value)

This is where variables start to feel practical — you’re no longer working with abstract examples, but with real information from your spreadsheet.

4.3.4 Reassigning vs. Redefining

One important detail: you can change the value of a variable declared with let, but you cannot redeclare it in the same scope.

This is allowed:

let onHand = 500
onHand = 460   // OK

This is not:

let onHand = 500
let onHand = 460   // not allowed

Understanding this distinction helps you avoid common errors as your scripts grow.

4.3.5 Why Variables Matter

Variables are the glue that holds your programs together. They let you capture data from a spreadsheet, store intermediate results, build dynamic messages, track progress through a script, and make decisions based on changing values. As you move forward, you’ll use them constantly — sometimes dozens in a single script. Getting comfortable with them now will make everything else feel more natural.

4.4 Working With Text and Numbers

As you begin writing scripts that interact with real spreadsheet data, you’ll spend a lot of time working with two fundamental kinds of information: text and numbers. These show up everywhere in Google Sheets — item names, supplier labels, costs, demand figures, IDs — and knowing how to manipulate them is essential for building useful tools.

4.4.1 Working With Text (Strings)

Text values — strings — are written inside quotes:

let item = "classroom clicker"
let message = "Reorder review complete"

Strings are flexible: you can combine them, break them apart, and insert values into them. Join them with the + operator (this is called string concatenation):

let label = "Reorder report for: " + item + "!"

Or build more readable text with template literals (string interpolation):

let label = `Reorder report for: ${item}`

When you pull text from a spreadsheet, it arrives as a string:

let sheet = SpreadsheetApp.getActiveSheet()
let itemName = sheet.getRange("A2").getValue()   // likely a string

Once it’s in a variable, you can transform it however you like.

4.4.2 Working With Numbers

Numbers in JavaScript represent both whole numbers and decimals:

let unitCost = 18
let caseSize = 20
let casePrice = unitCost * caseSize   // 360

You can perform all the standard arithmetic operations: addition +, subtraction -, multiplication *, division /, and remainder %. JavaScript also gives you Math.sqrt() for square roots — which is exactly what we need to answer the question this chapter has been building toward: how many clickers should the University Store order at a time?

The economic order quantity (EOQ) balances the cost of placing orders against the cost of holding stock. With annual demand, ordering cost, and holding cost stored as variables, the calculation is a single line:

let annualDemand = 3600
let orderingCost = 70
let holdingCost = 2.20
let eoq = Math.sqrt(2 * annualDemand * orderingCost / holdingCost)
console.log(eoq)

This prints about 479. That’s the order size that makes the total of ordering and holding costs as low as possible — at 479 units, the store spends roughly $526 a year placing orders and roughly $526 a year holding stock, the two pulling into balance. Notice that the formula didn’t change; only the names made it readable. That’s the payoff of storing your inputs in well-named variables.

Apps Script reads numeric spreadsheet values as JavaScript numbers, so you can pull a figure straight from a cell and compute with it:

let demand = sheet.getRange("B5").getValue()   // a number
let weekly = demand / 52

4.4.3 Converting Between Text and Numbers

Sometimes you’ll get a value from a spreadsheet that looks like a number but is stored as text, or you’ll need to turn a number into text to build a message. JavaScript gives you simple tools for this.

Convert text to a number:

let num = Number("3600")   // becomes 3600

Convert a number to text:

let text = String(479)   // becomes "479"

Apps Script often handles these conversions automatically, but it’s helpful to know how to do them yourself when needed.

4.4.4 Example: Reading an Item and Writing a Reorder Label

This small script reads an item name from cell A1 and writes a label like “Reorder report for: classroom clicker” into cell B1. Put an item name into A1 first.

function writeReorderLabel() {
  // Get the active sheet
  let sheet = SpreadsheetApp.getActiveSheet()

  // Get a reference to cell A1
  let cellA1 = sheet.getRange("A1")

  // Read the item name from cell A1
  let item = cellA1.getValue()

  // Build the label
  let label = "Reorder report for: " + item

  // Get a reference to cell B1
  let cellB1 = sheet.getRange("B1")

  // Write the label into cell B1
  cellB1.setValue(label)
}

This shows how to read from and write to a single cell. Accessing many cells at once is a bit more advanced and is covered later.

If this is the first time you’ve run a script that accesses worksheet data, you’ll have to allow it — see the permissions walkthrough earlier in this chapter.

4.4.4.1 How it works

• getRange("A1").getValue() pulls the item name from the sheet and stores it in item.
• The script builds a new string by combining "Reorder report for: " with the item.
• setValue() writes the final label into cell B1.

Try putting an item in A1, run the script, and watch the label appear. It’s simple, but it captures the essence of Apps Script: read real data, transform it with JavaScript, and write meaningful results back.

4.4.5 Why This Matters

Working with text and numbers is the backbone of almost every spreadsheet automation — cleaning up item names, formatting labels, calculating totals, generating reports, processing rows of mixed data. Mastering how to read, combine, and transform them turns raw spreadsheet data into something meaningful, and sets the stage for the more advanced scripting ahead.

4.5 Naming Conventions and Best Practices

As your scripts grow beyond a few lines, the names you choose for your variables start to matter a lot. Good naming makes your code easier to read, easier to debug, and easier to return to weeks or months later. Clear, consistent naming is one of the simplest ways to write code that feels clean and professional.

4.5.1 Use Clear, Descriptive Names

A variable name should tell you what the value represents, not how it’s used or what type it is. When you read your code later, the meaning should be obvious at a glance.

Good examples: annualDemand, orderingCost, isApproved, reorderPoint.

Less helpful examples: x, temp, data1, flag.

Descriptive names reduce mental overhead. You don’t have to remember what x means — you can just read the name and keep going.

4.5.2 Follow JavaScript’s camelCase Style

Although other conventions exist, we’ll use camelCase for variable and function names. The first word is lowercase, and each following word starts with a capital letter:

let unitCost = 18
let annualDemand = 3600
let isPreferredSupplier = true

camelCase is the most common naming convention across the JavaScript ecosystem, so following it helps your code feel familiar and consistent.

4.5.3 Start Names With Letters, Not Numbers

Variable names must begin with a letter, underscore, or dollar sign. They cannot start with a number:

let item1 = "clicker"   // valid
let _count = 10         // valid
let 1stItem = 5         // not valid

Sticking to letters for most names keeps things simple.

4.5.4 Avoid Abbreviations Unless They’re Obvious

Shortened names save a few keystrokes but cost clarity. Unless the abbreviation is universally understood, spell it out.

Prefer orderingCost over ordCst, and holdingCost over hldCst. Clear code is almost always better than compact code. (EOQ is fine — it’s universally understood in operations.)

4.5.5 Use Boolean Names That Read Like True/False Statements

Boolean variables should sound like conditions, which makes your code easier to read when you use them in logic. Examples: supplierIsPreferred, hasOpenOrder, isStockedOut, shouldReorder. When you read a line like:

if (shouldReorder) {

…it feels natural and self-explanatory.

4.5.6 Keep a Consistent Style Across Your Script

Consistency matters more than perfection. If you choose a pattern, stick with it: if you write annualDemand, don’t switch to ordering_cost later; if you name one variable unitCost, don’t name another cost_per_unit. A consistent style makes your code feel intentional and easier to navigate.

4.5.7 Why Naming Matters in Apps Script

When you start interacting with spreadsheet data, you’ll often have variables like:

let sheet = SpreadsheetApp.getActiveSheet()
let demandColumn = sheet.getRange("B2:B20").getValues()
let orderingCost = sheet.getRange("E1").getValue()

Clear names help you keep track of what each value represents — especially when you’re juggling multiple ranges, rows, and calculations. Good naming is one of the simplest habits that separates messy scripts from maintainable ones. As your projects grow, you’ll be glad you invested in clarity early. Next, we’ll start making decisions with this data — deciding, for instance, whether an item has dropped low enough to reorder.