Header formatting with fiddler shows how to use fiddler to easily set formats for your sheet headings. here’s how to do the same for column formats. This is quite a long read, as there’s a whole bunch of new functionality.
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
In Header formatting with fiddler we added an extra column as well as some header formatting and ended up with this
Adding column formats
This is just as straight forward as heading formats, and follows the same pattern for describing which formats to be set. This example wants to set some formatting in the “date seen” column, as well as the first and last name columns.
function evenBetter() {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setColumnFormat ({
backgrounds:"red",
fontColors:"white"
},"date seen")
.setColumnFormat ({
backgrounds:"lightgray",
fontStyles:"italic"
},["first name","last name"])
.dumpValues();
}
here it is
Clearing formats
Columns can be cleared of all formatting by providing a column name, or an array of column names and a null format
function clearColumn() {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setColumnFormat (null,"date seen")
.dumpValues();
}
Individual format properties can be cleared by setting them to null
function clearing() {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setColumnFormat ({
backgrounds:null
},"first name")
.dumpValues();
}
And all formats can be cleared like this
function clearAll() {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.clearColumnFormats ()
.dumpValues();
}
Inserting columns
Let’s go back to this
and insert a column, including all the formatting chained together.
function insertColumnAll () {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setHeaderFormat ({
wraps:true,
backgrounds:'yellow',
fontWeights:'bold'
})
.setColumnFormat ({
backgrounds:"red",
fontColors:"white"
},"date seen")
.setColumnFormat ({
backgrounds:"lightgray",
fontStyles:"italic"
},["first name","last name"])
.insertColumn ("middle name","last name")
.dumpValues();
}
That’s probably ok – there’s a new column with no formatting
But you can format that column even before it exists (it only gets written to the sheet with dumpValues). Just move all the similar formatting to after the insert.
function insertColumnAllBetter () {
new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setHeaderFormat ({
wraps:true,
backgrounds:'yellow',
fontWeights:'bold'
})
.setColumnFormat ({
backgrounds:"red",
fontColors:"white"
},"date seen")
.insertColumn ("middle name","last name")
.setColumnFormat ({
backgrounds:"lightgray",
fontStyles:"italic"
},["first name","middle name","last name"])
.dumpValues();
}
and you get this
Deleting columns and tidying unused formats First of all, let’s move all the format stuff to it’s own function to avoid repeating it for each example. I’ve also added a default column format (the one with the blue fontColors, that’ll be applied as a base format to all columns.
function init () {
return new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setHeaderFormat ({
wraps:true,
backgrounds:'yellow',
fontWeights:'bold'
})
.setColumnFormat ({
backgrounds:null,
fontColors:'blue',
fontStyles:null
})
.setColumnFormat ({
backgrounds:"red",
fontColors:"white"
},"date seen")
.setColumnFormat ({
backgrounds:"lightgray",
fontStyles:"italic"
},["first name","middle name","last name"]);
}
Let’s delete the “middle name” column
function deleteColumn () {
var fiddler = init();
fiddler.filterColumns (function (header) {
return header !== "middle name";
})
.dumpValues();
}
oops – Fiddler doesn’t clean up column E (by design, as it doesn’t know whether that’s a format you’ve set manually and want to keep).
Normally though, you’ll want to clean away formats left behind by deleting columns. It’s not Fiddler default behavior (to maintain compatibility with previous versions), but you can enable it.
function deleteColumnBetter () {
var fiddler = init();
fiddler.filterColumns (function (header) {
return header !== "middle name";
})
.setTidyFormats (true)
.dumpValues();
}
Now it tidies up
Since we’ll always be tidying up we may as well set that in out formatting init function
function initBetter () {
return new cUseful.Fiddler (SpreadsheetApp.getActiveSheet())
.setHeaderFormat ({
wraps:true,
backgrounds:'yellow',
fontWeights:'bold'
})
.setColumnFormat ({
backgrounds:null,
fontColors:'blue',
fontStyles:null
})
.setColumnFormat ({
backgrounds:"red",
fontColors:"white"
},"date seen")
.setColumnFormat ({
backgrounds:"lightgray",
fontStyles:"italic",
fontColors:null
},["first name","middle name","last name"])
.setTidyFormats (true);
}
Inserting rows
Let’s duplicate the rows and sort them.
function duplicateRows () {
var fiddler = initBetter();
fiddler.insertRows(0,fiddler.getNumRows(),fiddler.getData().slice())
.setData (fiddler.sort("last name"))
.dumpValues();
}
The columnFormats are all inherited properly – nothing to see here.
Deleting rows
Deleting rows without .setTidyFormats(true) will leave behind the formatting of the deleted rows.
function deleteRows () {
var fiddler = init();
fiddler.filterRows (function (row) {
return row['last name'] !== "doe";
})
.dumpValues();
}
So we’ll use the initBetter() function which includes .setTidyFormat(true)
function deleteRowsBetter () {
var fiddler = initBetter();
fiddler.filterRows (function (row) {
return row['last name'] !== "doe";
})
.dumpValues();
}
Formatting only
It’s possible to apply formats only (leave the data untouched). This might be handy if you are not using fiddler to manipulate data (for example you might have some formulas on the sheet etc) but you still want to apply formats without having to worry about column numbers and so on. To do that, you simply process your sheet as normal, but this time use dumpFormats() rather than dumpValues() Take a look at this example, where there’s strange formatting and formulas embedded in a few columns.
Loading and dumping from fiddler would normally convert the formulas to values, headings would be assigned to columns with blank heading rows, and the regular formats would be applied.
function load () {
initBetter().dumpValues();
}
would do this
.. but lets’ say that you just wanted to apply the formats and leave everything else alone.
function formatOnly () {
initBetter().dumpFormats();
}
would do this, leaving the formulas and blank headings intact
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.
Chaining
As with all fiddler methods, these are chainable so everything can be done in one statement. The formats can be set/cleared at any time in the life of a fiddler, but they are only actually applied to the sheet once , during the dumpValues() method. Remember that fiddler only deals in values – so formulas are not preserved (not yet anyway, but watch this space). There’s more fiddler stuff 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