0

Hoping someone can help. I have been working on this issue for a few days now (lots of googling and playing with the code formatting).

What I'm trying to accomplish: I have a sharepoint site to track the status of items and am trying to format the rows of data using the $Expiration (date) column. Depending on the date listed in the expiration I would like the rows to come up in different colors so that as the expiration date approaches the colors change to progressively more red.

Below is the code so far... I have tried other versions but none are performing as expected.

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "if": {
    "operator": ">",
    "operands": [
      {
        "operator": "Date()",
        "operands": [
          {
            "operator": "toDateString()",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  "[$Expiration]"
                ]
              }
            ]
          }
        ]
      },
      {
        "operator": "Date()",
        "operands": [
          {
            "operator": "toDateString()",
            "operands": [
              {
                "operator": "Date()",
                "operands": [
                  {
                    "operator": "+",
                    "operands": [
                      "@now",
                      15768000000
                    ]
                  }
                ]
              }
            ]
          }
        ]
      }
    ]
  },
  "then": {
    "additionalRowClass": "sp-css-backgroundColor-BgMintGreen sp-field-fontSizeSmall sp-css-color-MintGreenFont"
  },
  "else": {
    "if": {
      "operator": ">=",
      "operands": [
        {
          "operator": "Date()",
          "operands": [
            {
              "operator": "toDateString()",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    "[$Expiration]"
                  ]
                }
              ]
            }
          ]
        },
        {
          "operator": "Date()",
          "operands": [
            {
              "operator": "toDateString()",
              "operands": [
                {
                  "operator": "Date()",
                  "operands": [
                    {
                      "operator": "+",
                      "operands": [
                        "@now",
                        7776000000
                      ]
                    }
                  ]
                }
              ]
            }
          ]
        }
      ]
    },
    "then": {
      "additionalRowClass": "sp-css-backgroundColor-BgGold sp-field-fontSizeSmall sp-css-color-GoldFont"
    },
    "else": {
      "if": {
        "operator": ">=",
        "operands": [
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "[$Expiration]"
                    ]
                  }
                ]
              }
            ]
          },
          {
            "operator": "Date()",
            "operands": [
              {
                "operator": "toDateString()",
                "operands": [
                  {
                    "operator": "Date()",
                    "operands": [
                      "@now"
                    ]
                  }
                ]
              }
            ]
          }
        ]
      },
      "then": {
        "additionalRowClass": "sp-css-backgroundColor-BgCoral sp-field-fontSizeSmall sp-css-color-CoralFont"
      },
      "else": {
        "additionalRowClass": "sp-css-backgroundColor-redDark sp-field-fontSizeSmall sp-css-color-redDarkFont"
      }
    }
  }
}

Yes, I have looked at the samples given and have tried using the excel formatting:

{
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/row-formatting.schema.json",
  "additionalRowClass": "=if(([$Expiration] >= (@now + 15768000000)),'sp-css-backgroundColor-BgMintGreen sp-field-fontSizeSmall sp-css-color-MintGreenFont', if([$Expiration] >= (@now + 7776000000)),'sp-css-backgroundColor-BgGold sp-field-fontSizeSmall sp-css-color-GoldFont', if([$Expiration] >= @now),'sp-css-backgroundColor-BgCoral sp-field-fontSizeSmall sp-css-color-CoralFont','sp-css-backgroundColor-redDark sp-field-fontSizeSmall sp-css-color-redDarkFont')"
}

But again this doesn't work as expected either. I know the JSON formatting is correct, but it still will not change the rows as intended.

Any help is appreciated.

4
  • 1
    Are you using SP 2019 or SP online? Jan 10, 2022 at 17:22
  • if,then, and else are not valid JSON properties in formatting. Excel style expressions and view formatting (rowFormatter and additionalRowClass) are not supported in SP2019. Please edit your question to include the version of SharePoint you are using and to state what your format rules are (X number of days after/before expiration color should be etc.) Jan 10, 2022 at 21:12
  • Share point online (office 365), Expiration is more than 6 months away (green), Expiration is between 90 days and six months (yellow/gold), Expiration in less than 90 days (light red), Expiration has passed (Dark Red). Jan 11, 2022 at 15:11
  • Hi Troy Pickens. Thanks for your reply. I have updated answer under initial My Answer. Hope this can help you to solve issue. Have a nice day! Feb 2, 2022 at 5:25

1 Answer 1

0

Here is the difference on JSON formatting between SharePoint 2019 and SharePoint Online.

  • Excel-style expressions are not supported on SP 2019.
  • View formatting is currently supported only in SharePoint Online.

Therefore, the "rowFormatter" and "additionalRowClass" do not work in SharePoint Server 2019.

For SharePoint 2019, you can use Client Side Rendering (CSR) which represents a rendering engine for list views.

Note: Use classic SharePoint mode

You can refer the code samples given in below links and modify it according to your conditions:

=========================== Updated Answer =======================

Please use the below JSON formatting:

Note: Number of days *24 *60 *60 *1000. For example: six months = (6 * 30) *24 *60 *60 *1000

{
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "div",
    "txtContent": "@currentField",
    "style": {
       "background-color": "=if(@currentField < @now, '#B22222', if(@currentField > @now && @currentField < @now+7776000000, '#F08080', if(@currentField > @now+7776000000 && @currentField < @now+15552000000, '#FFFF00', if(@currentField > @now+15552000000, '#90EE90', ''))))"
    }
}

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.