Header formatting with fiddler shows how to use fiddler to easily set formats for your sheet headings and Formatting sheet column data with fiddler covers data formatting options. However there are occasions that you might want to use fiddler to play around with data, or even just give the ability to address data by its column heading rather than its range, but in the end you’ll eventually need a range (or a rangeList) to customize content, formats, formulas or other properties of the sheet. This article shows how to use fiddler to translate columns accessed by name into rangeLists. 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, manipulated in previous formatting articles Formatting sheet column data with fiddler and Header formatting with fiddler.
What are rangelists
These are a relatively new addition to Apps Script, and are essentially a set of disconnected ranges. This allows you an efficient way of, for example applying the same formatting options to a whole set of ranges in one go. For the purposes of this demo, I want to be able to log the A1 notation for each Range in a RangeList, so I’ll use this function.
function logRangeList (rangeList) { return rangeList.getRanges () .map (function (d) { return d.getA1Notation(); }) .join (","); }
fiddler.getRangeList ( [ columnNames ] , [ options ])
- columnNames – a single or array of column names the rangeList should apply to. If omitted, it returns ranges for each column in the data.
- options – an object with these properties and default if omitted.
{ numberOfRows: fiddler.getNumRows(), numberOfColumns:1, rowOffset:0, columnOffset:0 }
here’s some examples, and results
function manualRanges () { var fiddler = new cUseful.Fiddler (SpreadsheetApp.getActiveSheet()); // all the data in a specific column - F2:F7 Logger.log (logRangeList(fiddler.getRangeList ('date seen'))); // all the data in all the columns - A2:A7,B2:B7,C2:C7,D2:D7,E2:E7,F2:F7 Logger.log (logRangeList(fiddler.getRangeList ())); // all the data in a few columns - F2:F7,A2:A7,D2:D7 Logger.log (logRangeList(fiddler.getRangeList (['date seen', 'first name', 'home city']))); // first 2 rows in a few columns - B2:B3,A2:A3 Logger.log (logRangeList(fiddler.getRangeList (['middle name', 'first name'],{ numberOfRows:2 }))); // 3 columns after last name - D2:F7 Logger.log (logRangeList(fiddler.getRangeList ('last name',{ numberOfColumns:3, columnOffset:1 }))); // last 2 columns and last 2 rows - E6:E7,F6:F7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeaders().slice(-2),{ numberOfRows:2, rowOffset:fiddler.getNumRows()-2 }))); }
Fiddler has a few useful functions for finding columns, here’s some more examples of generating ranges using those
function manualRanges2 () { var fiddler = new cUseful.Fiddler (SpreadsheetApp.getActiveSheet()); // columns between middle name and home city - B2:B7,C2:C7,D2:D7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeadersBetween ("middle name","home city")))); // columns between middle name and home city in reverse - D2:D7,C2:C7,B2:B7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeadersBetween ("home city","middle name")))); // columns from last name to end - C2:C7,D2:D7,E2:E7,F2:F7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeadersBetween ("last name")))); // columns from beginning to last name - A2:A7,B2:B7,C2:C7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeadersBetween (null, "last name")))); // first 4 columns - A2:A7,B2:B7,C2:C7,D2:D7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeadersBetween (null, fiddler.getHeaderByIndex(3))))); // last 3 columns in reverse - F2:F7,E2:E7,D2:D7 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeadersBetween (fiddler.getHeaderByIndex(-1), fiddler.getHeaderByIndex(-3))))); }
So far the range have been closed (eg A2:A20), but sometimes you want them to be open (for example B2:B or C2:2)To get those, simply ask for 0 columns to get them all (C2:2) or 0 rows to get them all (B2:B)
function manualRanges3 () { var fiddler = new cUseful.Fiddler (SpreadsheetApp.getActiveSheet()); // now using full ranges (not based on data in sheet) // entire columns for all data - A2:A,B2:B,C2:C,D2:D,E2:E,F2:F Logger.log (logRangeList(fiddler.getRangeList (null , { numberOfRows:0 }))); // rest of rows starting at second last column - E2:2 Logger.log (logRangeList(fiddler.getRangeList (fiddler.getHeaderByIndex(-2) , { numberOfColumns:0, numberOfRows:1 }))); }
Using rangeLists
Although you can use fiddler to do most data manipulation and formatting, there will be times you need or prefer to access the sheet directly. Here’s a couple of usage examples.
Formulas
Let’s fill the middlename column with a formula, using autofill. We can use fiddler.getRangeList() to get all the ranges without needing to specifically know where they are
function formula () { var fiddler = new cUseful.Fiddler (SpreadsheetApp.getActiveSheet()); // get ranges for the firstName , middleName and last Name var [first,middle,last] = fiddler.getRangeList (["first name","middle name","last name"], { numberOfRows:1 }) .getRanges(); // make a formula and apply it middle.setFormula ("=" + first.getA1Notation () + "&" + last.getA1Notation()); // fill down the rest using the first row as a model var destination = fiddler.getRangeList ("middle name").getRanges()[0]; middle.autoFill (destination, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES); }
The result
Conditional rules
Now, apply a new conditional rule to this sheet – make the cell pink if any of home city or last seen in country end with “n”
function conditional () { var fiddler = new cUseful.Fiddler (SpreadsheetApp.getActiveSheet()); // get ranges for last seen in country and home city var rangeList = fiddler.getRangeList (["last seen in country","home city"]); // make a conditional var rule = SpreadsheetApp.newConditionalFormatRule() .whenTextEndsWith("n") .setBackground("pink") .setRanges (rangeList.getRanges()) .build(); var sheet = fiddler.getSheet(); // preserve any existing ones var rules = sheet.getConditionalFormatRules(); rules.push (rule); sheet.setConditionalFormatRules(rules); }
There is 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