ScriptdrillappendRow
sheet.appendRow(["a", 1, new Date()]) writes one row directly after the last row that has content, with the array items placed from column A. For many rows, write them in one call with sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, rows[0].length).setValues(rows).
Syntax
sheet.appendRow(valuesArray)| Part | What it is |
|---|---|
valuesArray | A 1D array; each item goes into the next column starting at A |
Examples
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Log");
sheet.appendRow([new Date(), "Order received", 149.99]);Result: A new row at the bottom of Log with a timestamp, a note and a number
const rows = [["Ana", "ana@x.com"], ["Ben", "ben@x.com"]];
sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 2).setValues(rows);Result: Two rows added in one write
Faster than calling appendRow in a loop.
sheet.appendRow(["Total", "", "=SUM(C2:C" + sheet.getLastRow() + ")"]);Result: A row with a formula in column C summing everything above it
Common mistakes
- The row lands below a far-away stray value: appendRow uses getLastRow, so clear stray cells or write at your own computed row.
- appendRow(1, 2, 3) throws: it takes one array argument, appendRow([1, 2, 3]).
- Appending inside a loop of 500 rows takes a minute: build a 2D array and write once with setValues.
- Dates appear as text: pass a Date object, not a formatted string, so the cell stays a real date.
Practice appendRow
These drills check your answer on live data, free, no account needed.
- Zip two arraysPair up the items of `a` and `b` by position: `[[a[0], b[0]], [a[1], b[1]], ...]`. Stop at the end of the shorter array. Items can be any value, including `null`, `0` and `false`.
- Format moneyFormat `amount` as money in the given ISO currency code (like `"USD"` or `"JPY"`) and locale, the way `Intl.NumberFormat` does with `style: "currency"`. Each currency keeps its usual number of decimals, and each locale its own grouping.
Questions
Does appendRow skip blank rows in the middle?
It writes after the last row with any content, so a blank row in the middle is ignored but a value far below everything moves the append point down.
How do I append to a specific sheet or another spreadsheet?
Get that sheet first: SpreadsheetApp.openById(id).getSheetByName("Data").appendRow([...]).
Related
- 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.
- setValue and setValuesWrite data to Google Sheets with Apps Script: setValue for one cell, setValues for a 2D array that must match the range size, and the dimension error fix.
- Send email from a sheetSend email from Google Sheets with Apps Script: MailApp.sendEmail and GmailApp.sendEmail with HTML body, cc and attachments, loop a list of recipients, quota.
- onEdit triggerUse the onEdit trigger in Google Apps Script: the event object with range, value and oldValue, limiting it to one sheet or column, timestamps, installable.