Userscript for filtering and sorting tables

tabletools.user.js

// ==UserScript==
// @name        TableTools
// @namespace   ameboide
// @author      ameboide
// @description Adds filters (ctrl+shift+click), sorting (ctrl+alt+click) and stats (ctrl+alt+shift+click) to tables
// @include     *
// @version     1.6
// @updateURL   https://gist.github.com/ameboide/368797ecaa18bb9ac11f866e12156c95/raw/tabletools.user.js
// ==/UserScript==

function clickHandler(event) {
  let table = event.target.closest('table');
  if(!table) return;

  let td = event.target.closest('td, th');
  let ctrl = event.ctrlKey || event.metaKey;
  let alt = event.altKey;
  let shift = event.shiftKey;
  let leftClick = event.button == 0;

  if(ctrl && shift && !alt && leftClick) toggleInputFilters(table);
  else if(ctrl && shift && !alt && !leftClick) toggleSelectFilters(table);
  else if(ctrl && !shift && alt && leftClick) sortTableByTd(table, td, -1);
  else if(ctrl && !shift && alt && !leftClick) sortTableByTd(table, td, 1);
  else if(ctrl && shift && alt) toggleStats(table);
}

function toggleInputFilters(table) {
  toggleFilters(table, 'tt_input_filters', 'input');
}

function toggleSelectFilters(table) {
  toggleFilters(table, 'tt_select_filters', 'select');
  fillSelectFilters(table);
}

function fillSelectFilters(table){
  let thead = table.querySelector('thead.tt_select_filters');
  if(!thead) return;

  let cols = countCols(table);
  let lists = new Array(cols);
  for(let tr of table.querySelectorAll('tbody tr:not(.tt_hidden), :scope > tr:not(.tt_hidden)')) {
    let c = 0;
    for(let td of tr.querySelectorAll('td, th')) {
      let text = clean(td.textContent);
      if(!lists[c]) lists[c] = {};
      if(!lists[c][text]) lists[c][text] = 0;
      lists[c][text]++;
      c += td.colSpan || 1;
    }
  }

  for(let col = 0; col < cols; col++) {
    let sel = thead.querySelector(`select[data-col="${col}"]`);
    let value = sel.value;
    for(let option of sel.querySelectorAll('option')) option.remove();

    let option = document.createElement('option');
    sel.appendChild(option);
    let keys = Object.keys(lists[col]).sort(function(a, b) {
      let ret = lists[col][b] - lists[col][a];
      return ret == 0 ? (a > b ? 1 : -1) : ret;
    });
    for(let text of keys) {
      option = document.createElement('option');
      option.textContent = `${text} (${lists[col][text]})`;
      option.value = `=${text}`;
      if(option.value == value) option.selected = 'selected';
      sel.appendChild(option);
    }
  }
}

function toggleFilters(table, klass, inputTag) {
  let thead = table.querySelector(`thead.${klass}`);
  if(thead) {
    thead.remove();
    filterColumns(table);
    return;
  }

  let cols = countCols(table);
  thead = document.createElement('thead');
  thead.classList.add(klass, 'tt_filters');
  let tr = document.createElement('tr');
  thead.appendChild(tr);
  table.prepend(thead);

  for(let i=0; i<cols; i++) {
    let td = document.createElement('td');
    let input = document.createElement(inputTag);
    input.dataset.col = i;
    input.addEventListener('change', (event) => filterColumns(event.target.closest('table')), false);
    input.addEventListener('keyup', (event) => {if(event.keyCode == 13) filterColumns(event.target.closest('table'))}, false);
    tr.appendChild(td);
    input.style.width = `${td.scrollWidth}px`;
    td.appendChild(input);
  }
}

function countCols(table){
  let tds = Array.from(table.querySelector('tr').querySelectorAll('td, th'));
  return Array.map(tds, (td) => td.colSpan || 1).reduce((a, b) => a + b, 0);
}

function filterColumns(table) {
  for(let tr of table.querySelectorAll('tr.tt_hidden')) tr.classList.remove('tt_hidden');
  for(let input of table.querySelectorAll('.tt_filters input, .tt_filters select')) filterColumn(input);
  fillSelectFilters(table);
  fillStats(table);
}

function filterColumn(input) {
  let col = input.dataset.col;
  let text = input.value;
  if(!text) return;

  let filter = inputToFilters(text);
  for(let tr of input.closest('table').querySelectorAll('tbody tr, :scope > tr')) {
    if(!matchFilters(colText(tr, col), filter)) tr.classList.add('tt_hidden');
  }
}

function inputToFilters(textOr){
  return textOr.split(' || ').map(function(textAnd){
    return textAnd.split(' && ').map(function(text){
      let m = text.match(/^\/(.+)\/$/);
      if(m) return {re: new RegExp(m[1], 'i')};

      text = clean(text);
      let comp = null;
      if(m = text.match(/^([<>=!\\])(.*)/)) {
        comp = m[1] == '\\' ? null : m[1];
        text = m[2];
      }
      return {comp: comp, text: text};
    });
  });
}

