In Header formatting with fiddler we added an extra column as well as some header formatting and ended up with this
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
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(); }
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
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); }
function duplicateRows () { var fiddler = initBetter(); fiddler.insertRows(0,fiddler.getNumRows(),fiddler.getData().slice()) .setData (fiddler.sort("last name")) .dumpValues(); }
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(); }
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
bruce mcpherson is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License. Based on a work at http://www.mcpher.com. Permissions beyond the scope of this license may be available at code use guidelines