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. 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
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")); } ResultsA. 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 codeThe 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. 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. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Google Apps Scripts snippets >