Fiddler – A functional approach to fiddling with sheet data

V8 version

Identifiying v8 versus legacy

v8 version

Legacy version

Main features

Some examples

The test data

// get some data
  var sheetValues = SpreadsheetApp
    .openById('1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ')
    .getSheetByName('airport list')
    .getDataRange()
    .getValues();

 // where to write the tests.. change this some sheet of your own.
  var fiddleRange =  SpreadsheetApp
    .openById('1najG4ARQ2JsqEGxT8kKz_IeYWCuBzzpmyU0R9oq3I58')
    .getSheetByName('fiddleTest')
    .getDataRange();

Creating a fiddler

// get a fiddler
  var fiddler = new cUseful.Fiddler();

Loading the test data

  fiddler.setValues (sheetValues);

  var data = fiddler.getData();

{
  "name": "Port Moresby Jacksons International Airport",
  "latitude_deg": -9.44338035583496,
  "longitude_deg": 147.220001220703,
  "elevation_ft": 146,
  "iso_country": "PG",
  "iso_region": "PG-NCD",
  "municipality": "Port Moresby",
  "iata_code": "POM"
 }

What if no column headers?

// try without headers
  fiddler.setHasHeaders(false);
  Logger.log(JSON.stringify(fiddler.getData().slice(0,2)));

[{
  "A": "name",
  "B": "latitude_deg",
  "C": "longitude_deg",
  "D": "elevation_ft",
  "E": "iso_country",
  "F": "iso_region",
  "G": "municipality",
  "H": "iata_code"
 }, {
  "A": "Port Moresby Jacksons International Airport",
  "B": -9.44338035583496,
  "C": 147.220001220703,
  "D": 146,
  "E": "PG",
  "F": "PG-NCD",
  "G": "Port Moresby",
  "H": "POM"
 }]

Modifying data

fiddler.getData()[0].municipality = 'Port Moresby area'; 

But two of the design criteria are that iteration is handled for you as per functional style, and that data item are addressed by their property names – not their position. Here are some examples of how all that works.

 

Committing data back to the sheet

 

At some point, you’ll want to write that data back to the sheet (or to a different one). For these examples, I’m using this function to clear the sheet and to write the values back to a given range.

 

The fiddler deals only in data values, not formulas – so that means that any formulas in the original data will not be written back – just the values. You can still, of course, use the fiddler to play around with the data, but remember that if you need to write it back – its just the data that will be committed.

 

Note that fiddler provides a getRange() method to calculate the output range required of its data, taking account of whether there is a header etc, based on the start position of a given range. The fiddler’s createValues() method will convert the JavaScript object array back into the format for writing to a sheet.

 

 

function showFiddler (fiddlerObject , outputRange) {
  
  // clear and write result 
  outputRange
  .getSheet()
  .clearContents();
  
  fiddlerObject
  .getRange(outputRange)
  .setValues(fiddlerObject.createValues());
}

 

Adding an array of objects to a fiddler

 

If you want to use your own array of objects, instead of using setValues(valuesfromasheet) you can use setData(your own array of objects). Writing that to a sheet it will convert it and create headers if they are needed.

 

// can also set data via an object rather than values
  fiddler.setData([
    {name:'john',last:'smith'},
    {name:'jane',last:'doe'}
  ]);
 
  // write to a sheet and take a look
  showFiddler (fiddler , fiddleRange);
  return;

 

 

which will be written as

 

 

 

More likely though, you’ll have used setValues() to populate the fiddler. I can simply reset the contents of the existing fiddler by putting the original values I read from the sheet back in like this

 

 //--- set the data back to original
  fiddler.setValues(sheetValues);

Filtering rows

 

This can be used to delete rows. A function you supply is called for every row in the data. You should return true if the row is to be kept, or false if it is to be removed. The row argument is a JavaScript object containing the data for the current row. The properties argument has lots of other stuff, which I’ll detail later in this post, and which might be useful for more complex operations.

 

This example reduces the data set to only airports in USA

 

 

fiddler.filterRows (function (row,properties) {
    return row.iso_country === "US";
  });
  
  // write to a sheet and take a look
  showFiddler (fiddler , fiddleRange);

 

 

Filtering columns

 

Entire columns can be filtered in a similar way.

 

 

fiddler.filterColumns (function (name,properties) {
    return name !== 'latitude_deg' && name !== 'longitude_deg';
  });

 

 

 

Mapping rows

 

You can change the values in a row using mapRows(), which will call your function for every row in the data. This time your function should return the row of data sent. Any modifications you make to that row will be committed back to the data. You can even send back an entire new row if you want.

 

