How to target a specific cell/column to change the column in a pivot table?

I have a pivot table where I am trying to turn the "Activity date for billable time" light green (can be any color) in my widget edit script, along with its corresponding value from another column, 2025-08-18, in this example. I have tried a couple of different script but nothing seems to work:

DRay any suggestions or anyone from your team have an idea?

Rafael Ferreira

Posted 11 months ago·Last reply 11 months ago

1 comment

Rafael Ferreira

OP11 months ago

Solved the issue: The order of the scripts and then us





// 1) Color the column's sub-header cell (row-1 "member"):
widget.transformPivot(
  {
    type: ['member'],                // header cells
    axis: 'columns',                 // match column members
    columns: [{
      dim: '[Program_Assessment_Response_Question.Program_Assessment_Question_Version_Name]',
      members: ['Activity date for billable time']
    }]
  },
  function (metadata, cell) {
    cell.style = cell.style || {};
    cell.style.backgroundColor = '#e6f4ea';  // light green
    cell.style.borderRadius = '4px';
    cell.style.textAlign = 'center';         // keeps it centered
  }
);

// 2) Color all value cells under that column:
widget.transformPivot(
  {
    type: ['value'],                 // data cells
    columns: [{
      dim: '[Program_Assessment_Response_Question.Program_Assessment_Question_Version_Name]',
      members: ['Activity date for billable time']
    }]
  },
  function (metadata, cell) {
    cell.style = cell.style || {};
    cell.style.backgroundColor = '#e6f4ea';
    cell.style.borderRadius = '4px';
  }
);


(function () {
  // Same dimension you used for "Activity date for billable time"
  var DIM = '[Program_Assessment_Response_Question.Program_Assessment_Question_Version_Name]';

  // The four column headers to color
  var MEMBERS = [
    'CM Assessment/Interview',
    'CM Screening and Evaluation',
    'Contact with Family Member',
    'Contact with Member'
  ];

  // Light blue
  var COLOR = '#dbeafe'; // tweak if you want a different shade

  function paintCell(_, cell) {
    cell.style = cell.style || {};
    cell.style.backgroundColor = COLOR;
    cell.style.borderRadius = '4px';
  }

  // 1) Color the column header (member cells on the columns axis)
  widget.transformPivot(
    {
      type: ['member'],
      axis: 'columns',
      columns: [{ dim: DIM, members: MEMBERS }]
    },
    paintCell
  );

  // 2) Color all data cells under those columns
  widget.transformPivot(
    {
      type: ['value'],
      columns: [{ dim: DIM, members: MEMBERS }]
    },
    paintCell
  );
})();


/*--------------------*/



(function () {
  // Text shown in the top header (row-0)
  var TOP_LABEL = 'Program Activity Time';

  // Scope to this widget only
  var root = (widget.$$container?.[0] || widget.$container?.[0] || widget.container || document);

  function centerTopHeader() {
    // find the row-0 header cell whose text matches TOP_LABEL
    var tds = root.querySelectorAll('.table-grid.table-grid--top td.table-grid__cell--row-0');
    var col = null;

    for (var i = 0; i < tds.length; i++) {
      var td = tds[i];
      var text = td.querySelector('.table-grid__content div')?.innerText?.trim() || '';
      if (text === TOP_LABEL) {
        var cls = Array.prototype.slice.call(td.classList).find(function (c) { return c.indexOf('table-grid__cell--col-') === 0; });
        if (cls) col = +cls.split('--col-')[1];
        break;
      }
    }
    if (col == null) return;

    // center ONLY that top header cell’s content
    var nodes = root.querySelectorAll(
      '.table-grid.table-grid--top td.table-grid__cell--row-0.table-grid__cell--col-' + col + ' .table-grid__content'
    );
    nodes.forEach ? nodes.forEach(centerEl) : Array.prototype.forEach.call(nodes, centerEl);
  }

  function centerEl(el) {
    el.style.setProperty('display', 'flex', 'important');
    el.style.setProperty('justify-content', 'center', 'important');
    el.style.setProperty('align-items', 'center', 'important');
    el.style.setProperty('text-align', 'center', 'important');
  }

  // run after the pivot paints
  function run() { setTimeout(centerTopHeader, 0); }

  widget.on && widget.on('domready', run);
  widget.on && widget.on('ready', run);
  widget.on && widget.on('refresh', run);

  // tiny retry in case the grid mounts a bit later
  (function tick(n){
    if (n <= 0) return;
    if (!root.querySelector('.table-grid')) return void setTimeout(function(){ tick(n-1); }, 150);
    run();
  })(30);

  // re-apply if the grid re-renders its header DOM
  var grid = root.querySelector('.sisense-pivot__multi-grid') || root;
  if (window.MutationObserver && grid) {
    var mo = new MutationObserver(function (m) {
      for (var i=0;i<m.length;i++) { if (m[i].addedNodes.length || m[i].removedNodes.length) { centerTopHeader(); break; } }
    });
    mo.observe(grid, { childList: true, subtree: true });
  }
})();