Created
February 19, 2025 20:07
-
-
Save phillipoertel/24b29845a555a2f2e1d434f4bc5c727f to your computer and use it in GitHub Desktop.
Copilot/GPT 4o example
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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