This example modifies the content of one column with the contents of another

 

 

fiddler.mapRows(function (row,properties) {
    row.municipality += ('(' + row.iso_country + ')' );
    return row;
  });

 

 

 

Mapping Headers

 

You can change the column headers (the data property names will be automatically refactored)  using mapHeaders(). Your function will be called for each header and you should return the name or a modified version of it. This example changes any headers that have iso_ in them.

 

 

 fiddler.mapHeaders (function(name,properties) {
    return name.replace('iso_','');
  });

 

Mapping columns

 

The values in an entire column can be changed in one function with mapColumns(). In this case, your function is called for every column in the data, passing an array of values for every row in that column. This example changes the entire column values from feet to meters, then renames the column using mapHeaders(). Note how these functions can be chained together. The values array should be returned as is, or with any required changes.

 

 

fiddler.mapColumns (function (values,properties) {
    return properties.name === "elevation_ft" ? 
      values.map(function(d) { return d * 0.3048; }) : values;
  })
  .mapHeaders(function (name, properties) {
      return name.replace ("elevation_ft" , "elevation_meters");       
  });

 

 

 

 

Inserting Rows

 

Rows can be inserted by providing the index (starting at 0) before which they should be inserted. The first argument is the index before which it should be inserted (if missing it’s appended), and the second is the number of rows to insert.

 

// insert 2 at the beginning
  fiddler
  .insertRows(0,2)
  
  // insert 3 at the 5th position
  .insertRows(4,3)
  
  // insert 1 at the end
  .insertRows();

 

Deleting blank rows

fiddler.filterRows (function (row, properties) {
    return properties.values.some(function(d) { 
      return d !== '';
    });
  });

Inserting columns

This is similar to insertRows, except that that new column name should be provided, along with the name of the column before which it should be inserted.  

// insert one before country
  fiddler
  .insertColumn ('country' , 'iso_country')
  
  // insert one at the end
  .insertColumn ('elevation_meters');

Moving columns

This is actually similar to insert column. You simply need to provide the column name and where to move it before. This example moves a column to the end, then moves another one just before it.  

fiddler
  .moveColumn ('latitude_deg')
  .moveColumn ('longitude_deg','latitude_deg');

Copying and mapping a column

Columns can be copied by specifying the name of the column to copy, a new name,  and the name of the column to position it in front of. This example first copies the elevation_ft column to a new column called elevation_meters. It then converts the values to meters by using mapColumn(). Your function will be called for each row in the elevation_meters column, and you should return either the value received or a new value for it. This is similar to the mapColumns() method, except it is for a single column and the iteration through the rows is done for you.

fiddler
  .copyColumn ('elevation_ft' , 'elevation_meters' , 'elevation_ft')
  .mapColumn ('elevation_meters',function (value , properties) {
        return value * 0.3048;
  });

Inserting rows with values

There is a 3rd argument available to insertRows(). This should be an array of objects that will be the data for the inserted rows. This example is adding two rows at the beginning. As much or as little of the data properties can be supplied as you wish. Any missing ones will be shown as blank.  

fiddler
  .insertRows (0,2,[{name:'unknown 1'},{name:'unknown 2'}]);

Copying data to inserted rows.

It’s very straightforward to duplicate existing data into newly inserted rows. This example shows how to insert 2 new rows and duplicate the data from the existing first 2 rows

fiddler
  .insertRows (0,2,fiddler.getData().slice(0,2));

Matching and selecting

This one expects the name of a column (as in mapColumn), and will call your function for each row in that column passing its value. Your function should return true if the row should be selected. It returns an array of positions that match. This is handy for a kind of index/match/vlookup type operation. This example will find all the rows that are in Canada and then use that to log their municipalities.

// do a match - all rows where country is canada
  var matches = fiddler.selectRows('iso_country',function (value , properties) {
    return value === "CA";
  });
  
  // show the result from a different column
  Logger.log (matches.map(function(d) {
    return fiddler.getData()[d].municipality;
  }));

the logged result

