[GAS Beginner Series #13] Get All Spreadsheet Data at Once: Range.getValues() and 2D Arrays Explained

When automating Google Sheets with Google Apps Script (GAS), the getLastRow() and getLastColumn() methods we covered in our previous lessons (Parts 11 and 12) allow you to automatically detect the last row and column containing data—no matter how large your sheet grows.

Once you know the exact dimensions of your table, the next logical step is to load all of that data straight into your program for processing.

You might immediately think of Range.getValue(), which we introduced in Part 5. However, relying on getValue() to read an entire table will quickly lead you straight into a performance trap.

In this tutorial, we will explore Range.getValues() (plural), one of the most critical methods in Google Apps Script that lets you retrieve entire tables in a single batch, speeding up execution times dramatically!

We will break down two-dimensional (2D) arrays—often the single biggest hurdle for beginners—step by step, share practical code you can use immediately, and troubleshoot common pitfalls. Let’s dive in!

1. Why You Shouldn’t Loop getValue(): The Power of getValues()

First, let’s address a fundamental question: why is getValues() necessary instead of just looping getValue()?

Imagine you have a table with 100 rows and 4 columns, totaling 400 cells. To retrieve all this data, there are two primary approaches:

  1. Call getValue() repeatedly, reading one cell at a time 400 times.
  2. Call getValues() once, reading all 400 cells simultaneously in a single batch.

In Google Apps Script, communicating with Google Sheets across the network is a relatively slow and resource-heavy operation.

  • Looping getValue() 400 times: This sends 400 separate round-trip requests between GAS and Google Sheets. It can take several seconds to tens of seconds, and as your dataset expands, your script risks exceeding the 6-minute execution limit and throwing a timeout error.
  • Using getValues() once: You send a single request saying, “Please give me all the data within this range!” Because it only requires one round trip, the operation finishes in under 0.1 seconds.

A Everyday Analogy to Picture It

  • getValue() (Singular): Imagine grocery shopping where you pick up an apple, run to the checkout counter to pay, run back to the aisle for a carrot, run back to checkout again, and repeat. That would be wildly inefficient!
  • getValues() (Plural): You fill your shopping cart with everything you need first, then pay for everything at once at the register.

When handling real-world datasets in GAS, the golden rule is always: “Retrieve data in a single batch using getValues().”

2. Quick Comparison: getValue() vs. getValues()

Here is a side-by-side comparison of the singular and plural methods:

Feature Range.getValue() (Singular) Range.getValues() (Plural)
Target Range Single cell (e.g., A1) Multiple cells / Range (e.g., A1:D5)
Return Type Single value (String, Number, Date, etc.) 2D Array (Entire table data)
Speed Very slow for many cells (can cause timeouts) Extremely fast (one round-trip API call)
Best Use Case Reading a single specific cell value Reading entire tables or data blocks

3. Essential Concept: What Is a “2D Array”?

When you execute getValues(), the retrieved data is returned to your program as a “two-dimensional array” (2D array).

While this is often considered a stumbling block for beginners, it is actually quite straightforward once you visualize the structure.

1D Arrays vs. 2D Arrays

  • Standard Array (1D Array): A simple flat list of items arranged in a line: ["Fried Chicken", "Japanese Sweets", "Deli Dish"].
  • 2D Array: A nested structure where arrays are placed inside another array. It directly mirrors the rows and columns of your spreadsheet inside your code.
[
  ["Item Name", "Category", "Price"],           // Row 1 data (Array)
  ["Fried Chicken", "Hot Chef", 313],            // Row 2 data (Array)
  ["Walnut Daifuku", "Japanese Sweets", 162]    // Row 3 data (Array)
]

Think of it as an outer pair of brackets [ ] wrapping individual inner brackets [ ], where each inner array represents one horizontal row.

4. The #1 Beginner Gotcha: Zero-Based Indexing

When extracting a specific item from a 2D array, beginners often get tripped up by how positions are counted.

In Google Sheets, rows and columns start counting from 1 (Row 1, Column 1).

However, programming languages like JavaScript and Google Apps Script strictly count starting from 0 (zero-based index).

Mapping Spreadsheet Positions to Array Indices

Let’s look at how spreadsheet positions map to JavaScript’s array[rowIndex][columnIndex] syntax:

Spreadsheet Position Content 2D Array Syntax Index Calculation
Row 1, Column 1 (Cell A1) "Item Name" data[0][0] Row 1 (1 – 1 = 0), Col 1 (1 – 1 = 0)
Row 1, Column 2 (Cell B1) "Category" data[0][1] Row 1 (1 – 1 = 0), Col 2 (2 – 1 = 1)
Row 2, Column 1 (Cell A2) "Fried Chicken" data[1][0] Row 2 (2 – 1 = 1), Col 1 (1 – 1 = 0)
Row 3, Column 3 (Cell C3) 162 data[2][2] Row 3 (3 – 1 = 2), Col 3 (3 – 1 = 2)

💡 Mental Trick to Avoid Off-by-One Errors

Always remember: “Subtract 1 from the spreadsheet row and column numbers!” Keep this simple rule in mind, and you will never get lost.

5. Step 1: Set Up the Practice Sheet (Secoma Item List)

Let’s get some hands-on experience with getValues()!

We will build a practice inventory sheet inspired by popular snacks and deli items from Secoma (Seicomart, Hokkaido’s beloved convenience store chain).

Steps

  1. Create a new Google Sheet and name it “Product Inventory”.
  2. Rename the active sheet tab at the bottom to “Favorites List”.
  3. Enter the following sample data:
A B C
1 Item Name Category Price (Tax Incl.)
2 Fried Chicken Hot Chef 313
3 Walnut Daifuku Japanese Sweets 162
4 Creamy Chicken Katsu Bowl Hot Chef 594
5 Plain Yogurt Retail Partner 128

6. Step 2: Read Entire Table Data with getValues()

Now that our sheet is ready, let’s write a script to fetch the entire table in one call.

By combining getLastRow() and getLastColumn() from Parts 11 and 12, our script will dynamically adjust even as rows or columns are added or removed over time!

1. Paste the Code

Open Extensions > Apps Script from the spreadsheet menu bar, clear out the default template, and paste the following snippet:

function getAllSecomaData() {
  // 1. Get the active spreadsheet and sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  
  // 2. Automatically detect the last row and column containing data
  var lastRow = sheet.getLastRow();      // Returns 5
  var lastCol = sheet.getLastColumn();   // Returns 3
  
  // 3. Define the full data range starting at Row 1, Column 1 (A1:C5)
  //    getRange(startRow, startColumn, numRows, numColumns)
  var range = sheet.getRange(1, 1, lastRow, lastCol);
  
  // 4. Retrieve all data in a single batch (stored as a 2D array)
  var allData = range.getValues();
  
  // 5. Output the retrieved 2D array to the execution log
  Logger.log(allData);
}

2. Code Breakdown Line by Line

  • var lastRow = sheet.getLastRow();
    Automatically finds the bottom-most row with data (row 5 in this case).
  • var lastCol = sheet.getLastColumn();
    Automatically finds the right-most column with data (column 3, which is Column C).
  • sheet.getRange(1, 1, lastRow, lastCol)
    Selects the target range using numerical coordinates. It instructs GAS to: “Start at Row 1, Column 1, and extend 5 rows down and 3 columns across.”
  • var allData = range.getValues();
    Reads all cell values in that selected range in a single operation and assigns them to the variable allData.

3. Inspect the Execution Log

Save the script and run getAllSecomaData.

If you see output like the following in the Execution log, your script ran successfully!

Info [
  ["Item Name", "Category", "Price (Tax Incl.)"],
  ["Fried Chicken", "Hot Chef", 313.0],
  ["Walnut Daifuku", "Japanese Sweets", 162.0],
  ["Creamy Chicken Katsu Bowl", "Hot Chef", 594.0],
  ["Plain Yogurt", "Retail Partner", 128.0]
]

As you can see, the table structure from Google Sheets has been cleanly transferred directly into your code as a 2D array!

7. Step 3: Extracting Specific Values from the 2D Array

Now that the entire dataset is safely stored inside allData, let’s pull out individual data points directly from memory.

Add the following logging operations and run the script again:

function getSpecificData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var allData = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();

  // 1. Extract the header text "Item Name" (Cell A1)
  Logger.log("■ Cell A1: " + allData[0][0]);

  // 2. Extract "Walnut Daifuku" (Cell A3)
  //    Spreadsheet Row 3, Col 1 ➡ Array: [3-1][1-1] = [2][0]
  Logger.log("■ Cell A3: " + allData[2][0]);

  // 3. Extract the price for "Creamy Chicken Katsu Bowl": 594 (Cell C4)
  //    Spreadsheet Row 4, Col 3 ➡ Array: [4-1][3-1] = [3][2]
  Logger.log("■ Cell C4: " + allData[3][2] + " yen");
}

