CODEWITHSUNDEEP
javascriptgoogle-sheets
bugmagnet
bugmagnet

Reputation: 7769

How do I get see the zero value in a Google Sheets Chart?

In this list of "Dogs on tap" we have Shitzu but none on tap. The charting tool in Google Sheets doesn't even acknowledge that we have Shitzu. Nevertheless, I would like the fact that we have the breed (even if we have none on tap at the moment) reflected in the pie chart.

Data and chart

I have read about sliceVisibilityThreshold and have fiddled with someone else's work such that if I change

        var options = {
          title: 'My Daily Activities'
        };

to

        var options = {
          title: 'My Daily Activities',
          sliceVisibilityThreshold : 0
        };

the legend will at least acknowledge the zero value. Now how do I get that same behaviour into my Google Sheets chart?

Upvotes: 0

Views: 5073

Answers (1)

iansedano
iansedano

Reputation: 6481

You can use a "close to zero" value:

enter image description here

To take this further you could use and onEdit Apps Script simple trigger to change all 0 values to 0.00000...01 while keeping the display at only a few significant figures, so it displays as 0 too.

function onEdit(e) {
  let s = SpreadsheetApp.getActive() // This line can be deleted after authorization
  let rng = e.range
  if (e.value == 0){
    rng.setValue(0.00000000000001)
  }
}

Run this function once in the editor to grant the authorizations necessary. Then it should automatically replace all 0 values behind the scenes, and will show up in the chart after a short delay:

enter image description here

Ideally there would be an option, though it could be argued that if something occupies 0% of the pie, it shouldn't show up in a pie chart. In any case, you can always make a feature request if you think it should have its own option.

Reference

Upvotes: 1

Related Questions