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. 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.