Looker Heatmap Table

02 Source Code

/**
 * Heatmap Table -- Looker Custom Visualization
 * Developed by Martin Velez — RavencoreX
 * https://ravencorex.com
 *
 * Renders a table where rows = dimension values, columns = pivot values,
 * and cells are colored by configurable thresholds (High / Medium / Low).
 *
 * Query: 1 dimension (rows) + 1 pivot dimension (columns) + 1 measure (cell value).
 *
 * Admin → Visualizations:
 *   ID:    heatmap_table
 *   Label: Heatmap Table
 *   Main:  <URL to your hosted copy of this file>
 */

looker.plugins.visualizations.add({

  id: "heatmap_table",
  label: "Heatmap Table",

  options: {
    // -- Thresholds --
    threshold_high: {
      type: "number", label: "High Threshold >= (%)",
      default: 80, section: "Thresholds", order: 1
    },
    threshold_medium: {
      type: "number", label: "Medium Threshold >= (%)",
      default: 60, section: "Thresholds", order: 2
    },
    color_high: {
      type: "string", label: "High Color", default: "#22C55E",
      display: "color", section: "Thresholds", order: 3
    },
    color_medium: {
      type: "string", label: "Medium Color", default: "#F59E0B",
      display: "color", section: "Thresholds", order: 4
    },
    color_low: {
      type: "string", label: "Low Color", default: "#EF4444",
      display: "color", section: "Thresholds", order: 5
    },
    label_high: {
      type: "string", label: "High Label", default: "High",
      section: "Thresholds", order: 6
    },
    label_medium: {
      type: "string", label: "Medium Label", default: "Medium",
      section: "Thresholds", order: 7
    },
    label_low: {
      type: "string", label: "Low Label", default: "Low",
      section: "Thresholds", order: 8
    },

    // -- Legend --
    show_legend: {
      type: "string", label: "Show Legend", display: "select",
      values: [{ "Yes": "true" }, { "No": "false" }],
      default: "true", section: "Legend", order: 1
    },
    legend_title: {
      type: "string", label: "Legend Title", default: "Coverage Rate:",
      section: "Legend", order: 2
    },
    legend_position: {
      type: "string", label: "Legend Position", display: "select",
      values: [{ "Above Table": "above" }, { "Below Table": "below" }],
      default: "above", section: "Legend", order: 3
    },
    legend_align: {
      type: "string", label: "Legend Alignment", display: "select",
      values: [{ "Left": "left" }, { "Center": "center" }, { "Right": "right" }],
      default: "left", section: "Legend", order: 4
    },
    legend_font_weight: {
      type: "string", label: "Legend Font Weight", display: "select",
      values: [{ "Normal": "normal" }, { "Bold": "bold" }],
      default: "normal", section: "Legend", order: 5
    },

    // -- Table --
    header_label: {
      type: "string", label: "Header Label (Row / Col)",
      default: "", placeholder: "e.g. Type / Region",
      section: "Table", order: 1
    },
    cell_font_size: {
      type: "number", label: "Cell Font Size (px)", default: 16,
      section: "Table", order: 2
    },
    header_font_size: {
      type: "number", label: "Header Font Size (px)", default: 14,
      section: "Table", order: 3
    },
    cell_height: {
      type: "number", label: "Cell Height (px)", default: 56,
      section: "Table", order: 4
    },
    cell_border_radius: {
      type: "number", label: "Cell Border Radius (px)", default: 6,
      section: "Table", order: 5
    },
    cell_gap: {
      type: "number", label: "Cell Gap (px)", default: 4,
      section: "Table", order: 6
    },
    col_header_font_weight: {
      type: "string", label: "Column Header Font Weight", display: "select",
      values: [{ "Bold": "bold" }, { "Normal": "normal" }],
      default: "bold", section: "Table", order: 7
    },
    row_header_font_weight: {
      type: "string", label: "Row Header Font Weight", display: "select",
      values: [{ "Semi-Bold": "600" }, { "Bold": "bold" }, { "Normal": "normal" }],
      default: "600", section: "Table", order: 8
    },
    cell_value_font_weight: {
      type: "string", label: "Cell Value Font Weight", display: "select",
      values: [{ "Bold": "bold" }, { "Normal": "normal" }],
      default: "bold", section: "Table", order: 9
    },
    font_family: {
      type: "string", label: "Font Family",
      default: "'Inter','Helvetica Neue',Arial,sans-serif",
      section: "Table", order: 10
    },
    value_is_percentage: {
      type: "string", label: "Values are Percentages", display: "select",
      values: [{ "Yes (0-1 or 0-100)": "true" }, { "No (raw numbers)": "false" }],
      default: "true", section: "Table", order: 11
    }
  },

  create: function (element, config) {
    element.innerHTML = "";
    element.style.fontFamily = config.font_family || "'Inter','Helvetica Neue',Arial,sans-serif";
    element.style.display = "flex";
    element.style.alignItems = "flex-start";
    element.style.justifyContent = "center";
    element.style.width = "100%";
    element.style.height = "100%";
    element.style.overflow = "hidden";
    element.style.background = "white";
    element.style.padding = "0";
    element.style.margin = "0";
    element.style.boxSizing = "border-box";
  },

  updateAsync: function (data, element, config, queryResponse, details, doneRendering) {
    element.innerHTML = "";
    element.style.overflow = "hidden";
    element.style.padding = "0";
    element.style.margin = "0";

    var parent = element.parentElement;
    while (parent && parent !== document.body) {
      parent.style.overflow = "hidden";
      parent.style.padding = "0";
      parent = parent.parentElement;
    }
    if (!document.getElementById("_heatmap_reset_css")) {
      var st = document.createElement("style");
      st.id = "_heatmap_reset_css";
      st.textContent = "#vis, #vis-container, .looker-vis-context { padding:0!important; margin:0!important; overflow:hidden!important; }";
      document.head.appendChild(st);
    }

    if (!data || data.length === 0) {
      element.innerHTML = '<p style="color:#9CA3AF;text-align:center;padding:20px;font-size:13px;">No data returned</p>';
      doneRendering(); return;
    }

    var dimensions = queryResponse.fields.dimension_like || queryResponse.fields.dimensions || [];
    var measures = queryResponse.fields.measure_like || [];
    if (measures.length === 0) {
      measures = (queryResponse.fields.measures || []).concat(queryResponse.fields.table_calculations || []);
    }
    var pivots = queryResponse.pivots || [];

    if (measures.length < 1) {
      element.innerHTML = '<p style="color:#9CA3AF;text-align:center;padding:20px;font-size:13px;">Add at least 1 measure</p>';
      doneRendering(); return;
    }
    if (pivots.length === 0) {
      element.innerHTML = '<p style="color:#9CA3AF;text-align:center;padding:20px;font-size:13px;">Add a pivot dimension to generate columns</p>';
      doneRendering(); return;
    }

    this.clearErrors();

    var threshHigh  = config.threshold_high  != null ? Number(config.threshold_high)  : 80;
    var threshMed   = config.threshold_medium != null ? Number(config.threshold_medium) : 60;
    var colorHigh   = config.color_high   || "#22C55E";
    var colorMed    = config.color_medium  || "#F59E0B";
    var colorLow    = config.color_low    || "#EF4444";
    var labelHigh   = config.label_high   || "High";
    var labelMed    = config.label_medium  || "Medium";
    var labelLow    = config.label_low    || "Low";
    var showLegend  = config.show_legend  !== "false";
    var legendTitle  = config.legend_title != null ? config.legend_title : "Coverage Rate:";
    var legendPos   = config.legend_position || "above";
    var legendAlign = config.legend_align    || "left";
    var headerLabel = config.header_label    || "";
    var cellFz      = Number(config.cell_font_size)    || 16;
    var headerFz    = Number(config.header_font_size)  || 14;
    var cellH       = Number(config.cell_height)       || 56;
    var cellRadius  = config.cell_border_radius != null ? Number(config.cell_border_radius) : 6;
    var cellGap     = config.cell_gap        != null ? Number(config.cell_gap)        : 4;
    var fontFamily  = config.font_family || "'Inter','Helvetica Neue',Arial,sans-serif";
    var isPercentage = config.value_is_percentage !== "false";
    var legendFw    = config.legend_font_weight === "bold" ? "700" : "400";
    var colHeaderFw = config.col_header_font_weight === "normal" ? "400" : "700";
    var rowHeaderFw = config.row_header_font_weight || "600";
    if (rowHeaderFw === "bold")   rowHeaderFw = "700";
    if (rowHeaderFw === "normal") rowHeaderFw = "400";
    var cellValueFw = config.cell_value_font_weight === "normal" ? "400" : "700";

    var pivotKeys = [];
    var pivotLabels = {};
    for (var pi = 0; pi < pivots.length; pi++) {
      pivotKeys.push(pivots[pi].key);
      pivotLabels[pivots[pi].key] = pivots[pi].key;
    }

    var rowDim = dimensions.length > 0 ? dimensions[0] : null;
    var measureField = measures[0];

    var rows = [];
    for (var ri = 0; ri < data.length; ri++) {
      var row = data[ri];
      var rowLabel = rowDim
        ? (row[rowDim.name].rendered || String(row[rowDim.name].value) || "--")
        : "Row " + (ri + 1);
      var cells = [];
      for (var ci = 0; ci < pivotKeys.length; ci++) {
        var pk = pivotKeys[ci];
        var mCell = row[measureField.name];
        var val = null, rendered = null, links = null;
        if (mCell && mCell[pk]) {
          val      = Number(mCell[pk].value);
          rendered = mCell[pk].rendered || null;
          links    = mCell[pk].links    || null;
        }
        cells.push({ pivotKey: pk, value: val, rendered: rendered, links: links });
      }
      rows.push({ label: rowLabel, cells: cells });
    }

    function getCellDisplay(val, rendered) {
      if (val === null || isNaN(val)) return { display: "--", pct: null };
      var pct = isPercentage
        ? ((val > 0 && val <= 1) ? val * 100 : val)
        : val;
      var display = rendered && !isPercentage
        ? rendered
        : isPercentage
          ? Math.round(pct) + "%"
          : rendered || String(Math.round(val));
      return { display: display, pct: pct };
    }

    function getColor(pct) {
      if (pct === null) return "#F3F4F6";
      if (pct >= threshHigh) return colorHigh;
      if (pct >= threshMed)  return colorMed;
      return colorLow;
    }

    var wrapper = document.createElement("div");
    wrapper.style.cssText = "font-family:" + fontFamily + ";padding:16px;box-sizing:border-box;width:100%;display:flex;flex-direction:column;gap:12px;";

    function buildLegend() {
      var legend = document.createElement("div");
      var alignMap = { left: "flex-start", center: "center", right: "flex-end" };
      legend.style.cssText = "display:flex;align-items:center;gap:16px;flex-wrap:wrap;justify-content:" + (alignMap[legendAlign] || "flex-start") + ";";
      if (legendTitle) {
        var titleEl = document.createElement("span");
        titleEl.style.cssText = "font-size:13px;color:#6B7280;font-weight:500;";
        titleEl.textContent = legendTitle;
        legend.appendChild(titleEl);
      }
      [{ color: colorLow, label: labelLow }, { color: colorMed, label: labelMed }, { color: colorHigh, label: labelHigh }]
        .forEach(function(item) {
          var wrap = document.createElement("div");
          wrap.style.cssText = "display:flex;align-items:center;gap:6px;";
          var swatch = document.createElement("div");
          swatch.style.cssText = "width:16px;height:16px;border-radius:3px;background:" + item.color + ";flex-shrink:0;";
          var lbl = document.createElement("span");
          lbl.style.cssText = "font-size:13px;color:#374151;font-weight:" + legendFw + ";";
          lbl.textContent = item.label;
          wrap.appendChild(swatch);
          wrap.appendChild(lbl);
          legend.appendChild(wrap);
        });
      return legend;
    }

    if (showLegend && legendPos === "above") wrapper.appendChild(buildLegend());

    var table = document.createElement("table");
    table.style.cssText = "width:100%;border-collapse:separate;border-spacing:" + cellGap + "px;table-layout:fixed;";

    var thead = document.createElement("thead");
    var hRow  = document.createElement("tr");
    var thCorner = document.createElement("th");
    thCorner.style.cssText = "text-align:left;font-size:" + headerFz + "px;font-weight:" + colHeaderFw + ";color:#374151;padding:12px 16px;background:#F9FAFB;border-radius:" + cellRadius + "px;";
    thCorner.textContent = headerLabel;
    hRow.appendChild(thCorner);

    for (var hi = 0; hi < pivotKeys.length; hi++) {
      var th = document.createElement("th");
      th.style.cssText = "text-align:center;font-size:" + headerFz + "px;font-weight:" + colHeaderFw + ";color:#374151;padding:12px 8px;background:#F9FAFB;border-radius:" + cellRadius + "px;";
      th.textContent = pivotLabels[pivotKeys[hi]];
      hRow.appendChild(th);
    }
    thead.appendChild(hRow);
    table.appendChild(thead);

    var tbody = document.createElement("tbody");
    for (var tri = 0; tri < rows.length; tri++) {
      var tr = document.createElement("tr");
      var tdLabel = document.createElement("td");
      tdLabel.style.cssText = "padding:12px 16px;font-size:" + headerFz + "px;font-weight:" + rowHeaderFw + ";color:#374151;background:#F9FAFB;border-radius:" + cellRadius + "px;height:" + cellH + "px;vertical-align:middle;";
      tdLabel.textContent = rows[tri].label;
      tr.appendChild(tdLabel);

      for (var tci = 0; tci < rows[tri].cells.length; tci++) {
        var cell = rows[tri].cells[tci];
        var info = getCellDisplay(cell.value, cell.rendered);
        var bgColor = getColor(info.pct);
        var td = document.createElement("td");
        td.style.cssText = "text-align:center;vertical-align:middle;font-size:" + cellFz + "px;font-weight:" + cellValueFw + ";color:" + (info.pct === null ? "#9CA3AF" : "white") + ";background:" + bgColor + ";border-radius:" + cellRadius + "px;height:" + cellH + "px;padding:8px;";
        td.textContent = info.display;
        if (cell.links && cell.links.length > 0) {
          td.style.cursor = "pointer";
          (function(links) {
            td.addEventListener("click", function(e) {
              LookerCharts.Utils.openDrillMenu({ links: links, event: e });
            });
          })(cell.links);
        }
        tr.appendChild(td);
      }
      tbody.appendChild(tr);
    }
    table.appendChild(tbody);
    wrapper.appendChild(table);

    if (showLegend && legendPos === "below") wrapper.appendChild(buildLegend());

    element.appendChild(wrapper);
    doneRendering();
  }

});