Auto-format new rows to match the table style

Northwind’s Projects sheet is a shared workspace, and shared sheets drift. Someone adds a row at the bottom, the borders stop, the status column loses its dropdown, and the table slowly stops looking like a table. Asking everyone to copy the formatting by hand never sticks.

This automation does it for them. Whenever a row is inserted into the Projects sheet, the script copies the formatting and data validation from the row directly above it onto the new row. Borders, fonts, number formats and the status dropdown all carry down automatically, so the table stays consistent no matter who is editing it.

What you’ll need

• A Google Sheet with a tab named exactly Projects.
• At least one fully formatted data row already in place — the script copies from the row above, so the table needs a style to copy.
• Edit access to the spreadsheet, so you can install the trigger.

The script

// The tab this automation watches. Rows added elsewhere are ignored.
const TARGET_SHEET = 'Projects';

/**
 * Runs on every spreadsheet change. When a row is inserted into the
 * Projects sheet, it copies formatting and data validation from the
 * row above so the new row matches the table style.
 *
 * @param {Object} e The onChange event object Apps Script passes in.
 */
function onChangeFormat(e) {
  // 1. Only react to row insertions — ignore edits, deletes, formatting, etc.
  if (e.changeType !== 'INSERT_ROW') return;

  // 2. Only act on the Projects sheet; leave every other tab alone.
  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  if (sheet.getName() !== TARGET_SHEET) return;

  // 3. Need at least a header row and one formatted data row to copy from.
  const lastRow = sheet.getLastRow();
  if (lastRow < 3) return;

  // 4. The source is the row above the new one; the target is the new row.
  const lastColumn = sheet.getLastColumn();
  const sourceRange = sheet.getRange(lastRow - 1, 1, 1, lastColumn);
  const targetRange = sheet.getRange(lastRow, 1, 1, lastColumn);

  // 5. Copy the visual formatting (borders, fonts, number formats, fills).
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);

  // 6. Copy the data validation rules (dropdowns, checkboxes, etc.).
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
}

How it works

1. onChangeFormat receives the change event. It checks e.changeType and returns immediately unless the change was an INSERT_ROW — edits, deletes and column changes are all ignored.
2. It checks the active sheet’s name. Only the Projects tab is formatted; inserting rows anywhere else does nothing.
3. It reads the last row. If the sheet has fewer than three rows, there is no formatted data row to copy from yet, so the script stops.
4. It builds two ranges spanning the full width of the table: sourceRange, the row above the insertion, and targetRange, the newly inserted row.
5. The first copyTo call pastes only the formatting — borders, fonts, fills and number formats — without touching the cell values.
6. The second copyTo call pastes only the data validation, so any dropdowns or checkboxes on the source row are recreated on the new row.

Example run

Before — a teammate inserts a blank row 8 under the formatted table:

Project	Owner	Status
Atlas rebrand	Priya	Done
Harbour app	Sam	In progress
(new row 8 — plain, no borders, no Status dropdown)

After the insert fires the trigger, row 8 inherits the table style instantly:

Project	Owner	Status
Atlas rebrand	Priya	Done
Harbour app	Sam	In progress
(row 8 — bordered, correct fonts, Status dropdown ready)

The person just types into a row that already looks right.

Wire it up

The onChange event needs an installable trigger — a simple onChange function name will not fire on its own:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose function onChangeFormat, event source From spreadsheet, and event type On change.
4. Save and approve the authorisation prompt.

Watch out for

• onChange fires for many change types — inserts, deletes, formatting — so the early changeType check is essential. Without it the script would run on every edit.
• The new row inherits whatever the row above looks like. If someone leaves the bottom row of the table in an odd state, the next inserted row copies that oddness down.
• Inserting several rows at once still copies from a single source row. The lastRow is the bottom of the new block, so only the bottom new row gets formatted reliably; for bulk inserts, copy the format down by hand.
• INSERT_ROW events do not always carry the exact range that was inserted, so the script assumes the insertion is at the bottom of the table. Rows inserted in the middle of the table are not handled.
• The trigger runs under the editor who installed it. Other collaborators can still insert rows and the trigger fires, but it does so with that one person’s permissions.