-
Notifications
You must be signed in to change notification settings - Fork 0
/
aggregateChartAll.gs
119 lines (101 loc) · 3.91 KB
/
aggregateChartAll.gs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
function aggregateData() {
var sheet = SpreadsheetApp.getActiveSheet();
var data = sheet.getDataRange().getValues();
var numRows = data.length;
// create an object to hold the aggregate data
var aggregateData = {};
// loop through each row of the data
for (var i = 1; i < numRows; i++) {
var row = data[i];
var date = row[0];
var time = row[1];
var temp = row[2];
var co2 = row[3];
var pressure = row[4];
var particulate = row[5];
// get the hour of the time value
var hour = new Date(time).getHours();
// create a key for the date and hour combination
var key = date + ' ' + hour;
// if the key doesn't exist in the aggregate data object yet, create it
if (!aggregateData[key]) {
aggregateData[key] = {
date: date,
time: hour + ':00:00',
temps: [],
co2s: [],
pressures: [],
particulates: [],
meanTemp: '',
meanCO2: '',
meanPressure: '',
meanParticulate: ''
};
}
// add the temperature, CO2, pressure, and particulate to the arrays for this key
aggregateData[key].temps.push(temp);
aggregateData[key].co2s.push(co2);
aggregateData[key].pressures.push(pressure);
aggregateData[key].particulates.push(particulate);
}
// loop through each key in the aggregate data object
for (var key in aggregateData) {
var temps = aggregateData[key].temps;
var co2s = aggregateData[key].co2s;
var pressures = aggregateData[key].pressures;
var particulates = aggregateData[key].particulates;
// calculate the mean temperature, CO2, pressure, and particulate for this key
var meanTemp = temps.reduce(function(sum, value) {
return sum + value;
}, 0) / temps.length;
var meanCO2 = co2s.reduce(function(sum, value) {
return sum + value;
}, 0) / co2s.length;
var meanPressure = pressures.reduce(function(sum, value) {
return sum + value;
}, 0) / pressures.length;
var meanParticulate = particulates.reduce(function(sum, value) {
return sum + value;
}, 0) / particulates.length;
// update the mean values in the aggregate data object
aggregateData[key].meanTemp = meanTemp;
aggregateData[key].meanCO2 = meanCO2;
aggregateData[key].meanPressure = meanPressure;
aggregateData[key].meanParticulate = meanParticulate;
}
// write the aggregate data to a new sheet
var newSheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
newSheet.getRange(1, 1).setValue('dateF');
newSheet.getRange(1, 2).setValue('timeF');
newSheet.getRange(1, 3).setValue('meanT');
newSheet.getRange(1, 4).setValue('meanCO2');
newSheet.getRange(1, 5).setValue('meanPressure');
newSheet.getRange(1, 6).setValue('meanParticulate');
var row = 2;
// create the data table for the chart
var chartData = [['time', 'Temperature', 'CO2', 'Pressure', 'Particulate']];
for (var key in aggregateData) {
var time = aggregateData[key].time;
var meanTemp = aggregateData[key].meanTemp;
var meanCO2 = aggregateData[key].meanCO2;
var meanPressure = aggregateData[key].meanPressure;
var meanParticulate = aggregateData[key].meanParticulate;
chartData.push([time, meanTemp, meanCO2, meanPressure, meanParticulate]);
// write the aggregate data to the new sheet
newSheet.getRange(row, 1).setValue(aggregateData[key].date);
newSheet.getRange(row, 2).setValue(time);
newSheet.getRange(row, 3).setValue(meanTemp);
newSheet.getRange(row, 4).setValue(meanCO2);
newSheet.getRange(row, 5).setValue(meanPressure);
newSheet.getRange(row, 6).setValue(meanParticulate);
row++;
}
// create the chart and add it to the new sheet
var chart = newSheet.newChart()
.setChartType(Charts.ChartType.LINE)
.addRange(newSheet.getRange('A1:E' + row))
.setPosition(7, 1, 0, 0)
.setOption('title', 'Aggregate Data')
.build();
newSheet.insertChart(chart);
}