Make Bar chart from single column of Spreadsheet data

Question

I have a big spreadsheet with a column of categorized data. I want to create a bar chart for each column representing the count of the different values in that column. I tried to automate this process with Google Apps Script, but the result I get is quite wrong.

Here is the code I am using

function myFunction() {

var responseSpreadSheet = SpreadsheetApp.openByUrl("https://mySpreadsheetLink")
                          .getActiveSheet();
Logger.log(responseSpreadSheet.getName());

var titles = responseSpreadSheet.getRange(1, 4, 1, 25).getValues(); 

var barChart = responseSpreadSheet.newChart()
               .setChartType(Charts.ChartType.BAR)
               .setOption('title', titles[0][0])
               .addRange(responseSpreadSheet.getRange("D2:D57"))
               .setPosition(5, 5, 0,0)
               .build();


responseSpreadSheet.insertChart(barChart);
}

Here is the spreadsheet with the embedded bar chart:

And the desired outcome will be this

How do I create this type of chart over my column?

Answer 1

EmbeddedCharts do not expose all the capabilities available to Spreadsheet Charts, nor all the features of the Visualization API. The specific limitations you're up against here:

• EmbeddedCharts only use spreadsheet Ranges for data sources.

• The automatic Aggregation settings supported in the Sheets UI are not available programmatically.

  The desired chart isn't simply graphing a single column of data, it's effectively generating a table from it through Aggregation. When you select a single column of text cells, the default chart will be bar chart titled "Count of [column header]", with "Aggregate column" enabled, like this:

How to get the same results via script

Let's say Column A consists of a list of respondents' favourite pets:

Favourite Pet
-------------
Cat
Cat
Dog
Alpaca
Budgie
Budgie
Cat
Dog
Hamster
Cat
Gerbil
Rabbit
Dog
Rabbit
Cat
Dog

The sum-aggregate data would look like:

Favourite Pet   Count
---------------------
Cat               5
Dog               4
Alpaca            1
Budgie            2
Hamster           1
Gerbil            1
Rabbit            2

Option 1: Spreadsheet functions

Because of the limitations of EmbeddedCharts, the data to be displayed must be in a "Table", a Range or set of Ranges, so you need to meet that requirement. One way would be to use a Sheets QUERY() function, as in Counting number of occurrences in column?:

=QUERY({A:A,A:A},"select Col1, count(Col2) where Col1 != '' group by Col1 label count(Col2) 'Count'",1)

To insert a chart programmatically, use the range produced by the above QUERY in your existing script.

Option 2: All by Script

The script needs to generate a similar table of aggregate data. There are numerous examples of that in JavaScript, such as Counting the occurrences of JavaScript array elements. With that, and an array transposing function from Google Spreadsheet Script - How to Transpose / Rotate Multi-dimensional Array?, we can put that together like this:

/**
 * Generate a table with summed categories (counts).
 *
 * @param {Object[][]} data   Single column of values to be counted.
 *
 * @returns {Object[][]}      Two columns, categories and counts
 */
function sumAggregate( data ) {
  // transpose spreadsheet column; we want just column 0.
  var arr = transpose( data )[0];

  // Magically aggregate array items. From https://stackoverflow.com/a/28832203/1677912
  var agg = arr.reduce(function(countMap, word) {countMap[word] = ++countMap[word] || 1;return countMap}, {});

  // Convert aggregated data into a 2-D array
  var result = [];
  for (var item in agg) {
    result.push( [item,agg[item]] );
  }
  return result;
}

// From https://stackoverflow.com/a/16705104/1677912
function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c]; }); });
}

With that function taking care of generating the table of sums required for the chart, we just need to generate the data and place it into the spreadsheet.

function generateChart() {
  var sheet = SpreadsheetApp.getActiveSheet();

  var chart = sheet.getCharts()[0];
  var options = chart.getOptions();
  var a = JSON.stringify(options.get('width'))

  var data = sheet.getRange("A1:A17").getValues();
  var header = data.splice(0, 1);                // remove header row
  var aggregateData = sumAggregate( data );
  aggregateData.unshift([header[0][0],"Count"]); // replace & expand header row

  // Store in spreadsheet
  var tableRange = sheet.getRange(1, 3, aggregateData.length, aggregateData[0].length);
  tableRange.setValues(aggregateData);

  var barChart = sheet.newChart()
               .setChartType(Charts.ChartType.BAR)
               .setOption('title', 'Count of '+header[0][0])
               .setPosition(5, 5, 0,0)
               .addRange(tableRange)
               .build();

  sheet.insertChart(barChart);  
}

Result: