Unique values with data fiddler

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

function removeDuplicates() {
  var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
  var sheet = ss.getSheetByName("remove-duplicates");
  
  // this one will remove duplicates rows using a fiddler
  // http://ramblings.mcpher.com/Home/excelquirks/gassnips/uniquefiddler
  // 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

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. 

You want to learn Google Apps Script?
Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.


Comments