Reading and writing sheets Updated November 2026

Delete rows

sheet.deleteRow(5) removes row 5 and sheet.deleteRows(5, 3) removes rows 5 to 7. To delete rows matching a condition, read the data, then loop from the last row to the first and delete as you go, so earlier row numbers do not shift under you. For large sheets, filter the array and rewrite the sheet instead.

Syntax

sheet.deleteRow(rowPosition) | sheet.deleteRows(rowPosition, howMany)

Part	What it is
`rowPosition`	1-based row number to delete
`howMany`	Number of consecutive rows to remove

Examples

const sheet = SpreadsheetApp.getActiveSheet();
sheet.deleteRow(2);

Result: Row 2 is gone and everything below moves up

const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 3).getValues();
for (let i = data.length - 1; i >= 0; i--) {
  if (data[i][2] === "Cancelled") sheet.deleteRow(i + 2);
}

Result: Every row whose column C says Cancelled is removed

Looping backward keeps the remaining row numbers valid.

const all = sheet.getDataRange().getValues();
const kept = all.filter((row, i) => i === 0 || row[2] !== "Cancelled");
sheet.clearContents();
sheet.getRange(1, 1, kept.length, kept[0].length).setValues(kept);

Result: Same outcome in two writes instead of one delete per row; much faster on big sheets

const last = sheet.getLastRow();
const max = sheet.getMaxRows();
if (max > last) sheet.deleteRows(last + 1, max - last);

Result: Trailing empty rows removed

Common mistakes

Only every other matching row is deleted: you looped forward; after a delete the next row moves up into the index you just checked, so loop backward.
Those rows are out of bounds: row number computed from an array index without adding the header offset, or a delete past getMaxRows.
Cannot delete all rows: a sheet must keep at least one row; clear content instead when nothing should remain.
Deleting 2,000 rows one by one hits the 6 minute limit: filter the array and rewrite the sheet, or delete contiguous blocks with deleteRows.

Practice Delete rows

These drills check your answer on live data, free, no account needed.

Items in stockEach item looks like `{ name, qty }`. Return the items that are in stock, meaning `qty` is greater than 0, in their original order. A negative `qty` is a back order, not stock.
Remove duplicates by a fieldReturn the items with duplicates removed, where two items are duplicates when `item[key]` is the same value. Keep the first item for each value, in the original order. Values are compared the way a `Set` compares them, so the number `1` and the string `"1"` are different.

Questions

How do I delete rows where a cell is empty?

Same backward loop with the condition data[i][0] === "" (getValues returns empty strings for blanks).

Is there an undo for script deletions?

No undo from the script, but File, Version history in Sheets restores an earlier version of the whole spreadsheet.

Loop through rowsLoop through rows in Google Sheets with Apps Script: read the data once with getValues, loop the 2D array with for, for...of or forEach, then write back.
getLastRowFind the last used row in Google Sheets with Apps Script: getLastRow for the whole sheet, how it treats blanks and formulas, and the last row of one column.
Remove duplicatesRemove duplicate rows in Google Sheets with Apps Script: range.removeDuplicates for whole rows or chosen columns, or a JavaScript Set filter on one column.
Clear and format cellsClear and format Google Sheets cells with Apps Script: clear content or formats, set background and font color, bold, number and date formats, borders.