2

I'm making a Sheet to be used by elementary students, to track some energy "usage" in their class, based on a date. To make it dead easy, I've created dropdowns for their choices (text).

In order to make a graph, I've changed the choices into numbers ("All"=3, "Some"=2, "None"=1, "N/A"=0) onto another tab (using Apps Script). This makes a nice graph, but the vertical axis of course shows the numbers. I'm hoping there is a way to swap them out for the text.

I've tried the 'ticks' option, but nothing changes:

var vAxisOptions = {
    0: {
      ticks: [{v:0, f:'N/A'}, {v:1, f:'None'}, {v:2, f:'Some'}, {v:3, f:'All'}, {v:4, f:''}],
      maxValue: 4,
      gridlines: {count: 5} //add an extra line of space to see the lines better
    }
  };

And then apply it by .setOption('vAxes', vAxisOptions).

I suspect this just isn't possible, but is it? Thanks!!

Example: https://docs.google.com/spreadsheets/d/1zOeXJy92LdCmhdLW6MmLA0JCNVk34kk50B3tQGACwlY/edit?usp=sharing

p.s. Click the "View Results" button to make the graph if you make data changes

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Chris Metropolis
  • 21
  • 2
  • it's possible, but I've never used in sheets before. when using `ticks` don't need the other options for `maxValue` or `gridlines` -- try removing those. if that still doesn't work, try using `vAxis` instead of `vAxes` -- `.setOption('vAxis', vAxisOptions)` -- and --> `var vAxisOptions = {ticks: [{v:0, f:'N/A'}, {v:1, f:'None'}, {v:2, f:'Some'}, {v:3, f:'All'}, {v:4, f:''}]};` – WhiteHat Aug 21 '20 at 17:19
  • Thanks, @WhiteHat; I did try removing the other options, and vAxis on its own - no dice. Google has nicely documented this option, but without a code example. And I read somewhere else that to add a vertical title, using vAxis doesn't work, but vAxes does - even though the docs say you can. So infuriating! – Chris Metropolis Aug 21 '20 at 18:39
  • Perhaps this works when using Google visualization (I found an older answer of yours @WhiteHat [link](https://stackoverflow.com/questions/41464716/google-chart-formatted-dynamic-h-axis-is-not-working)) - but not with an embedded chart? – Chris Metropolis Aug 21 '20 at 19:28
  • Tangentially related: https://stackoverflow.com/questions/13594839 If you can do this manually, Reverse engineer to see the options in source code. – TheMaster Aug 21 '20 at 21:24
  • Thanks, @TheMaster - I did see that post, and I think I will try that route - thanks for reminding me. – Chris Metropolis Aug 24 '20 at 13:07
  • Ok, perhaps I'm almost there; digging in the js I see `'chartJson': '{\"view\":{\"columns\":[0,1,2,3,4]},\"dataTable\":{\"parsedNumHeaders\":1,\"rows\":[{\"c\":[{\"v\":\"Date(2020,7,17)\",\"f\":\"17\/08\/2020\"}` Last bit of help: how would I implement view.columns in .setOption()? I've tried it a couple ways, but nothing changes. Thanks! – Chris Metropolis Aug 24 '20 at 15:29
  • Have you found the answer? If so, please provide the answer so more people can benefit from it. If not, can you share more details on what is going on now and what changes have you made? – Kessy Aug 31 '20 at 06:58
  • @Kessy, no, I have not found the answer. It seems so close, but I'm not sure how to use the view.columns values with setOption. I've asked in other forums, too. I suspect Google has not exposed that part of the API in embedded sheets. I think the "ticks" property should be the way to do it, but has no effect (someone forgot to implement it?) – Chris Metropolis Aug 31 '20 at 15:30

1 Answers1

1

There is this Google Issue tracker issue on this matter that it is being worked on.

You can go there and click on the star next to the title of the issue so you will get updates on the issue.

Kessy
  • 1,894
  • 1
  • 8
  • 15