I wondered if there might be more functional programming, more declarative approach to the problem of fiddling around with spreadsheet data. Using the API to insert rows and worrying about column numbers and ranges can quickly become spaghetti, so here’s what I came up with.
 
Page Content hide
4 Main features

V8 version

All my libraries are gradually being migrated to v8 versions and into their own library. The legacy versions will still be available but will be frozen.
 

Identifiying v8 versus legacy

Previously all libraries were prefixed by a c as in cUseful. The v8 libraries will be prefixed by bm, as in bmFiddler
 

v8 version

Here’s the key for bmFiddler, and it’s also on Github

13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU

Legacy version

The legacy version of  Fiddler can be found in my cUseful library.
 
Here’s the key, and it’s also on Github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

Main features

  • All data is loaded from values returned from a sheet and manipulated as objects where the keys are the column headings.
  • Values with no column headings are also supported
  • Rows and columns can be filtered and mapped without the need for any looping or needing to keep track of column positions.
  • It all happens in memory so it’s very fast.

 

Some examples

 

The best way to demonstrate is by example, so here’s a fairly comprehensive list of examples.
 
If you prefer, there’s a video of this post below.
 

 

The test data

I’m using a public sheet of about 800 major airports.
 
 
 
 
and loading the data like this.
 
 
// get some data
  var sheetValues = SpreadsheetApp
    .openById('1h9IGIShgVBVUrUjjawk5MaCEQte_7t32XeEP1Z5jXKQ')
    .getSheetByName('airport list')
    .getDataRange()
    .getValues();
 
 
The result will be written to another workbook to check the results of each example. If you are playing around using these examples, then create your own workbook and amend as required.
 
 
 // where to write the tests.. change this some sheet of your own.
  var fiddleRange =  SpreadsheetApp
    .openById('1najG4ARQ2JsqEGxT8kKz_IeYWCuBzzpmyU0R9oq3I58')
    .getSheetByName('fiddleTest')
    .getDataRange();
 

Creating a fiddler

 

Assuming you are using the cUseful library, you can get a fiddler like this.
 
 
// get a fiddler
  var fiddler = new cUseful.Fiddler();
 

Loading the test data

 

The first thing to do is to load the values. Although this is initially designed for spreadsheet data, any array data can be used.
 
  fiddler.setValues (sheetValues);
 
Data can be retrieved in its object literal format like this
 
  var data = fiddler.getData();
 
Here’s what the first data row looks like, so now your sheet data can be treated like any other array of JavaScript objects.
 
{
  "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?

 

Although this is best used with data with headers, it is possible to manage data with no headers too. If you set the hasHeaders property to false, the data will be refactored, using A1 style column labels for data property names.
 
 
// try without headers
  fiddler.setHasHeaders(false);
  Logger.log(JSON.stringify(fiddler.getData().slice(0,2)));
 
 
Here’s what the first couple of rows look like
 
 
[{
  "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

 

Of course, all that allows structured access to sheet data, but how about modifying data, adding columns, rows etc.
 
Patching data in the object returned by getData() can easily be done just by changing it.
 
For example, this change would be reflected when the data was finally committed back to a sheet
 
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

Here I can use one of the properties passed by filterRows(), to detected if there are any non-blank values in the row, and keep them.
 
 
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.

 method  someValue  called for  every
 filterRows  an object literal where the keys are each header, and the values are the values for this current row.  row
 filterColumns  the heading for this column  column
 mapRows  an object literal where the keys are each header, and the values are the values for this current row.  row
 mapColumns  an array of values for every row in the current column.  column
 mapColumn  the value of the current row for this column  row
 mapHeaders  the name of the current header  column
 selectRows  the value of the current row for this column  row

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.  

 property  contains
 name  the name of the column
 data  all the data in the fiddler as returned by fiddler.getData()
 headers  an object whose keys are the column headers, and values are their position to output them at
 rowOffset  the row index for this row starting at 0
 columnOffset  the column index for this row starting at 0
 values  an array of all the values for this column/row
 row  an object literal with all the values for this row
 fiddler  the fiddler object that is managing this callback

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

Here’s the key for bmFiddler, and it’s also on Github

13EWG4-lPrEf34itxQhAQ7b9JEbmCBfO8uE4Mhr99CHi3Pw65oxXtq-rU

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

More on this topic available here