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
- A functional approach to fiddling with sheet data
- Unique values with data fiddler
- More sheet data fiddling
- Fiddling with text fields that look like dates
- A functional approach to updating master sheet
- Populating sheets with API data using a Fiddler
- Header formatting with fiddler
- Formatting sheet column data with fiddler
- Styling Gmail html tables
- Sorting Google Sheet DisplayValues