A loop that calls getValue and setValue on individual cells inside a for loop feels natural to write, but each call is a separate round trip to the Sheets service, and a few hundred rows can turn a task that should take a second into one that runs for minutes or times out.
This tutorial applies a ten percent discount to a Pricing sheet by reading the entire used range once with getDataRange().getValues(), modifying the resulting in-memory array, and writing it all back with a single setValues call.
Because the transformation happens on a plain JavaScript array rather than on live Range objects, the loop that computes each discounted price runs entirely in the script's own memory and never touches the Sheets API until the final write.
The function also looks up column positions by header name instead of hardcoded indexes, so the Pricing sheet's columns can be reordered without breaking applyDiscountToPriceColumn as long as the header text stays the same.
Need this built? Hire a Google Apps Script developer →
Sheet / project setup
| Sheet | Range | Role |
|---|---|---|
| Raw Import | A2:D | Read once with getValues |
| Cleaned | A2:D | Written once with setValues |
| Transform | trim + uppercase col B | In-memory map |
What it does
applyDiscountToPriceColumn reads the whole Pricing sheet as a two-dimensional array, walks every row to compute a discounted price and a last-updated timestamp, and writes the modified array back in one setValues call.
Prerequisites
getDataRange().getValues() returns the sheet's used range as a plain array of arrays, and setValues on that same range writes an equally-shaped array back in a single Sheets API operation regardless of how many rows it contains.
Walkthrough
No special authorization beyond the script's normal spreadsheet access is needed; this pattern works with any range the script already has permission to read and write.
Edge cases
Locating priceColumnIndex and discountColumnIndex with headerRow.indexOf keeps the code resilient to column reordering, and the function throws early if either required header is missing rather than writing into the wrong column silently.
Testing
A missing Price or Discounted Price header throws immediately with a descriptive message, which surfaces a sheet-structure problem before any calculation happens rather than after a partial, confusing write.
Hardening
Run applyDiscountToPriceColumn against a copy of the Pricing sheet first and spot-check a handful of rows by hand to confirm the ninety percent multiplier and rounding produce the values you expect.
Variations
The same batch pattern scales to sheets with tens of thousands of rows without hitting execution time limits nearly as quickly as a per-cell loop would, which is the main reason to default to getValues and setValues even for scripts that start out small.
Full code: applyDiscountToPriceColumn()
Run applyDiscountToPriceColumn against a copy of the sheet first and confirm the Price and Discounted Price headers exist before trusting the output.
function applyDiscountToPriceColumn() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Pricing');
var range = sheet.getDataRange();
var values = range.getValues();
var headerRow = values[0];
var priceColumnIndex = headerRow.indexOf('Price');
var discountColumnIndex = headerRow.indexOf('Discounted Price');
var updatedColumnIndex = headerRow.indexOf('Last Updated');
if (priceColumnIndex === -1 || discountColumnIndex === -1) {
throw new Error('Pricing sheet is missing required Price or Discounted Price columns.');
}
var now = new Date();
for (var i = 1; i < values.length; i++) {
var price = Number(values[i][priceColumnIndex]) || 0;
values[i][discountColumnIndex] = Math.round(price * 0.9 * 100) / 100;
if (updatedColumnIndex !== -1) {
values[i][updatedColumnIndex] = now;
}
}
range.setValues(values);
}- Line 3: Reads the sheet's entire used range in a single call.
- Line 7: Locates the price column by header name, not a hardcoded index.
- Line 10: Throws early if a required header column is missing.
- Line 17: Computes the discounted price entirely in memory.
- Line 19: Stamps a Last Updated timestamp only if that column exists.
- Line 23: Writes the entire transformed array back in one setValues call.
Deploy this example
- 01
Open Apps Script
In the bound spreadsheet: Extensions → Apps Script. For standalone projects, create one at script.google.com and link your Sheet by ID.
- 02
Paste and save
Add a .gs file, paste the code below, rename constants at the top (sheet names, column letters, API property keys), then save.
- 03
Authorize once
Run the main function from the editor. Accept OAuth scopes when prompted — triggers cannot run until authorization succeeds once.
- 04
Add the trigger
Triggers → Add trigger → choose the handler function and event (time-driven, on edit, or on form submit). Delete test triggers before production.
Before you run: batch a sheet transformation
- 1Full range read once with getDataRange().getValues() rather than looped getValue calls
- 2All transformation logic applied to the in-memory array, not live Range objects
- 3Required header columns located by name with indexOf, not hardcoded positions
- 4Missing header columns treated as a hard error before any calculation runs
- 5Single setValues call used to write the transformed array back
- 6Spot-checked results against a hand calculation on a copy of the sheet
- 7Row count considered against execution time limits for very large sheets
Frequently asked questions
Each getValue or setValue call is its own request to the underlying Sheets service, so a loop over a thousand rows makes roughly a thousand separate round trips instead of the two total calls this batch pattern uses.
It returns the sheet's used range, which is everything from A1 to the last row and column containing data or formatting, so leftover formatting in cells beyond your real data can make the returned range larger than expected.
Header-based lookup keeps the function working if someone inserts a new column or reorders existing ones, as long as the header text itself is not changed, which is far more common in practice than the column count staying fixed forever.
Yes, call getRange with explicit row and column bounds instead of getDataRange, and the same read-transform-write approach applies to any subset of the sheet.
setValues will throw an error if the array's dimensions do not match the range it is being written to, so any logic that adds or removes elements from a row must also adjust the target range's dimensions accordingly.
There is no fixed row limit in the API itself, but very large single calls consume more of the script's memory and execution time budget, so extremely large sheets sometimes benefit from chunking the write into a few large batches instead of one enormous one.