Vary Chart Colour By Value

[Qiro]

Hey guys,

So, I know we've covered this, but I'm picking up on the thread started by @Koichi_Tsuji on Radial Gauges using QuickChart.

CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:'rgb(0, 100, 0)'
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: true,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
)

I have this inspired expression that handles the generation of charts in my app perfectly. What I'd like to do is have the RGB change based on the [Percentage] value. Is this possible in AppScript?
Searching around, it appears that people have achieved the same in java here, but I honestly haven't a clue how to translate it.

Would be grateful for any help.

[Marc_Dillon]

That's not a "script". It's just a URL to the quickchart.io service, with a JSON object of parameters.

https://quickchart.io/

https://community.quickchart.io/

https://quickchart.io/documentation/

You just need to replace the rgb values section(s) with a conditional expression, like IFS() or SWITCH(), that outputs different color values for different percentage values.

https://help.appsheet.com/en/articles/2355961-ifs

https://help.appsheet.com/en/articles/2355972-switch

[Qiro]

I understand IF statements and how to use them, where I'm struggling is how to insert it into the expression. 

Positioning it in-line just throws an error.

backgroundColor:[",IF([Percentage] < 50, 'rgb(0, 100, 0)', 'rgb(100, 0, 0)'),"]

[Steve]

What error?

[Qiro]

Great question.

Chart error: ReferenceError: rgb is not defined

---

Unfortunately, this is already beyond my ken.

[Steve]

Where does that error occur? Please post a screenshot of the entire screen.

[Qiro]

Here's the full expression. I'm guessing I'm misusing the rgb option.

CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:[",IF([Percentage] < 50, 'rgb(0, 100, 0)', 'rgb(100, 0, 0)'),"]
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: false,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
)

I have set the column as an IMAGE and use it as shown here:

If I replace the entire rgb IF statement with a colour, it displays correctly.

[Qiro · Accepted solution]

Found a solution that works. It's not pretty, but it functions as expected:

The IF statement should not be in-line with the rest of the expression, instead, the CONCATENATED expression needs to be wrapped in an IF statement.

IF([Total Score] > 50,
CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:'rgb(0, 100, 0)'
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: false,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
),
CONCATENATE("https://quickchart.io/chart?c={
  type:'radialGauge',
  data:{
    datasets:[{
       data:[",NUMBER([Percentage]*100),"],
       backgroundColor:'rgb(100, 0, 0)'
      }]
    },
  options:{
    trackColor: 'rgb(220, 220, 220)',
    centerPercentage: 80,
    roundedCorners: false,
    centerArea:{text:' ",	
    NUMBER([Percentage]*100),	"%25'}
    }
  }"
))

There must be a better way, but I don't know what it is.

This expression just checks to see what the [Percentage] is and displays a static image based on the result.