Execution Log Output

Info ■ Cell A1: Item Name
Info ■ Cell A3: Walnut Daifuku
Info ■ Cell C4: 594 yen

Once you load data using getValues(), accessing any cell in memory happens instantly without making additional network calls back to Google Sheets.

8. Five Common Traps Beginners Face (And How to Fix Them)

Here are five frequent errors and misunderstandings that beginners run into when working with getValues(). Review these now so you can troubleshoot quickly!

Trap 1: Accidentally Using getValues() on a Single Cell

  • The Mistake: Calling getValues() when you only want a single cell value like A1.
  • What Happens: Instead of returning a clean string like "Item Name", it returns a nested 2D array: [["Item Name"]]. Consequently, equality checks like if (data == "Item Name") will fail unexpectedly.
  • The Fix:
    • Single cell ➡ Use getValue() (singular)
    • Multiple cells / tables ➡ Use getValues() (plural)

Trap 2: Plural vs. Singular Typos

  • The Mistake: Forgetting the trailing s and writing getValue() on a multi-cell range like A1:C5.
  • What Happens: GAS will not throw an error; instead, it silently returns only the top-left cell’s value (cell A1). This often leaves beginners wondering why the rest of their table data went missing.

Trap 3: TypeError: Cannot read properties of undefined

  • The Mistake: Querying allData[5][0] when your table only has 5 rows.
  • The Cause: The 5th row corresponds to index allData[4]. Index allData[5] actually targets the nonexistent 6th row, which returns undefined. Attempting to access [0] on undefined throws a TypeError.
  • The Fix: Remember that array indices are always Row Number – 1.

