4

I am using Google Scripts to create a chart in Google Sheets however I have been having difficulties for a couple days now and I think it comes down to my lack of understanding of the syntax used by Google Scripts and its embedded chart object.

From my understand - and according to the syntax below:

A photo from the documentation

To set the candlesticks rising colour to green I would do the following:

.setOption('candlestick', {risingColor: {fill: '#31d831'}})

However this doesn't work! I'd assume the syntax works like this:

candlestick.risingColor.fill
└────┬────┘ └────┬────┘ └──┴──────────────┐
     └───────┐   └──────────┐             │
.setOption('candlestick', {risingColor: {fill: '#31d831'}})

I made this assumption because this code here works:

.setOption('vAxes', {0: {viewWindow: {min: 1}}})

The documentation is similar:

enter image description here

vAxis.viewWindow.min
└─┬─┘ └────────┬┘└─┴───────────────────┐
  └──────────┐ └──────────┐            │
.setOption('vAxes', {0: {viewWindow: {min: 1}}})
                     |
┌────────────────────┴──────────────────────────────┐
This zero refers to the data set I would like to edit

Sidenote: While typing this I have also realised that I've spelt Axis wrong as Axes however this works Even through the documentation says Axis

I am thoroughly confused and I cannot find an answer anywhere my google-fu might of just been weak but I just cannot figure this out.

This is a similar question however they are not using embedded sheets and even when I copy their syntax it doesn't work!

Thank you in advance any help would be greatly appreciated!

Edit: Here is the code I am using to generate my chart:

const chart = data.newChart()
    .setChartType(Charts.ChartType.CANDLESTICK)
    .addRange(candle.getRange("a:e"))
    .setOption('vAxes', {0: {viewWindow: {min: smallestValue-1, max: largestValue+1}}})
    .setOption('candlestick', {risingColor: {stroke: '#31d831'}, fallingColor: {stroke: '#d83131', fill: '#ffffff'}}) // This is the line that doesn't work because I am unsure of the syntax!
    .setOption('height', 735)
    .setOption('width', 1175)
    .setPosition(1, 1, 0, 0)
    .build()
  
candle.insertChart(chart)

This syntax is different to the syntax in the second answer in the possible duplicate.

The accepted answer in that possible duplicate asks the user to export the chart and grab the options from there after creating it with the charts GUI in Google Sheets. However you cannot edit the colours in google sheets GUI for the Candlestick chart!

I know colouring is possible for the candlestick chart as the example in the documentation does it (This syntax here does not work when using .setOption() as explained in my original question)

I have also tried this syntax which is seen in this question:

var chart = data.newChart()
    .setChartType(Charts.ChartType.CANDLESTICK)
    .addRange(candle.getRange("a:e"))
    .setOption('vAxes', {0: {viewWindow: {min: smallestValue-1, max: largestValue+1}}})
    .setOption('candlestick.risingColor.stroke', '#31d831')
    .setOption('candlestick.fallingColor.stroke', '#d83131')
    .setOption('candlestick.fallingColor.fill', '#ffffff')
    .setOption('height', 735)
    .setOption('width', 1175)
    .setPosition(1, 1, 0, 0)
    .build()
  
candle.insertChart(chart)

However this doesn't work either

Final edit for anyone else: I have come to the conclusion that it is not possible to do what I am trying to do and with the help of TheMaster he has pointed me in the direction of using and taking advantage of the sidebar I have added this as an edit instead of the answer as maybe this might become possible one day and if it does It'd be nice to get this properly answered.

Good luck to anyone else looking into this in the future, keep me updated on its possibility!

pfych
  • 850
  • 1
  • 13
  • 32
  • 1
    Possible duplicate of [Google Apps Script: How to set "Use column A as labels" in chart embedded in spreadsheet?](https://stackoverflow.com/questions/13594839/google-apps-script-how-to-set-use-column-a-as-labels-in-chart-embedded-in-spr) – TheMaster Mar 08 '19 at 14:11
  • That question doesn't explain the syntax or answer my question! – pfych Mar 08 '19 at 22:23
  • 1
    Not straightaway. If you can manually do it, then it's possible to decipher the syntax as explained in that question. So, it's a "possible" duplicate. – TheMaster Mar 08 '19 at 22:37
  • Ah I see where your coming from! However neither answers apply to my issue as the first requires the use of the customise GUI in sheets (Which candlestick chart doesn't have for colour) and the second is just a code snippet without explanation on how it works. Thank you for pointing this answer out and I will look into it however it doesn't answer this question. – pfych Mar 09 '19 at 00:54
  • I have edited my question with an example and some explanation as to why it isn't a duplicate – pfych Mar 09 '19 at 01:37
  • 1
    Retracted my close-duplicate vote. But, If it isn't possible with GUI, It probably won't be possible with scripts either. You might wanna look for alternatives- like, showing the chart in a sidebar, where html/js is accessible freely. – TheMaster Mar 09 '19 at 02:31
  • That might be the case... The google chart documentation is a bit of a joke and really needs a rework. I will keep trying though thanks for the help – pfych Mar 09 '19 at 03:00
  • 1
    True. In case it wasn't clear, The screenshot documentation is for Google charts and not for the embedded chart in a spreadsheet(which may or may not have those features). So, a sidebar html Google chart can do all that you want. – TheMaster Mar 09 '19 at 09:54
  • Thanks for all the help, I'll move my charts over to the sidebar system. – pfych Mar 10 '19 at 00:45

0 Answers0