Unique values with data fiddler - Desktop Liberation

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

Page Content hide

1 Manipulating unique values.

2 Results

3 The code

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

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)

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