Dual Y-Axis Chart in Google Charts API: Mimicking Google Sheets Functionality

Other Questions Google Sheets
1

I’ve been trying to create a chart in the Google Charts API that resembles what Google Sheets offers. My goal is to display two y-axes each with its own scale and a shared x-axis. Although I managed to build a combination of bar and line charts, I’m unable to implement a second y-axis.

Here’s a sample of my current implementation:

function drawChart() {
  var chartData = [
    ['Date', 'Sales', 'Profit'],
    ['Jan 1',  100,    0.5],
    ['Feb 1',  150,    0.7],
    ['Mar 1',  200,    0.9],
    ['Apr 1',  250,    1.1],
    ['May 1',  300,    1.3]
  ];

  var options = {
    title: 'Monthly Sales and Profit',
    hAxis: {title: 'Date'},
    vAxis: {title: 'Sales', format: '#'},
    seriesType: 'bars',
    series: {1: {type: 'line'}}
  };

  var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
  chart.draw(google.visualization.arrayToDataTable(chartData), options);
}

In this setup, the left y-axis represents sales values ranging from 0 to 300. How can I add a right y-axis for profit that scales from 0 to 1.5?

2

I’ve actually run into this exact issue before when working on a project for a client. The trick is to use the ‘vAxes’ option instead of ‘vAxis’, and then specify which series corresponds to each axis. Here’s what worked for me:

options = {
vAxes: {
0: {title: ‘Sales’, format: ‘#’},
1: {title: ‘Profit’, format: ‘#.##’}
},
series: {
0: {targetAxisIndex: 0},
1: {targetAxisIndex: 1, type: ‘line’}
}
}

This sets up two y-axes and tells the chart which data goes with each. You might need to play around with the formats and ranges to get it looking just right. Also, don’t forget to adjust your chart type to ‘ComboChart’ if you haven’t already. That should get you pretty close to what Google Sheets can do.

3

I’ve tackled a similar challenge before, and I found that the key to implementing a dual y-axis in Google Charts API is to use the ‘vAxes’ option instead of ‘vAxis’. Here’s how you can modify your code:

var options = {
  title: 'Monthly Sales and Profit',
  hAxis: {title: 'Date'},
  vAxes: {
    0: {title: 'Sales', format: '#'},
    1: {title: 'Profit', format: '#.#'}
  },
  series: {
    0: {targetAxisIndex: 0},
    1: {targetAxisIndex: 1, type: 'line'}
  },
  seriesType: 'bars'
};

This setup creates two vertical axes and assigns each series to the appropriate axis. The ‘targetAxisIndex’ property in the ‘series’ option determines which axis each series uses. Remember to adjust the format of each axis as needed. This should give you a chart similar to what Google Sheets offers, with sales on the left y-axis and profit on the right.

4

hey alex, i’ve done this before. try using vAxes instead of vAxis in ur options. something like this:

vAxes: {
0: {title: ‘Sales’, format: ‘#’},
1: {title: ‘Profit’, format: ‘#.#’}
},

then set targetAxisIndex for each series. that should give u 2 y-axes like in sheets. goodluck!