More sheet data fiddling

In A functional approach to fiddling with sheet data and Unique values with data fiddler I showed a way of working with sheet data without bothering too much about column numbers and all that stuff that goes wrong when things change.  It turned out that this has been so useful to me that I now use it for all sheet operations in all projects, and also client side for conveniently manipulating any structured data.

This class - Fiddler can be found in my cUseful library.

Here's the key, and it's also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j


One thing that's been bothering me though, is that since its sertValues method takes an array,   it still needed a pattern like this to load data
var fiddler = new cUseful.Fiddler().setValues (SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mysheet").getDataRange().getValues());

and since we need a range to be able to write data, the pattern is like this
sheet.clearContent();
fiddler.getRange(sheet.getDataRange()).setValues(fiddler.createValues());

It occurred to me that since I usually populate from a sheet, play around with the data, and write it back, I could do with a few shortcuts. Now if you pass a sheet to the constructor it will automatically load it and remember which sheet it came from.
var fiddler = new cUseful.Fiddler (sheet);

and to write data back the same sheet,
fiddler.dumpValues();

or to a different sheet
fiddler.dumpValues(anotherSheet);

This means that I can pretty much chain together an entire sheet reformat , something like this
new cUseful.Fiddler (sheet)
  .filterRows (function (row) {
    return row.value !== "rubbish";
  })
  .dumpValues();

or to another sheet like this
new cUseful.Fiddler (sheet)
  .filterRows (function (row) {
    return row.value === "rubbish";
  })
  .dumpValues(rubbishSheet);

or write an array of objects to a sheet like this
new cUseful.Fiddler().setData(objects).dumpData (sheet);

For a lot more on fiddler, and videos too, see A functional approach to fiddling with sheet data and Unique values with data fiddler
 

For more like this, see Google Apps Scripts snippets. Why not join our forumfollow 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