[Edmonton, Halifax, Ottawa, Quebec, Montreal, Vancouver, Winnipeg, London, Calgary, Victoria, St. John's, Toronto]

Inserting a column at a specific place.

By design, all column manipulation is done by named header, but there may be some reason that you want to specifically add a column at a particular place, irrespective of where the other columns are – for example in the first column. You can do this as follows. This example inserts a new first column, then populates it with a timestamp.

 // insert a column at a specific place - the beginning
  // and add a timestamp
  fiddler
  .insertColumn ('first column' , fiddler.getHeaders()[0])
  .mapColumn('first column', function (value,properties) {
    return new Date().getTime();
  });

Notice the timestamp shows that each operation in the snapshot was completed within a millisecond. This shows how blisteringly fast this method of spreadsheet manipulation is, compared to trying to do it with the spreadsheet API. In fact, the operation on the entire worksheet was completed in under 10 milliseconds.  

Using the properties argument.

Pretty much up till now we’ve used just the first argument passed to the iteration functions. The second argument, properties, contains some useful stuff for more complex operations. This example uses insertColumn to create a new column, then uses mapColumn to set the value of each row in that column, basing the contents on a different property in that row.

 fiddler
  .insertColumn ('high' , 'elevation_ft')
  .mapColumn('high',function (value,properties) {
    return properties.row.elevation_ft > 1500 ? (
      properties.row.elevation_ft > 3000 ? 'really' : 'quite')  : 'not';
  });

Your iteration function will receive a callback like this (someValue, properties), where someValue varies with the method as below.

Your iteration function will receive a callback like this (someValue, properties), where properties have the following contents (not all of which will be relevant for each iteration type). Using this gives handy shortcuts to the current row/column and can provide access to all the data structures for the entire dataset if more complex manipulations are needed.  

Some new stuff

I’ve just introduced a couple of new methods. filterUnique() and getUniqueValues(). The examples below should explain what they do.

function ft () {
  
  
  // simulate spreadsheet data
  var fiddler = new cUseful.Fiddler();
  
  // you can populate a fiddler like this
  //fiddler.setValues (
  //  SpreadsheetApp.openById(id)
  //  .getSheetByName(name)
  //  .getDataRange()
  //  .getValues() 
  //);
  
  // but I'll just simulate data for the test
  var testData = [
    ['name','id','score'],
    ['john','a',100],
    ['mary','b',200],
    ['john','c',300],
    ['terry','d',200]
  ];
  
  
  // filter out duplicate names, keep the first one.
  // [[name, id, score], [john, a, 100.0], [mary, b, 200.0], [terry, d, 200.0]]
  Logger.log(fiddler
             .setValues (testData)
             .filterUnique ('name')
             .createValues());  
   
  // filter out duplicate names, keep the last one.
  // [[name, id, score], [mary, b, 200.0], [john, c, 300.0], [terry, d, 200.0]]
  Logger.log(fiddler
             .setValues (testData)
             .filterUnique ('name',true)
             .createValues());  
  
  // add a duplicate name and id, but different score
  testData.push (['john','c',400]);
  
  // filter out duplicates where name & id match, keep last one
  //  [[name, id, score], [john, a, 100.0], [mary, b, 200.0], [john, c, 300.0], [terry, d, 200.0]]
  Logger.log(fiddler
             .setValues (testData)
             .filterUnique (['name','id'])
             .createValues()); 
  
  // add a couple of complete duplicate rows
  testData.push (['john','c',400]);
  testData.push (['terry','d',200]);
  // filter out completely duplicated rows
  // [[name, id, score], [john, a, 100.0], [mary, b, 200.0], [john, c, 300.0], [john, c, 400.0], [terry, d, 200.0]]
  Logger.log(fiddler
             .setValues (testData)
             .filterUnique ()
             .createValues()); 
  
  // or as a json object
  //[{score=100.0, name=john, id=a}, {score=200.0, name=mary, id=b}, 
  // {score=300.0, name=john, id=c}, {score=400.0, name=john, id=c}, 
  // {score=200.0, name=terry, id=d}]
  Logger.log(fiddler
             .setValues (testData)
             .filterUnique ()
             .getData()); 
  
  // get unique values
  fiddler.setValues (testData);
  
  // [john, mary, terry]
  Logger.log(fiddler.getUniqueValues("name"));
  // [a, b, c, d]
  Logger.log(fiddler.getUniqueValues("id"));
  // [100.0, 200.0, 300.0, 400.0]
  Logger.log(fiddler.getUniqueValues("score"));
  
  // you would write to a spreadsheet like this
  //  var sh = SpreadsheetApp.openById(id)
  //  .getSheetByName(name)
  // clear it if you want
  //  sh.clearContents();
  // write it out
  //  fiddler.getRange(sh.getDataRange())
  //  .setValues(fiddler.createValues());
  
}

For further fiddler, features see Sorting Google Sheet DisplayValues and Unique values with data fiddler

The code

v8 version

The tests referred to in the examples are available on GitHub too.

More on this topic available here