// Sheets · Apps Script

Append a row to the top of a sheet in Google Sheets.

How to insert a new row at position 1 in Google Sheets using Apps Script so the newest data always appears at the top, instead of piling up at the bottom like appendRow does.

“I want to add a row of data to the top of my sheet so the most recent entry appears first, but appendRow keeps writing to the bottom.”

The script

copy · paste · trigger
insertRowAtTop.gs
Apps Script
// Insert a new row at the top of the active sheet
// and write values into it — newest entry always row 1.
function prependRow(values) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // Push every existing row down by one
  sheet.insertRowBefore(1);

  // Write the values array into the new first row
  var range = sheet.getRange(1, 1, 1, values.length);
  range.setValues([values]);
}

// Example call: log a timestamp + event name
function logEvent() {
  var now = new Date();
  prependRow([now, 'form_submit', 'user_42']);
}
Generate yours

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

Walkthrough

Why appendRow won't do what you want

appendRow(values) scans the sheet for the last row that contains data and writes below it. That behavior is fine for a growing log you read from the bottom, but if you want newest-first — a feed, an audit trail, a form-response sheet sorted by recency — it's immediately wrong. There is no parameter to change which end it appends to.

The fix is two calls: insertRowBefore(1) to open a blank row at position 1, shifting every existing row down by one, then getRange(1, 1, 1, values.length).setValues([values]) to fill it. The first time I hit this I spent twenty minutes reading the appendRow docs looking for an offset argument that doesn't exist.

What insertRowBefore actually does to your data

insertRowBefore(n) inserts one blank row above row n. Passing 1 means the insert lands above whatever was previously row 1, so your old row 1 becomes row 2, row 2 becomes row 3, and so on. Formulas with absolute references (=$A$1) stay anchored to the new row 1 — which is now blank — so if you have a header row with fixed-reference formulas, account for that. Relative references shift correctly.

setValues expects a two-dimensional array: an array of rows, each row being an array of cell values. That is why the call wraps values in an extra pair of brackets: [values]. Pass a flat array and you get a type error that reads 'Incorrect range width' — confusing until you see it once.

One quota note: insertRowBefore triggers a structural change on the spreadsheet, which counts toward the Sheets API write quota (300 write requests per minute per project on the free tier). If you are prepending in a tight loop — say, processing a batch of 500 webhook events — batch the inserts or accumulate rows in memory and write them in one shot with insertRowsBefore(1, count) followed by getRange(1, 1, count, cols).setValues(matrix).

Targeting a specific sheet by name

getActiveSheet() works when the script runs from a bound editor or a menu click, because the user has a tab focused. In a time-driven trigger or a standalone script, getActiveSheet() returns whatever sheet happens to be active for the owning account at that moment — which is non-deterministic.

Use getSheetByName('Log') instead. It returns null if the sheet doesn't exist, so a one-line guard (if (!sheet) return;) prevents a silent null-dereference that surfaces as a cryptic 'Cannot read property insertRowBefore of null' in the execution log. I keep a small getOrCreateSheet helper in a utils file for exactly this reason.

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
Does insertRowBefore shift down a frozen header row?
Yes. insertRowBefore(1) pushes the frozen row down to row 2 and your new data lands in row 1. If you want to preserve a header at row 1, call insertRowBefore(2) instead and write to getRange(2, 1, 1, values.length).
Can I insert multiple rows at once instead of calling insertRowBefore in a loop?
Yes. sheet.insertRowsBefore(1, n) opens n blank rows above row 1 in a single API call. Then write the full matrix with getRange(1, 1, n, cols).setValues(matrix). This is significantly faster for batches and uses one write quota unit instead of n.
Will a form-submit trigger work with this pattern?
Yes, but the trigger passes an event object, not a plain array. Pull the values you want from e.namedValues or e.values, build your array, and pass it to prependRow. The form's own response sheet still gets the appended row at the bottom; this pattern applies to a separate destination sheet you control.
Why does setValues throw 'Incorrect range width' even though my array looks right?
setValues requires a 2D array: [[val1, val2, val3]]. If you pass a flat array [val1, val2, val3], the method sees a range of height 1 and width 1 but receives 3 values, and throws that error. Wrap the array: setValues([yourArray]).

Related scripts