Answers

Answers to common Google Sheets, Excel and Apps Script questions

Problems people regularly ask about in public forums, explained in our own words with a complete fix. Each page links to the public question that inspired it, and script answers are unit-tested.

• Q01

  Keep only the newest row per ID (formula)

  A single SORTN formula that returns one row per ID or email, always the most recent one, even when the data comes from two ranges.

• Q02

  Delete older duplicates (script)

  A fast Apps Script that keeps one row per key, the newest by date or by position, and rewrites the sheet in one go instead of deleting rows one by one.

• Q03

  onEdit won't send email

  A function named onEdit can't send email because simple triggers can't use authorized services. Rename it, add an installable trigger, and it works.

• Q04

  Email when a formula result changes

  Edit triggers ignore formula recalculations. Save a snapshot of the column and compare it on a timer to email only what changed.

• Q05

  Email full Form answers

  Google Forms only tells you a response arrived. This short Apps Script emails every answer, with Reply going straight to the respondent.

• Q06

  Static timestamp on edit

  Formula tricks with NOW() can recalculate later. A small onEdit script writes a fixed date-time the first time a cell is filled, including pasted blocks.

• Q07

  Timestamp when a status is chosen

  An onEdit script that records the time in another column only when a dropdown changes to one value, such as Shipped, plus how to reuse it across many files.

• Q08

  Split names with middle names

  TEXTBEFORE and TEXTAFTER formulas that split on the last space, handle one-word names, and keep suffixes like Jr. with the surname.

• Q09

  Highlight rows with duplicate values

  One conditional formatting formula colors the entire row whenever its value in a key column appears more than once. Variants for repeats only and two columns.

• Q10

  XLOOKUP two criteria #N/A

  The multiply-the-conditions XLOOKUP pattern for two criteria, how to tell when numbers stored as text are breaking it, and a Google Sheets equivalent.

• Q11

  Move a ticked row to another tab

  An onEdit script that moves exactly the row you ticked to another tab, instead of sweeping several rows, with safe checks for pastes and unticks.

• Q12

  Import all CSVs from a Drive folder

  An Apps Script that reads every CSV in a Drive folder, keeps a single header row, skips blank lines and writes everything to one tab in a single call.

• Q13

  Daily backup copies

  A short Apps Script that saves a dated copy of your spreadsheet to a Drive folder every night and trashes copies older than the newest 30.