// Sheets · Apps Script

Get unique values from a column in Google Sheets.

How to extract unique values from a column in Google Sheets using Apps Script, including the flat() fix that makes Set deduplication actually work on getValues output.

“I need to pull every distinct value out of a column in Google Sheets without duplicates, and UNIQUE() alone is not cutting it because I need the result in a script.”

The script

copy · paste · trigger

getUniqueColumnValues.gs

Apps Script

// getUniqueColumnValues.gs
// Reads a source column, dedupes it, writes uniques to a target column.
// Adjust SHEET_NAME, SRC_COL, DEST_COL, and START_ROW as needed.

const SHEET_NAME = 'Data';
const SRC_COL    = 1;   // A = 1, B = 2, …
const DEST_COL   = 3;   // write uniques to column C
const START_ROW  = 2;   // skip the header row

function getUniqueColumnValues() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(SHEET_NAME);
  const lastRow = sheet.getLastRow();

  const raw    = sheet.getRange(START_ROW, SRC_COL, lastRow - START_ROW + 1, 1).getValues();
  const flat   = raw.flat();                          // [[a],[b],[a]] → [a,b,a]
  const unique = [...new Set(flat)].filter(String);   // dedupe, drop blanks
  const out    = unique.map(function(v) { return [v]; }); // back to 2D for setValues

  sheet.getRange(START_ROW, DEST_COL, out.length, 1).setValues(out);
  Logger.log('Unique values written: ' + out.length);
}

Generate yours

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

Walkthrough

Why your Set is not deduplicating

getValues() always returns a 2D array, even when you read a single column. A call on 100 rows of column A gives you [["foo"],["bar"],["foo"]] — 100 single-element arrays, not 100 strings. When you feed that directly to new Set(), JavaScript sees 100 distinct array references, so every row survives as unique. The Set does nothing useful.

The fix is one word: .flat(). Call raw.flat() immediately after getValues() and the nested arrays collapse to ["foo","bar","foo"]. Now Set deduplicates on value equality, which is what you wanted. I have watched this bite people who swear their logic is right, because the Logger will happily print [[foo],[bar]] and it looks like a list of strings at a glance.

If you are reading a multi-column range and only want one column, slice first: raw.map(function(row) { return row[0]; }) gets you the same flat array without needing .flat(). Either route works; .flat() is shorter when the range is already one column wide.

Dropping blanks and handling the header

After flattening and deduplicating, filter(String) strips empty strings and cells that came back as empty strings from the sheet. It also quietly drops 0, false, and null if those can appear in your column — if your data includes legitimate zeros you want to keep, swap the filter for .filter(function(v) { return v !== ''; }) instead.

The START_ROW constant handles the header. Set it to 2 to skip row 1, or to 1 if your column has no header. The range height is computed as lastRow - START_ROW + 1 so the script adapts as rows are added without you touching it.

One trap: getLastRow() returns the last row with any content in the entire sheet, not just in your source column. If column A ends at row 50 but column Z has a stray value in row 200, your range will over-read. For tight control, lock the source range to a specific last row, or use getLastRow() on the column directly by checking the column's own content.

Writing the result back and keeping it fresh

setValues() requires a 2D array matching the shape of the target range, which is why unique.map(function(v) { return [v]; }) re-wraps each value into a one-element array before writing. Passing a flat array to setValues() throws an IllegalArgumentException that reads like a shape mismatch — it is.

The script writes to DEST_COL starting at START_ROW. If the unique list shrinks between runs (you removed source rows), the old output is not automatically cleared. Add sheet.getRange(START_ROW, DEST_COL, sheet.getLastRow(), 1).clearContent() before the setValues() call if you need the output column to reflect deletions.

To run this on a schedule, open Extensions > Apps Script, go to Triggers (clock icon), and add a time-driven trigger pointing at getUniqueColumnValues. A daily trigger at, say, 06:00 keeps a deduplicated reference list current without any manual intervention. The first time I set this up for a client's inventory sheet, the trigger ran for three months before anyone noticed it existed — which is the goal.

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

Can I do this without Apps Script, just with a formula?: Yes. =UNIQUE(A2:A) returns distinct values as a spilled array in Sheets. Use Apps Script when you need the result in a variable mid-script, when you want to write it to a specific location on a schedule, or when the downstream logic is more than a formula can handle.
Why does filter(String) drop zeros?: String(0) is '0', which is truthy, so 0 passes the filter fine. What String does drop is the empty string, null, and undefined, because String('') is '' and Boolean('') is false. The only value you lose is a cell that is genuinely empty.
My sheet has 50,000 rows. Will this be slow?: One getValues() call fetches all 50,000 rows in a single API round-trip, which is fast. The JavaScript Set and flat() operations happen in memory and are negligible at that scale. Avoid putting getValues() or setValues() inside a loop — that kills performance by making one API call per row. The script above makes exactly two range calls regardless of row count.
How do I get uniques from multiple columns combined into one list?: Read a multi-column range with getValues(), then flatten the whole 2D array with .flat() — this collapses all cells into one array. Pass that to new Set() and filter as usual. The order will be row-major (all values from row 1, then row 2, and so on), which is normally fine for a deduplicated reference list.