Google Chart with Google Spreadsheet

Question

I would like to create a custom chart with the data in Google Spreadsheet. However it didn't work so well. Could you give me some advice?

This is the data structure of the attendance data in my spreadsheet

Date   | John | Allen | Danny
11 Feb | Yes  | No    | Yes
12 Feb | No   | Yes   | Yes
13 Feb | Yes  | Yes   | Yes

I want to create a attendance chart for each day, showing the sum of all days (e.g. 2 ppl in 11 Feb, 2 ppl in 12 Feb). I know how to obtain the data but how can I do the sum in Google Chart script?
I want to create a attendance chart for each person, under a period of time (e.g. Feb/Mar). In addition I would like to create a date filter. Again I have trouble summing up the 'Yes' for each person.

Thanks in advance

asgallant asgallant · Accepted Answer · 2014-02-28T18:19:56

To create a chart of attendance per day, you need to use a DataView with a calculated column the sums the "Yes"s for each day:

var view = new google.visualization.DataView(data);
view.setColumns([0, {
    type: 'number',
    label: 'Attendance',
    calc: function (dt, row) {
        // assumes all columns after the first are yes/no columns for tracking attendance
        var attendance = 0;
        for (var i = 1; i < dt.getNumberOfColumns(); i++) {
            attendance += (dt.getValue(row, i) == 'Yes') ? 1 : 0;
        }
        return attendance;
    }
}]);

To get attendance by person, you need to use the google.visualization.data.group function:

var groupColumns = [];
for (var i = 1; i < data.getNumberOfColumns(); i++) {
    groupColumns.push({
        type: 'number',
        label: data.getColumnLabel(i),
        aggregation: function (vals) {
            var attendance = 0;
            for (var j = 0; j < vals.length; j++) {
                attendance += (vals[j] == 'Yes') ? 1 : 0;
            }
            return attendance;
        }
    });
}
var groupedData = google.visualization.data.group(data, [{
    type: 'number',
    column: 0,
    modifier: function () {return 0;}
}], groupColumns);

Google Chart with Google Spreadsheet

1 Answers