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

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/google-apps-scripts-snippets-2/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