Formatting sheet column data with fiddler

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

Page Content hide

1 Quickstart

2 Adding column formats

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 example

range.setFontWeights becomes fontWeights

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

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.

Desktop Liberation

The definitive resource for Google Apps Script and Microsoft Office automation