google sheets - Sparkline to change colour based on different values

Question

I'm looking to get my sparkline to change colour based on values to reflect a product expiring and to also show when a product has been delivered:

Issue Date(C10), Expiration Date(D10), Sparkline(J10), certificate status(K10):

Ideally I would like the cell to be green, and as the date moves closer to expiration I would like the cell to fill as red - Once the certificate status has been changed to "Delivered" The sparkline would change to grey. I'm a newbie to this apart from a basic sparkline, anything else produces an Error or parse error

question from:https://stackoverflow.com/questions/65845341/sparkline-to-change-colour-based-on-different-values

Answer 1

Sounds like what you want is to change color1 dynamically. You can do this by interpolating a value and concatenating to a string. Assuming input row 10, like in your question:

=SPARKLINE(NOW()-C10,
{
  "charttype","bar";
  "max",D10-C10;
  "color1",IF(
    K10="Delivered",
    "gray",
    "rgb("&INT(255*(NOW()-C10)/(D10-C10))&","&INT(255*(D10-NOW())/(D10-C10))&",0)"
  )
})

• Here, your data input is Now()-C10, which gets passed to the sparkline.
• The IF Statement overrides the color to gray if K10="Delivered".
• The rgb string sets the color to more red as the date gets closer to the expiration date and more green as the date gets closer to the Issue Date.

If you want a full gray bar upon delivery, you can set the max property to (K10<>"Delivered")*(D10-C10).