Fiddler is a way to handle spreadsheet data in a functional way, as described in A functional approach to fiddling with sheet data
This class – Fiddler can be found in my cUseful library.
Here’s the key, and it’s also on github
Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j
There’s a video of how to use it below
Manipulating unique values.
One common operation on spreadsheet data is to deal with unique values. Fiddler has a couple of new methods to reduce spreadsheet data to unique values. For a full description of the many things that Fiddler can do, see A functional approach to fiddling with sheet data
method | arguments | returns | purpose |
.filterUnique | columnNames – an single or array of column names. If missing all columns are used for filtering.
keepLast – whether to use the first or last occurrence of a row when duplicate is found compareFunction – an optional function to compare values. should return a boolean – true if values are equal |
fiddler for chaining | Reduces the data held in the fiddler to get rid of duplicate rows. A duplicate row is where the values in all the columns mentioned in the columnNames argument are exactly the same for a given row. If columnNames is blank, then the entire row needs to be identical for there to be a duplicate
default is |
.getUniqueValues | columnName – the columnName to return the values of
compareFunction – an optional function to compare values. should return a boolean – true if values are equal |
An array of values | Returns the unique values in the column specified.
default is |
Example with custom compare functions. These are sheets focused, but fiddler can be used with any data arrays. The input data is
function removeDuplicates() { var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o'); var sheet = ss.getSheetByName("remove-duplicates"); // this one will remove duplicates rows using a fiddler // https://ramblings.mcpher.com/gassnippets2/unique-values-with-data-fiddler?preview_id=5996&preview_nonce=32bc8981f9&preview=true // get the values, remove duplicate rows - a duplicate defined as same first/last name var fiddler = new cUseful.Fiddler(sheet); // A. get all the unique values in a given column Logger.log (fiddler.getUniqueValues("first name")); // B. get all the unique values in a given column, with a custom compare function Logger.log (fiddler.getUniqueValues("first name", function (a,b) { return a.toLowerCase() === b.toLowerCase(); })); // C. remove duplicates using multiple columns // and write back to a different sheet (creating it if necessary) fiddler.filterUnique (["first name","last name"]) .dumpValues (ss.getSheetByName ("dupremove") || ss.insertSheet ("dupremove")); // D.remove duplicates, with a custom compare function // and write back to a different sheet (creating it if necessary) var fiddler = new cUseful.Fiddler(sheet) .filterUnique (["first name","last name"] ,false , function (a,b) { return a.toLowerCase() === b.toLowerCase(); }).dumpValues (ss.getSheetByName ("customdupremove") || ss.insertSheet ("customdupremove")); // E. remove duplicates, with a custom compare function , and keep the last occurrence // and write back to a different sheet (creating it if necessary) var fiddler = new cUseful.Fiddler(sheet) .filterUnique (["first name","last name"] ,true , function (a,b) { return a.toLowerCase() === b.toLowerCase(); }).dumpValues (ss.getSheetByName ("customdupremovelast") || ss.insertSheet ("customdupremovelast")); }
Results
A. List of unique names[john, jane, mary, tom, John, michael] B. List of unique name, where custom compare function is supplied to ignores case.[john, jane, mary, tom, michael] C. create a new sheet of rows with duplicate first/last name combinations removed
D. create a new sheet with with duplicate first/last name combinations removed, and a custom function to ignore case
E. create a new sheet with with duplicate first/last name combinations removed, and a custom function to ignore case, keeping the last occurrence (instead of the first)
The code
The code is in the cUseful library. Here’s the key, and it’s also on github
Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j
More on this topic available here
- A functional approach to fiddling with sheet data
- Unique values with data fiddler
- More sheet data fiddling
- Fiddling with text fields that look like dates
- A functional approach to updating master sheet
- Populating sheets with API data using a Fiddler
- Header formatting with fiddler
- Formatting sheet column data with fiddler
- Styling Gmail html tables
- Sorting Google Sheet DisplayValues
Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.