// Sheets · Apps Script

Fill a formula down an entire column in Google Sheets.

Use Apps Script to fill a formula down a column in Google Sheets without breaking relative references — stop at the last data row, not row 1000.

“I want to programmatically fill a formula down a column in Google Sheets, stopping at the last row that has data, and have row references shift correctly the way drag-fill does.”

The script

copy · paste · trigger

fillFormulaDown.gs

Apps Script

// Fill a formula down column C, rows 2–lastRow
// Uses R1C1 notation so references shift per row (like drag-fill)
function fillFormulaDown() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Last row with data in column A (the anchor column)
  var lastRow = sheet.getRange('A:A').getValues()
    .filter(function(row) { return row[0] !== ''; }).length;

  if (lastRow < 2) return; // nothing to fill

  // R1C1: RC[-2] = same row, 2 cols left; RC[-1] = same row, 1 col left
  var formula = '=RC[-2]*RC[-1]';

  var range = sheet.getRange(2, 3, lastRow - 1, 1); // C2:C{lastRow}
  range.setFormulaR1C1(formula);
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why setFormula breaks what you expect

The first time I hit this, I spent twenty minutes debugging why every cell in column C had the same output. The culprit: Range.setFormula() stamps one literal A1 string into every cell in the range. If you pass '=A2*B2', every row gets exactly =A2*B2, not =A3*B3, not =A4*B4. There is no shifting. It behaves like Ctrl+V paste, not drag-fill.

setFormulaR1C1() is the fix. R1C1 notation describes references relative to the cell being written. RC[-2] means 'same row, two columns to the left.' When Apps Script writes that into C2, it resolves as A2; into C3, it resolves as A3. The formula text you pass is identical for every row; the engine handles the per-row translation at write time.

Finding the last data row without burning quota

A common mistake is passing the sheet's maximum row count (sheet.getMaxRows(), often 1000 or more) as the fill height. That writes formulas into hundreds of empty cells, slows the sheet down, and makes last-row detection harder later.

The snippet reads all values from column A with a single getValues() call, then counts non-empty entries with filter. One API call, no loop, and it stops exactly where your data stops. If your anchor column is not A, change 'A:A' to match. The sheet.getLastRow() built-in is a shorter alternative, but it returns the last row with any content anywhere in the sheet, which can surprise you if another column extends further than the anchor.

Adapting the range and formula to your sheet

getRange(2, 3, lastRow - 1, 1) breaks down as: start row 2 (skip header), column 3 (C), height of lastRow minus 1 rows, width 1. Adjust the column number and start row to match your layout.

For non-adjacent references, R1C1 uses absolute positions too: R2C1 is always $A$2. Mix them when you need one locked anchor and one shifting reference, for example '=R2C1*RC[-1]' to multiply every row by a fixed rate in A2. That is the pattern I reach for most often when building calculated columns against a lookup table.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does setFormula put the same formula in every cell instead of adjusting the row number?: setFormula writes a literal A1-notation string to every cell in the range with no shifting. Use setFormulaR1C1 with relative R1C1 references (like RC[-1]) so the reference resolves differently per row, the same way dragging a formula down the column would.
How do I stop the fill at the last row that has data instead of the end of the sheet?: Read your anchor column with getRange('A:A').getValues(), filter out empty strings, and use the resulting array length as your row count. Avoid getMaxRows() — it returns the sheet capacity (often 1000+), not your data boundary.
Can I use this on a named sheet instead of the active sheet?: Yes. Replace getActiveSheet() with getSheetByName('YourSheetName'). getActiveSheet() is fine for a manually triggered function but will target the wrong tab if the script runs from a trigger while a different sheet is open.
What is the R1C1 syntax for an absolute reference mixed with a relative one?: Use a row or column number instead of R or C alone. R2C1 locks to row 2, column 1 (which is $A$2 in A1 notation). RC[1] stays relative. So '=R2C1/RC[-1]' divides the value in A2 by the cell one column to the left of the current cell, on the current row.

// one good script a week