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
function  (a,b) { return a===b ; }

 .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
function  (a,b) { return a===b ; }

 

Example with custom compare functions. These are sheets focused, but fiddler can be used with any data arrays. The input data is

Screenshot 2017-08-24 at 11.30.40

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

Screenshot 2017-08-24 at 11.33.38

D. create a new sheet with with duplicate first/last name combinations removed, and a custom function to ignore case


Screenshot 2017-08-24 at 12.12.00

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)

Screenshot 2017-08-24 at 12.09.44

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

For more like this see Google Apps Scripts Snippets

Why not join our forum, follow the blog or follow me on Twitter to ensure you get updates when they are available.