Fiddler and rangeLists

Apps Script (intermediate level)posted on 13th November 2018

 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 thos
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);
                
}

and here's the result





There's more fiddler stuff here
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