When playing around with sheet data, I always find myself using the Fiddler object – some more posts on this below if you are not familiar with it already.
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
One annoying omission is being able to format data when dumping it, but that’s now been added. Here’s how to format the header row
This class – Fiddler can be found in my cUseful library.
Here’s the key, and it’s also on github
1EbLSESpiGkI3PYmJqWh3-rmLkYKAtCNPi1L2YCtMgo2Ut8xMThfJ41Ex
Quickstart
Here’s our starting sheet
Let’s say we want to add a date in column E
function myFunction() {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.insertColumn ("date seen")
.mapRows (function (row) {
row['date seen'] = new Date();
return row;
})
.dumpValues();
}
Not bad
But, it would be better if I could highlight the heading row, and wrap the ones that are too long
function better() {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setHeaderFormat ({
wraps:true,
backgrounds:'yellow',
fontWeights:'bold'
})
.dumpValues();
}
Much better
What can be set
The argument to .setHeaderFormat() can be any of the (plural) properties you can set with the range.setxxxxxs method. Just keep the camel case and lowercase the first letter – for examplerange.setFontWeights becomes fontWeightsrange.setFontColor becomes fontColors note – that singular methods such as .setFontWeight won’t work, so always use the plural method eg .setFontWeights. That’s all folks.