Stacked Bar Chart

[Samantha Zumwalt]

I am wanting to make a stacked bar chart for my agents to see how many orders each department is contributing towards the one common goal for the month. I want something more sophisticated than what is available in Google Sheets. Can someone help me create this please? 

I have posted the sheet that I am using for reference. The data being used for the chart is L3:L8. 

https://docs.google.com/spreadsheets/d/1F7_JGyhDQAq0yk0u20haUX-lc0G2CJhmdbCk8b-fkr0/edit#gid=1820859385

[Ray Thomas]

Because Google Visualizations and Google Sheets both use Query language - https://developers.google.com/chart/interactive/docs/querylanguage -  you can either extract the data you use in the Sheet or do it in the code when drawing the chart.

I used your Sheet and used a query in the chart code to extract the data. The only problem was that the data table had to be transposed to get it to work properly. Luckily, there's a function to do that.

I made a working example at to do what I think you are trying to achieve at http://brisray.com/test/zumwalt.htm

Here's the code to do what I think you are trying to achieve:

<script type="text/javascript" src="https://www.gstatic.com/charts/loader.js"></script>

<script type="text/ecmascript">

    google.charts.load("current", {packages:["corechart"]});

google.setOnLoadCallback(drawChart);

function transposeDataTable(dataTable) {

//step 1: let us get what the columns would be

var rows = [];//the row tip becomes the column header and the rest become

for (var rowIdx=0; rowIdx < dataTable.getNumberOfRows(); rowIdx++) {

var rowData = [];

for( var colIdx = 0; colIdx < dataTable.getNumberOfColumns(); colIdx++) {

rowData.push(dataTable.getValue(rowIdx, colIdx));

}

rows.push( rowData);

}

var newTB = new google.visualization.DataTable();

newTB.addColumn('string', dataTable.getColumnLabel(0));

newTB.addRows(dataTable.getNumberOfColumns()-1);

var colIdx = 1;

for(var idx=0; idx < (dataTable.getNumberOfColumns() -1);idx++) {

var colLabel = dataTable.getColumnLabel(colIdx);

newTB.setValue(idx, 0, colLabel);

colIdx++;

}

for (var i=0; i< rows.length; i++) {

var rowData = rows[i];

console.log(rowData[0]);

newTB.addColumn('number',rowData[0]); //assuming the first one is always a header

var localRowIdx = 0;

for(var j=1; j< rowData.length; j++) {

newTB.setValue(localRowIdx, (i+1), rowData[j]);

localRowIdx++;

}

}

return newTB;

  }

function drawChart() {

     var queryString = encodeURIComponent('SELECT J, L LIMIT 6 OFFSET 1 ');

     var query = new google.visualization.Query( 'https://docs.google.com/spreadsheets/d/1F7_JGyhDQAq0yk0u20haUX-lc0G2CJhmdbCk8b-fkr0/gviz/tq?gid=1820859385&headers=1&tq=' + queryString);

      query.send(handleQueryResponse);

    }

     function handleQueryResponse(response) {

      if (response.isError()) {

        alert('Error in query: ' + response.getMessage() + ' ' + response.getDetailedMessage());

        return;

      }

     var Options = {

isStacked: 'percent',

     };

      var data = response.getDataTable();

  var transposedData = transposeDataTable(data);

      var chart = new google.visualization.BarChart(document.getElementById('chart_div'));

      chart.draw(transposedData, Options);

}   

</script>

Queries are very useful. I used them to create some of the sheets at https://docs.google.com/spreadsheets/d/1uoYZyzlsWEiJa6jtWnoR781hm49AdDm4P3TUX0uou3I/edit#gid=0 which are used to draw the graphs on https://www.indstate.edu/business/SMIFC/research/SMIFCstats