Text Value in Pivot Table - Colour Coding Text Value based on cell value

[Erin Haig]

Hi Everyone,

I am attempting to do some data mapping in a pivot table. 

I have set up up where the pivot table pull text values and sorts by using =JOIN(CHAR(10),ColumnName)

With the text values that show up, I would like to colour code them based a value in a separate cell.  Does anyone know how to do this?  

I've tried the conditional formatting, however that only changes the cell versus the pulled data.  

Thanks!

Erin

[Clark Lind]

I think I know what you mean, but can you provide a simple example? 

[Erin Haig]

For example in a Column C if there is an IF/Then statement and then there is a text value  eg:  =IFS(C12<5,"No",C12<6,"Yes") is there a way to make the text value either No (red) or Yes (Green) automatically when pulled into the pivot table?

[Clark Lind]

From what I have been able to see, I can't find a way of manipulating just parts of a display value of a cell without messing up the underlying formula. 
If, once calculated, it never changes (i.e., you don't need the formula any more, just the value), then this can be done with Apps script, but you will lose the formula and ability to update the data later on.

[Clark Lind]

If you are say, running reports weekly, and just want it colored for the weekly report, you could duplicate the pivot table tab and run the code to make the text color changes. This will keep your original data untouched. Then just delete the duplicated tab when no longer needed. 

That may give you some ideas to play with anyway. 
I tested this code and it works. For some reason, when I try to change a portion of text that doesn't start at the beginning, it doesn't work, so as a workaround, I change the whole text to the desired color (red or green), then go back and change any text that comes before it back to black. This also assumes the "yes" or "no" are at the end of the cell contents.

function colorCells() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var ws = ss.getSheetByName('Sheet1');//replace with your tab name

  var range = ws.getRange('G6:G9');//replace with your pivot table column range. The below code assumes it is all in a single column

  var data = range.getDisplayValues();

//loop through each cell

  for (var i = 0; i < data.length; i++) {

 //   console.log(data[i][0])

    var dataLen = data[i][0].length;

    var end = 0;

    var textColor = ""

      if (data[i][0].indexOf('No') > 1) {

        end = dataLen - 2;

        textColor = "red"

       }

      if (data[i][0].indexOf('Yes') > 1) {

        end = dataLen - 3

        textColor = "green"

      }

//set the cell color to red or green

    var newColor = SpreadsheetApp.newTextStyle()

      .setForegroundColor(textColor)

      .build();

    var richText1 = SpreadsheetApp.newRichTextValue()

      .setText(data[i][0])

      .setTextStyle(0, dataLen, newColor)

      .build();

    range.getCell(i+1,1).setRichTextValue(richText1);

//set front portion of cell content back to black

    var black = SpreadsheetApp.newTextStyle()

      .setForegroundColor('black')

      .build();

    var richText2 = SpreadsheetApp.newRichTextValue()

      .setText(data[i][0])

      .setTextStyle(0, end, black)

      .build();

    range.getCell(i+1,1).setRichTextValue(richText2);

      }

}