function matchFilters(text, ors){
  for(let ands of ors){
    let ok = true;
    for(let filter of ands){
      if(!matchFilter(text, filter)){
        ok = false;
        break;
      }
    }
    if(ok) return true;
  }
  return false;
}

function colText(tr, col) {
  let c = 0;
  for(let td of tr.querySelectorAll('td, th')) {
    c += td.colSpan || 1;
    if(c <= col) continue;
    return clean(td.textContent);
  }
}

function matchFilter(content, filter) {
  if(filter.re) return content.match(filter.re);

  let text = filter.text;
  if(!filter.comp) return content.indexOf(text) >= 0;

  if(isNum(text) && isNum(content)) {
    text = parseNum(text);
    content = parseNum(content);
  }

  switch(filter.comp) {
    case '<': return content < text;
    case '>': return content > text;
    case '=': return content == text;
    case '!': return typeof(text) == 'string' ? content.indexOf(text) < 0 : content != text;
  }
}

function isNum(text){
  return text && !isNaN(text.replace(/,/g, ''));
}

function parseNum(text){
  return parseFloat(text.replace(/,/g, ''));
}

function clean(string) {
  return string.trim().toLowerCase().replace(/\s+/g, ' ');
}

function sortTableByTd(table, td, order){
  let col = 0;
  for(let tdi of td.parentNode.querySelectorAll('td, th')) {
    if(tdi == td) break;
    col += tdi.colSpan || 1;
  }
  sortTable(table, col, order);
}

function sortTable(table, col, order) {
  let trs = table.querySelectorAll('tbody tr, :scope > tr');
  let tbody = table.querySelector('tbody');
  if(!tbody) {
    tbody = document.createElement('tbody');
    table.appendChild(tbody);
  }

  let ordered = Array.prototype.slice.call(trs).sort(function(a, b) {
    let aText = colText(a, col);
    let bText = colText(b, col);
    if(isNum(aText) && isNum(bText)) {
      aText = parseNum(aText);
      bText = parseNum(bText);
    }
    if(aText == bText) return 0;
    return (aText > bText ? 1 : -1) * order;
  });

  for (let tr of ordered) tbody.appendChild(tr);
}

function toggleStats(table) {
  let thead = table.querySelector('thead.tt_stats');
  if(thead) {
    thead.remove();
    return;
  }

  let cols = countCols(table);

  thead = document.createElement('thead');
  thead.classList.add('tt_stats');
  let tr = document.createElement('tr');
  thead.appendChild(tr);
  table.prepend(thead);

  for(let i=0; i<cols; i++) {
    let td = document.createElement('td');
    tr.appendChild(td);
  }
  fillStats(table);
}

function fillStats(table){
  let tr = table.querySelector('thead.tt_stats tr');
  if(!tr) return;

  let i = 0;
  for(let td of tr.querySelectorAll('td')) {
    let stats = colStats(table, i);
    let html = '';
    for(let key in stats) html += `<strong>${key}:</strong> <span>${stats[key]}</span><br/>`;
    td.innerHTML = html;
    i++;
  }
}

function colStats(table, col){
  let distinct = {}, sum = 0, min = null, max = null, isTime = true;
  let trs = table.querySelectorAll('tbody tr:not(.tt_hidden), :scope > tr:not(.tt_hidden)');
  for(let tr of trs){
    let text = colText(tr, col);
    if(!distinct[text]) distinct[text] = 0;
    distinct[text]++;
    if(isTime && text && !text.match(/^(\d+:)?\d{1,2}:\d{1,2}$/)){
      isTime = false;
      sum = 0;
    }
    if(isTime && text){
      sum += timeToInt(text);
    }
    else if(isNum(text)){
      text = parseNum(text);
      sum += text;
    }
    if(min === null || text < min) min = text;
    if(max === null || text > max) max = text;
  }
  let count0 = 0;
  for(let text in distinct) if(text.match(/^(0+(\.0+|(:0+)+)?)?$/)) count0 += distinct[text];
  let stats = {
    Count: trs.length,
    CountNot0: trs.length - count0,
    CountDist: Object.keys(distinct).length,
    Min: min,
    Max: max
  };
  if(sum > 0){
    if(isTime){
      stats.Avg = intToTime(sum/stats.Count);
      stats.AvgNot0 = intToTime(sum/stats.CountNot0);
      stats.Sum = intToTime(sum);
    }
    else{
      stats.Avg = (sum/stats.Count).toFixed(3);
      stats.AvgNot0 = (sum/stats.CountNot0).toFixed(3);
      stats.Sum = sum;
    }
  }
  return stats;
}

function timeToInt(text){
  let parts = text.split(':'), sum = 0;
  for (let i = parts.length - 1, mult = 1; i >= 0; i--, mult *= 60) sum += parts[i] * mult;
  return sum;
}

function intToTime(secs){
  return zpad(secs/3600) + ':' + zpad(secs/60 % 60) + ':' + zpad(secs % 60);
}

function zpad(num, len){
  return (Math.floor(num) + '').padStart(len || 2, '0');
}

let style = document.createElement('style');
style.innerHTML = '.tt_hidden{ display: none; } .tt_stats{ background-color: #ffc; }';
document.head.appendChild(style);

window.addEventListener('mousedown', clickHandler, true);