Header formatting with fiddler

Apps Script (easy level)posted on 10th November 2018

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.

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

That's all folks.

For more like this, see Google Apps Scripts snippets. Why not join our forumfollow the blog or follow me on twitter to ensure you get updates when they are available. 

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.






Comments