Search code examples
google-sheets

How to add background color and a number in sparkline cell?

I have a whole list of begin and end-dates. For each pair, I would like to see the progress based on the current date in a sparkline:

   A            B
1  BeginDate    EndDate
2  21.01.2023   21.10.2023

For instance, if today is 30.04.2023 (I use DD.MM.YYYY format), then the sparkline looks like:

enter image description here

The formula I use for this is:

=SPARKLINE(MAX(0,TODAY()-A2),{"charttype","bar";"max",MAX(1,B2-A2)})

However, I am unable to set a background color, like below:

enter image description here

  • Q1: How can I update my formula so that the (100%) beckground color is blue and the progress bar itself is orange (like now; it seems to be a default color bc I haven't defined any color?)
  • Q2: Is it possible to - in addition to the progress bar - have the percentage been written as number in the same cell (see mockup below)?

enter image description here

Any help is greatly appreciated!

Solution

  • Q1: Rather than defining the maximum for the bars in the options argument, add a second data point to the data argument corresponding to the difference between the end date and today using an array literal:

    =SPARKLINE({MAX(0,TODAY()-A2),B2-TODAY()},{"charttype","bar"})

    Q2: I'm 99.9% certain this is not possible, I'm afraid.