Widget Scripts: Conditional Formatting

⏱️ 2 minute read

Here are a few ready-to-use widget scripts to highlight specific cells in your widget.

Replace the variables highlighted in bold with the appropriate values for your use case.

To appoint colours, you can use:
- the name of the colour
- the colour's HTML hex code
- the RGBA (or RGB) code

• Extend conditional formatting to a whole row
• Conditional formatting based on dimension value (highlights cell)
• Conditional formatting based on dimension value (highlights whole row)
• Conditional formatting for grand totals

Extend conditional formatting to a whole row

widget.on('ready', function(se, ev) {

//specify the column where the condition is applied
    panelName = 'Total Unit Price' 

   var colIndex = -1
    $('.pivot-scroller table tbody tr', element).each(function(index, trElement){ 
        if(index == 0)
        {
            $(trElement).find('td .table-grid__content .table-grid__content__inner').each(function(tdIndex, tdElement){
                if ($(tdElement).text() == panelName){
                    colIndex = tdIndex
                }
            })
        }
        else{
            if(colIndex >= 0){
                bgColor = $(trElement).find('.table-grid__cell--col-' + colIndex).css('backgroundColor')
                $(trElement).find('td').each(function(tdIndex, tdElement){
                     $(tdElement).css('backgroundColor', bgColor)
                })

                $('.table-grid--rows table tbody tr', element).each(function(rowIndex, rowElement){
                    if(rowIndex > 0 && index == rowIndex){
                        $(rowElement).find('td').css('backgroundColor', bgColor)
                    }
                })
            }
        }

    })
})

Conditional formatting based on dimension value (highlights cell)

widget.on('ready', function (se, ev) {
 setTimeout(function () {
   var e = element;
   $("tbody tr", e).not('.wrapper, .p-head-content').each(function () {
     var $row = $(this);
     
     $row.children().each(function(index) {
        if (index === 0 || index === 1) {   //specify the columns to check
         var cell = $(this);
         var text = cell.text().trim().toLowerCase();
         
         cell.css("background-color", "");
         
     // specify key words (all lower case) and background colors
         if (text.includes("KEY_WORD_1")) {   
           cell.css("background-color", "green");   
         } else if (text.includes("KEY_WORD_2")) {   
           cell.css("background-color", "#ffa500");   
         } else if (text.includes("KEY_WORD_3")) {   
           cell.css("background-color", "rgba(87, 14, 208, 0.77");   
         }
       }
     });
   });
 }, 1000);
});

Conditional formatting based on dimension value (highlights whole row)

widget.on('ready', function (se, ev) {
 setTimeout(function () {
   var e = element;
   $("tbody tr", e).not('.wrapper, .p-head-content').each(function () {
     var $row = $(this);
     var rowText = $row.text().trim().toLowerCase(); 
     
     $row.children().css("background-color", "");
     
     // specify key words (all lower case) and background colors
     if (rowText.includes("KEY_WORD_1")) {
       $row.children().css("background-color", "green");
     } else if (rowText.includes("KEY_WORD_2")) {
       $row.children().css("background-color", "#ffa500");
     } else if (rowText.includes("KEY_WORD_3")) {
       $row.children().css("background-color", "rgba(87, 14, 208, 0.77)");
     }
   });
 }, 1000);
});