Copilot/GPT 4o example

updateWeeklyStddevSheet.js

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);
}