Data wrangling with named columns in Google Spreadsheet

In Using a cross filter with Google Apps Script I showed how to use the powerful cross filter library to do some data wrangling. But using some of JavaScript’s Advanced Array functions, and working with named columns instead of column numbers gives you a lot of wrangling power straight out of the box.

In the example, we are going to play around with some of the data I used in Eurovision results with cross filter and dc.js.

The data

We have several thousand rows of data that starts like this

I’d like to create a summary sheet showing the total votes received for each country, and also sort this as well as the original data into reverse order of total votes received. You can do this is in juts a few lines of code.

Here’s the start of the summary data result

country total votes

And the original data, resorted in the same order of countries.

The code

function myFunction() {
  
  // get all the data on the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("votes");
  var summarySheet = ss.getSheetByName("summary");
  var values = sheet.getDataRange().getValues();
  
  // separate the headers & data
  var headers = values[0];
  var data = values.slice(1);

  // map the header names to column numbers
  var headMap = {};
  headers.forEach ( function (d,i) {
    var name = d ? d : 'column '+(i+1);
    headMap[name] = i;
  });
  
  // do things with the data - lets sum up all the votes by country
  var votesByCountry = data.reduce(function(summary,row) {
    
    if (!summary[row[headMap.country]]) {
      summary[row[headMap.country]] = 0;
    }
    summary[row[headMap.country]] += row[headMap.received];
    return summary;
  },{});
  

  // lets sort the oroginal data by total votes received by country, in reverse order, and re output the result
  sheet.getRange(2,1,data.length,data[0].length).setValues( 
    data.sort ( function (a,b) {
      return votesByCountry[b[headMap.country]] - votesByCountry[a[headMap.country]];
    }));
    
    
  // lets put the summary result to another sheet and sort in reverse order
  summarySheet.getRange(1,1,Object.keys(votesByCountry).length+1,2).setValues(
    [['country','total votes']]
    .concat(Object.keys(votesByCountry).map (function(k) {
      return [k,votesByCountry[k]] 
    })
    .sort ( function (a,b) {
      return b[1] - a[1];
    })));
}

Walkthrough

First separate the data into header and data, then create an object that maps column names to the physical column they appear in the data. This means we can forget all about column numbers, or even change the shape of the sheet, and our subsequent code will still work.

// get all the data on the sheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("votes");
  var summarySheet = ss.getSheetByName("summary");
  var values = sheet.getDataRange().getValues();
  
  // separate the headers & data
  var headers = values[0];
  var data = values.slice(1);

  // map the header names to column numbers
  var headMap = {};
  headers.forEach ( function (d,i) {
    var name = d ? d : 'column '+(i+1);
    headMap[name] = i;
	});

Now create a summary object all votes received by all countries

// do things with the data - lets sum up all the votes by country
  var votesByCountry = data.reduce(function(summary,row) {
    
    if (!summary[row[headMap.country]]) {
      summary[row[headMap.country]] = 0;
    }
    summary[row[headMap.country]] += row[headMap.received];
    return summary;
	},{});

And we’re ready to output the result!

First sort the original data based on the total votes received for each country, and write it back to the original sheet in a new order

// lets sort the oroginal data by total votes received by country, in reverse order, and re output the result
  sheet.getRange(2,1,data.length,data[0].length).setValues( 
    data.sort ( function (a,b) {
      return votesByCountry[b[headMap.country]] - votesByCountry[a[headMap.country]];
	  }));

Now write the summary results, including a heading row

// lets put the summary result to another sheet and sort in reverse order
  summarySheet.getRange(1,1,Object.keys(votesByCountry).length+1,2).setValues(
    [['country','total votes']]
    .concat(Object.keys(votesByCountry).map (function(k) {
      return [k,votesByCountry[k]] 
    })
    .sort ( function (a,b) {
      return b[1] - a[1];
	  })));

See Database abstraction with google apps script and Using crossfilter with Google Apps Script for more like this, or for a complete solution see A functional approach to fiddling with sheet data
For help and more information join our forum, follow the blog or follow me on Twitter