Trap 4: Raw Values vs. Formatted Text (Currency, Dates)

  • What Happens: Even if a cell displays formatted text like “¥162” or “2026/09/20” on the screen, getValues() retrieves raw numerical values like 162 or JavaScript Date objects like Sun Sep 20 2026 ....
  • The Cause: getValues() pulls underlying raw cell values, not their formatted display text.
  • The Fix: If you need the exact string formatted as visible on the sheet, use getDisplayValues(), which we will cover in an upcoming lesson.

Trap 5: Swapping Arguments in getRange()

  • The Mistake: Writing sheet.getRange(lastRow, lastCol, 1, 1) by accident.
  • The Correct Order: getRange(startRow, startColumn, numRows, numColumns). Keep this mental model handy: “Where do I start (startRow, startColumn), and how big is the area (numRows, numColumns)?”

9. Summary and What’s Next

In this lesson, we unpacked Range.getValues() for batch data retrieval and thoroughly demystified 2D arrays.

Key Takeaways

  1. getValues() retrieves multi-cell table ranges in a single efficient call.
  2. Minimizing communication between GAS and Google Sheets creates lightning-fast scripts.
  3. Data is returned as a 2D array (nested row and column arrays).
  4. Array indices are zero-based: subtract 1 from the sheet’s row and column numbers.
  5. Pair getLastRow() and getLastColumn() with getValues() for robust, self-adjusting ranges.

Once you know how to pull full datasets into memory, the natural next step is writing calculated and updated data back to Google Sheets all at once!

In Part 14, we will cover the counterpart to today’s method: Range.setValues().

Mastering the cycle of Fetch with getValues() ➡ Process in GAS ➡ Write with setValues() is the foundational best practice for real-world automated workflows. Stay tuned!