Skip to content

Instantly share code, notes, and snippets.

@phillipoertel
Created February 19, 2025 20:07
Show Gist options
  • Save phillipoertel/24b29845a555a2f2e1d434f4bc5c727f to your computer and use it in GitHub Desktop.
Save phillipoertel/24b29845a555a2f2e1d434f4bc5c727f to your computer and use it in GitHub Desktop.
Copilot/GPT 4o example
function updateWeeklyStddevSheet() {
const config = getConfig();
var sheet = getSheet('Weekly Stddev');
var weeklySheet = getSheet('Weekly'); // Changed to weekly sheet
var dataRange = weeklySheet.getDataRange(); // Changed to weekly sheet
var data = dataRange.getValues();
var weeklyData = {};
// Skip header row, process data by week
for (var i = 1; i < data.length; i++) {
var weekNumber = data[i][0];
var weekStart = data[i][1];
var weekKey = Utilities.formatDate(new Date(weekStart), Session.getScriptTimeZone(), "yyyy-MM-dd"); // Ensure date format
// Initialize week data if needed
if (!weeklyData[weekKey]) {
weeklyData[weekKey] = {
total: 0
};
}
// Sum up tickets for the week
for (var j = 3; j < data[i].length; j++) {
var count = Number(data[i][j]) || 0;
weeklyData[weekKey].total += count;
}
}
// Write weekly summary with standard deviation
sheet.clear();
// Set frozen rows and columns
sheet.setFrozenRows(1);
sheet.setFrozenColumns(2);
var weeks = Object.keys(weeklyData).sort((a, b) => new Date(b) - new Date(a)); // Sort weeks in descending order
// Set headers
sheet.getRange(1, 1, 1, 3).setValues([["Week Starting", "Stddev (Last 4 Weeks)", "Team Total"]]); // Updated headers
// Fill data
weeks.forEach((week, rowIndex) => {
var weekData = weeklyData[week];
var weekTotal = weekData.total;
sheet.getRange(rowIndex + 2, 1).setValue(week);
sheet.getRange(rowIndex + 2, 3).setValue(formatCellValue(weekTotal)); // Moved to third column
// Calculate standard deviation for the last 4 weeks
if (rowIndex < weeks.length - 3) {
var lastFourWeeks = weeks.slice(rowIndex, rowIndex + 4).map(w => weeklyData[w].total);
var stddev = Math.round(calculateStandardDeviation(lastFourWeeks));
sheet.getRange(rowIndex + 2, 2).setValue(formatCellValue(stddev)); // Moved to second column
}
});
sheet.autoResizeColumns(1, 3);
}
function calculateStandardDeviation(values) {
var mean = values.reduce((a, b) => a + b, 0) / values.length;
var variance = values.reduce((a, b) => a + Math.pow(b - mean, 2), 0) / values.length;
return Math.sqrt(variance);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment