A functional approach to fiddling with sheet data

I wondered if there might be a 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. 

This class - Fiddler can be found in my cUseful library.

Here's the key, and it's also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j


Here are the 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 retrevieved 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 items are addressed by their property names - not their position. Here's 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 new 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 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 has 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());
  
}

The code

The code is in the cUseful library, and also below.

Here's the key, and it's also on github

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

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


/**
* this is a utility for messing around with 
* values obtained from setValues method 
* of a spreadsheet
* @contructor Fiddler
*/
var Fiddler = function () {
  
  var self = this;
  var values_, 
      headerOb_ , 
      dataOb_=[],
      hasHeaders_ = true,
      functions_;
  
  
  /**
   * these are the default iteration functions
   * for the moment the do nothing
   * just here for illustration
   * properties take this format
   * not all are relevant for each type of function
   * .name the name of the column
   * .data all the data in the fiddle
   * .headers the header texts
   * .rowOffset the row number starting at 0
   * .columnOffset the column number starting at 0
   * .fiddler this object
   * .values an array of values for this row or column
   * .row an object with all the properties/values for the current row
   */
  var defaultFunctions_ = {

    /**
     * used to filter rows
     * @param {object} row the row object
     * @param {object} properties properties of this  row
     * @return {boolean} whether to include
     */    
    filterRows: function (row, properties) {
      return true;
    },
    
    /**
     * used to filter columns
     * @param {string} heading the heading text 
     * @param {object} properties properties of this  column
     * @return {boolean} whether to include
     */
    filterColumns:function (heading , properties) {
      return true;
    },
    
     /**
     * used to change objects rowwise
     * @param {object} row object 
     * @param {object} properties properties of this row
     * @return {object} modified or left as is row 
     */
    mapRows: function (row , properties) {
      return row;
    },
    
    /**
     * used to change values columnwise
     * @param {[*]} values the values for each row of the column
     * @param {object} properties properties of this column
     * @return {[*]|undefined} values - modified or left as is 
     */
    mapColumns: function (values, properties) {
      return values;
    },
    
    /**
     * used to change values columnwise in a single column
     * @param {*} value the values for this column/row
     * @param {object} properties properties of this column
     * @return {[*]|undefined} values - modified or left as is 
     */
    mapColumn: function (value, properties) {
      return value;
    },
    
    /**
     * used to change header values
     * @param {string} name the name of the column
     * @param {object} properties properties of this column
     * @return {[*]|undefined} values - modified or left as is 
     */
    mapHeaders: function (name, properties) {
      return name;
    },
    
    /**
     * returns the indices of matching values in a column
     * @param {*} value the values for this column/row
     * @param {object} properties properties of this column
     * @return {boolean} whether it matches 
     */
    selectRows: function (value , properties) {
      return true;
    }
    

  }; 
  
  // maybe a later version we'll allow changing of default functions
  functions_ = defaultFunctions_;

  /// ITERATION FUNCTIONS
  /**
   * iterate through each row - given a specific column
   * @param {string} name the column name
   * @param {function} [func] optional function that shoud true or false if selected
   * @return {Fiddler} self
   */
  self.selectRows = function (name, func) {
    
    var values = self.getColumnValues(name);
    var columnIndex = self.getHeaders().indexOf(name);
    var result = [];
    
    // add index if function returns true
    values.forEach (function(d,i) {
      if ((checkAFunc (func) || functions_.selectRows)(d, {
        name:name,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:i,
        columnOffset:columnIndex,
        fiddler:self,
        values:values,
        row:dataOb_[i]
      })) result.push(i);
    });
      
    return result;
  };


  /**
   * iterate through each row - nodifies the data in this fiddler instance
   * @param {function} [func] optional function that shoud return a new row if changes made
   * @return {Fiddler} self
   */
  self.mapRows = function (func) {

    dataOb_ = dataOb_.map(function (row,rowIndex) {      
      var result = (checkAFunc (func) || functions_.mapRows)(row, {
        name:rowIndex,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:rowIndex,
        columnOffset:0,
        fiddler:self,
        values:self.getHeaders().map(function(k) { return row[k]; }),
        row:row
      });
      
      if (!result || result.length !== row.length) {
        throw new Error(
          'you cant change the number of columns in a row during map items'
        );
      }
      return result;
    });
    
    return self;
  };

  /**
   * get the unique values in a column
   * @param {string} columnName
   * @return {[*]} array of unique values
   */
  self.getUniqueValues = function (columnName) {
  
    return self.getColumnValues(columnName).filter(function (d,i,a) {
      return a.indexOf(d) === i;
    });

    
  }
  /**
   * iterate through each row - nodifies the data in this fiddler instance
   * @param {[string]} [columnNames] optional which column names to use (default is all)
   * @param {boolean} [keepLast=false] whether to keep the last row or the first found
   * @return {Fiddler} self
   */
  self.filterUnique = function (columnNames , keepLast) {

    var headers = self.getHeaders();
    cols = columnNames || headers;
    if (!Array.isArray(cols)) cols = [cols];
    
    // may need to reverse
    var data = dataOb_.slice();
    if (!keepLast && columnNames ) {
      data.reverse();
    }
    // check params are valid
    if (cols.some(function(d) {
      return headers.indexOf(d) === -1;
    })) { 
      throw 'unknown columns in ' + JSON.stringify(cols) + ' compared to ' + JSON.stringify(headers); 
    } 
       
        
    // filter out dups
    data = data.filter(function(d,i,a) {
      return !a.slice(i+1).some(function (e) {
        return cols.every(function (f) {
          return d[f] === e[f];
        });
      });
    });
    
    // reverse again
    if (!keepLast && columnNames ) {
      data.reverse();
    }
    
    // register
    dataOb_ = data;
    return self;

  };
  
  /**
   * iterate through each row - nodifies the data in this fiddler instance
   * @param {function} [func] optional function that shoud return true if the row is to be kept
   * @return {Fiddler} self
   */
  self.filterRows = function (func) {

    dataOb_ = dataOb_.filter(function (row,rowIndex) {
      return (checkAFunc (func) || functions_.filterRows)(row, {
        name:rowIndex,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:rowIndex,
        columnOffset:0,
        fiddler:self,
        values:self.getHeaders().map(function(k) { return row[k]; }),
        row:row
      });
    });
    return self;
  };
 /**
   * iterate through each column - modifies the data in this fiddler instance
   * @param {string} name the name of the column
   * @param {function} [func] optional function that shoud return new column data
   * @return {Fiddler} self
   */
  self.mapColumn = function (name,func) {

    var values = self.getColumnValues(name);
    var columnIndex = self.getHeaders().indexOf(name);
        
    values.forEach (function (value,rowIndex) {

      dataOb_[rowIndex][name] = (checkAFunc (func) || functions_.mapColumns)(value, {
        name:name,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:rowIndex,
        columnOffset:columnIndex,
        fiddler:self,
        values:values,
        row:dataOb_[rowIndex]
      });
    
    });

    return self;
  };
  
 /**
   * iterate through each column - modifies the data in this fiddler instance
   * @param {function} [func] optional function that shoud return new column data
   * @return {Fiddler} self
   */
  self.mapColumns = function (func) {

    var columnWise = columnWise_ ();
    var oKeys = Object.keys(columnWise);
    
    oKeys.forEach (function (key,columnIndex) {
      // so we can check for a change
      var hold = columnWise[key].slice();
      var result = (checkAFunc (func) || functions_.mapColumns)(columnWise[key], {
        name:key,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:0,
        columnOffset:columnIndex,
        fiddler:self,
        values:columnWise[key]
      });
      
      // changed no of rows?
      if (!result || result.length !== hold.length) {
        throw new Error(
          'you cant change the number of rows in a column during map items'
        );
      }
      // need to zip through the dataOb and change to new column values
      if (hold.join() !== result.join()) {
        result.forEach(function(r,i) {
          dataOb_[i][key] = r;
        });
      }
    });
    
    return self;
  };
  
  /**
   * iterate through each header
   * @param {function} [func] optional function that shoud return new column data
   * @return {Fiddler} self
   */
  self.mapHeaders = function (func) {
    
    if (!self.hasHeaders()) {
      throw new Error('this fiddler has no headers so you cant change them');
    }
    
    var columnWise = columnWise_ ();
    var oKeys = Object.keys(columnWise);
    var nKeys = [];
    
    oKeys.forEach (function (key,columnIndex) {
      
      var result = (checkAFunc (func) || functions_.mapHeaders)(key, {
        name:key,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:0,
        columnOffset:columnIndex,
        fiddler:self,
        values:columnWise[key]
      });
      
      // deleted the header
      if (!result) {
        throw new Error(
          'header cant be blank'
        );
      }
      
      nKeys.push (result);
    });

    
    // check for change
    if (nKeys.join() !== oKeys.join()){
      headerOb_ = {};
      dataOb_ = dataOb_.map(function(d) {
        return oKeys.reduce(function(p,c) {
          var idx = Object.keys(p).length;
          headerOb_[nKeys[idx]] = idx;
          p[nKeys[idx]] = d[c];
          return p;
        },{});
      });
    }
    return self;
  };
  
  /**
   * iterate through each column - modifies the data in this fiddler instance
   * @param {function} [func] optional function that shoud return true if the column is to be kept
   * @return {Fiddler} self
   */
  self.filterColumns = function (func) {
    checkAFunc (func);
    
    var columnWise = columnWise_ ();
    var oKeys = Object.keys(columnWise);
    
    // now filter out any columns
    var nKeys = oKeys.filter(function (key,columnIndex) {
      var result = (checkAFunc (func) || functions_.filterColumns)(key, {
        name:key,
        data:dataOb_,
        headers:headerOb_,
        rowOffset:0,
        columnOffset:columnIndex,
        fiddler:self,
        values:self.getColumnValues(key)
      });
      return result;
    });
    
    // anything to be deleted?
    if (nKeys.length !== oKeys.length) {
      dataOb_ = dropColumns_ (nKeys);
      headerOb_ = nKeys.reduce(function(p,c) {
        p[c] = Object.keys(p).length;
        return p;
      } ,{});
    }
    return self;
  };
  
  //-----
  
  /**
  * get the values for a given column
  * @param {string} columnName the given column
  * @return {[*]} the column values
  */
  self.getColumnValues = function(columnName) {
    if (self.getHeaders().indexOf(columnName) === -1 ) {
      throw new Error (columnName + ' is not a valid header name');
    }
    // transpose the data
    return dataOb_.map(function(d) {
      return d[columnName];
    });
  };
 
  /**
  * get the values for a given row
  * @param {number} rowOffset the rownumber starting at 0
  * @return {[*]} the column values
  */
  self.getRowValues = function (rowOffset) {
   // transpose the data
    return headOb_.map(function(key) {
      return d[rowOffset][headOb_[key]];
    });
  }; 
  
   /**
  * copy a column before
  * @param {string} header the column name 
  * @param {string} [newHeader] the name of the new column - not needed if no headers
  * @param {string} [insertBefore] name of the header to insert befire, undefined for end 
  * @return {Fiddler} self
  */
  self.copyColumn = function (header, newHeader, insertBefore) {
    
    // the headers
    var headers = self.getHeaders();
    var headerPosition = headers.indexOf(header);
    
    if (!header || headerPosition === -1) {
      throw new Error ('must supply an existing header of column to move');
    }
    
    var columnOffset = insertColumn_ (newHeader, insertBefore);
    
    // copy the data
    self.mapColumns(function (values , properties) {
      return properties.name === newHeader ? self.getColumnValues(header) : values;
    });

    return self;
  };
   
  /**
  * get the range required to write the values starting at the given range
  * @param {Range} range the range
  * @return {Range} the range needed
  */
  self.getRange = function (range) {
    return range.offset (0,0,self.getNumRows() + (self.hasHeaders() ? 1 : 0) , self.getNumColumns()); 
  }
 /**
  * move a column before
  * @param {string} header the column name 
  * @param {string} [insertBefore] name of the header to insert befire, undefined for end 
  * @return {Fiddler} self
  */
  self.moveColumn = function (header, insertBefore) {
    
    // the headers
    var headers = self.getHeaders();
    var headerPosition = headers.indexOf(header);
    
    if (!header || headerPosition === -1) {
      throw new Error ('must supply an existing header of column to move');
    }
    
    // remove from its existing place
    headers.splice ( headerPosition , 1);
    
    // the output position
    var columnOffset = insertBefore ? headers.indexOf (insertBefore) : self.getNumColumns();
    // check that the thing is ok to insert before
    if (columnOffset < 0 || columnOffset > self.getNumColumns() ) {
      throw new Error (header + ' doesnt exist to insert before');
    }
    
    // insert the column at the requested place
    headers.splice ( columnOffset , 0, header);
    
    // adjust the positions
    headerOb_ = headers.reduce(function(p,c) {
      p[c] = Object.keys(p).length;
      return p;
    } , {});
    
    
    return self;
  };
  
 /**
  * insert a column before
  * @param {string} [header] the column name - undefined if no headers
  * @param {string} [insertBefore] name of the header to insert befire, undefined for end 
  * @return {number} the offset if the column that was inserted
  */
  function insertColumn_  (header, insertBefore) {
    
    // the headers
    var headers = self.getHeaders();
    
    // the position
    var columnOffset = insertBefore ? headers.indexOf (insertBefore) : self.getNumColumns();
    
    // check ok for header
    if (!self.hasHeaders() && header) {
      throw new Error ('this fiddler has no headers - you cant insert a column with a header');
    }
    
    // make one up
    if (!self.hasHeaders()) {
      header = columnLabelMaker_ (headers.length + 1);
    }
    
    if (!header) {
      throw new Error ('must supply a header for an inserted column');
    }
    if (headers.indexOf (header) !== -1 ) {
      throw new Error ('you cant insert a duplicate header ' + header);
    }
    
    // check that the thing is ok to insert before
    if (columnOffset < 0 || columnOffset > self.getNumColumns() ) {
      throw new Error (header + ' doesnt exist to insert before');
    }

    // insert the column at the requested place
    headers.splice ( columnOffset , 0, header);
    
    // adjust the positions
    headerOb_ = headers.reduce(function(p,c) {
      p[c] = Object.keys(p).length;
      return p;
    } , {});
    
    // fill in the blanks in the data
    dataOb_.forEach(function(d) {
      d[header] = '';
    });
    
    return columnOffset;
  }  
 /**
  * insert a column before
  * @param {string} [header] the column name - undefined if no headers
  * @param {string} [insertBefore] name of the header to insert befire, undefined for end 
  * @return {Fiddler} self
  */
  self.insertColumn = function (header, insertBefore) {
    
    // the headers
    insertColumn_ (header, insertBefore);
    return self;
  
  }
  
  
  /**
  * insert a row before
  * @param {number} [rowOffset] starting at 0, undefined for end 
  * @param {number} [numberofRows=1] to add
  * @param {[object]} [data] should be equal to number of Rows
  * @return {Fiddler} self
  */
  self.insertRows = function (rowOffset,numberOfRows, data) {
    if (typeof numberOfRows === typeof undefined) {
      numberOfRows = 1;
    }
    
    // if not defined insert at end
    if (typeof rowOffset === typeof undefined) {
      rowOffset = self.getNumRows();
    }
    
    if (rowOffset < 0 || rowOffset > self.getNumRows() ) {
      throw new Error (rowOffset + ' is inalid row to insert before');
    }

    for (var i =0, skeleton = [], apply = [rowOffset,0] ; i < numberOfRows ; i++) {
      skeleton.push (makeEmptyObject_());
    }
                                      
    // maybe we have some data
    if (data) {
      if (!Array.isArray(data)) {
        data = [data];
      }
      if (data.length !== skeleton.length) {
        throw new Error (
          'number of data items ' + data.length +  
          ' should equal number of rows ' + skeleton.length +' to insert ' );
      }
      // now merge with skeleton
      skeleton.forEach(function(e,i) {
        
        // override default values
        Object.keys (e).forEach(function (key) {
          if (data[i].hasOwnProperty(key)) {
            e[key] = data[i][key];
          }
        });
        
        // check that no rubbish was specified
        if (Object.keys(data[i]).some(function(d) { 
          return !e.hasOwnProperty (d);
        })) { 
          throw new Error('unknown columns in row data to insert'); 
        }
                       
      });
    }
    // insert the requested number of rows at the requested place
    dataOb_.splice.apply (dataOb_ , apply.concat(skeleton));
    
    return self;
  }
  
  function makeEmptyObject_ () {
    return self.getHeaders().reduce(function (p,c) {
      p[c] = ''; // in spreadsheet work empty === null string
      return p;
    },{});
  }
  /**
  * create a column slice of values
  * @return {object} the column slice
  */
  function columnWise_ () {
    // first transpose the data
    return Object.keys(headerOb_).reduce (function (tob , key) {
      tob[key] = self.getColumnValues(key);
      return tob;
    },{});
  }
  
 
 
  /**
   * will create a new dataob with columns dropped that are not in newKeys
   * @param {[string]} newKeys the new headerob keys
   * @return {[object]} the new dataob
   */
  function dropColumns_ (newKeys) {

    return dataOb_.map(function(row) {
      return Object.keys(row).filter (function (key) {
        return newKeys.indexOf(key) !== -1;
      })
      .reduce (function (p,c) {
        p[c] = row[c];
        return p;
      },{});
    });
  
  };
  
  /**
  * return the number of rows
  * @return {number} the number of rows of data
  */
  self.getNumRows = function () {
    return dataOb_.length;
  };
  
  /**
  * return the number of columns
  * @return {number} the number of columns of data
  */
  self.getNumColumns = function () {
    return Object.keys(headerOb_).length;
  };
  
  /**
   * check that a variable is a function and throw if not
   * @param {function} [func] optional function to check
   * @return {function} the func
   */
  function checkAFunc (func) {
    if (func && typeof func !== 'function') {
      throw new Error('argument should be a function');
    }
    return func;
  }
  
  /**
   * make column item
   * @param {object} ob the column object
   * @param {string} key the key as returned from a .filter
   * @param {number} idx the index as returned from a .filter
   * @return {object} a columnwise item
   */
  function makeColItem_ (ob,key,idx) {
    return {
      values:ob[key],
      columnOffset:idx,
      name:key
    };
  };
  
  /**
   * make row item
   * @param {object} row the row object as returned from a .filter
   * @param {number} idx the index as returned from a .filter
   * @return {object} a rowwise item
   */  
  function makeRowItem_ (row,idx) {
    return {
      values:Object.keys(headerOb_).map(function(k) { return row[k]; }),
      rowOffset:idx,
      data:row,
      fiddler:self
    };
  };

  
  /**
  * return the headers
  * @return {[string]} the headers
  */
  self.getHeaders = function () {
    return Object.keys(headerOb_);
  };
  
  /**
  * return the data
  * @return {[object]} as rowwise kv pairs 
  */
  self.getData = function () {
    return dataOb_;
  };
  
  /**
  * replace the current data in the fiddle
  * will also update the headerOb
  * @param {[object]} dataOb the new dataOb
  * @return {Fiddle} self
  */
  self.setData = function  (dataOb) {
    
    // need to calculate new headers
    headerOb_ = dataOb.reduce(function(hob,row) {
      Object.keys(row).forEach(function(key) {
        if (!hob.hasOwnProperty(key)) {
          hob[key] = Object.keys(hob).length;
        }
      });
      return hob;
    } , {});
    dataOb_ = dataOb;
    return self;
  };
  
  /**
   * initialize the header ob and data on from a new values array
   * @return {Fiddle} self
   */
  self.init = function () {
    headerOb_ = makeHeaderOb_();
    dataOb_ = makeDataOb_();
    return self;
  };
  
  /**
  * @return {boolean} whether a fiddle has headers
  */
  self.hasHeaders = function () {
    return hasHeaders_;
  };
  
  /**
  * set whether a fiddle has headers
  * @param {boolean} headers whether it has
  * @return {Fiddler} self
  */
  self.setHasHeaders = function (headers) {
    hasHeaders_ = !!headers ;
    return self.init();
  };
  
  /**
   * set a new values array
   * will also init a new dataob and header
   * @param {[[]]} values as returned from a sheet
   * @return {Fiddler} self
   */
  self.setValues = function (values) {
    values_= values;
    return self.init();
  };
  
  /**
   * gets the original values stored with this fiddler
   * @return {[[]]} value as needed by setvalues
   */
  self.getValues = function () {
    return values_;
  };
  
  /**
   * gets the updated values derived from this fiddlers dataob
   * @return {[[]]} value as needed by setvalues
   */
  self.createValues = function () {
    return makeValues_();
  };
  
  /**
   * make a map with column labels to index
   * if there are no headers it will use column label as property key
   * @return {object} a header ob.
   */
  function makeHeaderOb_ () {
    
    return values_.length ? 
      ((self.hasHeaders() ? 
       values_[0] : values_[0].map(function(d,i) {
         return columnLabelMaker_ (i+1);
       }))
    .reduce (function (p,c) {
      var key = c.toString();
      if (p.hasOwnProperty(key)) {
        throw 'duplicate column header ' + key;
      }
      p[key]=Object.keys(p).length;
      return p;
    },{})) : null;
    
  }
  
  /**
   * make a map of data
   * @return {object} a data ob.
   */
  function makeDataOb_ () {
    
    // get rid of the headers if there are any
    var vals = self.hasHeaders() ? values_.slice(1) : values_;
    
    // make an array of kv pairs
    return headerOb_ ? 
      ( (vals|| []).map (function (row) {
        return Object.keys(headerOb_).reduce(function (p,c) {
          p[c] = row [headerOb_[c]];
          return p;
        },{})
      })) : null;
  }
  
  /**
   * make values from the dataOb
   * @return {object} a data ob.
   */
  function makeValues_ () {

    // add the headers if there are any
    var vals = self.hasHeaders() ? [Object.keys(headerOb_)] : [];
    
    // put the kv pairs back to values
    dataOb_.forEach(function(row) {
      vals.push (Object.keys(headerOb_).reduce(function(p,c){
        p.push(row[c]);
        return p;
      },[]));
    });
    
    return vals;
  }
  
  /**
  * create a column label for sheet address, starting at 1 = A, 27 = AA etc..
  * @param {number} columnNumber the column number
  * @return {string} the address label 
  */
  function columnLabelMaker_ (columnNumber,s) {
    s = String.fromCharCode(((columnNumber-1) % 26) + 'A'.charCodeAt(0)) + ( s || '' );
    return columnNumber > 26 ? columnLabelMaker_ ( Math.floor( (columnNumber-1) /26 ) , s ) : s;
  }
  
  
  
  
};

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