google_spreadsheet_marcro_demo.js

function consolidate() {
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var routers = {};
  var routerIps = [];
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    if (0 == sheet.getName().indexOf('__')) {
      continue;
    }
    var values = sheet.getDataRange().getValues();
    for (var j = 0; j < values.length; j++) {
      var row = values[j];
      if (row.length < 4) {
        continue;
      }
      var routerIp = row[3];
      if (!(routerIp in routers)) {
        routerIps.push(routerIp);
        routers[routerIp] = {};
      }
      for (var k=4; k < row.length; k++) {
        var routerFeature = row[k];
        if (!(routerFeature in routers[routerIp])) {
          routers[routerIp][routerFeature] = '';
        }
      }
    }
  }
  routerIps = routerIps.sort();
  var ALL_ROUTER_FEATURES = ['DNS_WRONG_ANSWER','DNS_TCP_RST','HTTP_TCP_RST','TCP_PACKET_DROP'];
  var result = [['ROUTER_IP']];
  result[0].push.apply(result[0], ALL_ROUTER_FEATURES);
  for (var i = 0; i < routerIps.length; i++) {
    var routerIp = routerIps[i];
    var router = routers[routerIp];
    var row = [routerIp];
    for (var j = 0; j < ALL_ROUTER_FEATURES.length; j++) {
      var routerFeature = ALL_ROUTER_FEATURES[j];
      row.push(routerFeature in router);
    }
    result.push(row);
  }
  return result;
}