Weekly Editor Assignment Summary for ${weekRangeStr}

/** ENHANCED SCRIPT WITH CHECKBOXES ON MAIN SHEET ONLY

Based on the proven original script, adds checkbox functionality to main sheet
Individual editor sheets remain in original format without checkboxes
1. Pulls data from the source spreadsheet to "Editor Assignments" sheet with checkbox columns
1. Creates individual editor sheets with their specific assignments (original format)
1. Weekly summary and reset functionality
UPDATED: Email now includes link to spreadsheet instead of attachment
UPDATED: Clean editor performance tracking that grows horizontally */

/**

Main function that updates data and then organizes by editor
Run this function to update everything in one step */ function updateAndOrganize() { // First update the Editor Assignments sheet from the source updateEditorAssignmentData();

// Then create/update individual editor sheets organizeByEditor();

Logger.log("Completed full update and organization process"); }

/**

Updates the Editor Assignments sheet with data from the source spreadsheet
Enhanced to include checkbox columns on main sheet only */ function updateEditorAssignmentData() { // 1) Source spreadsheet details var sourceSpreadsheetId = "1JkrY9OvWGd_7299LXueCPu25GZKdpwSsMQCD1aDwoHU"; var sourceSheetName = "WAG - Master"; var sourceRangeA1 = "A1:AA30"; // Only copy these rows/cols

// 2) Destination details (this spreadsheet) var destSheetName = "Editor Assignments";

// Save existing checkbox states before clearing var destSS = SpreadsheetApp.getActiveSpreadsheet(); var destSheet = destSS.getSheetByName(destSheetName); var savedCheckboxStates = {};

if (destSheet) { savedCheckboxStates = saveCheckboxStates(destSheet); }

// ------------------------------------------------------------- // OPEN SOURCE & GET THE SPECIFIC RANGE // ------------------------------------------------------------- var sourceSS = SpreadsheetApp.openById(sourceSpreadsheetId); var sourceSheet = sourceSS.getSheetByName(sourceSheetName);

// Range object for A1:AA30 var sourceRange = sourceSheet.getRange(sourceRangeA1);

// We'll take "displayed values" so formulas become text/dates var displayValues = sourceRange.getDisplayValues();

// Basic cell formatting - EXACT SAME AS ORIGINAL var backgrounds = sourceRange.getBackgrounds(); var fontColors = sourceRange.getFontColors(); var fontWeights = sourceRange.getFontWeights(); var fontStyles = sourceRange.getFontStyles(); var fontFamilies = sourceRange.getFontFamilies(); var numberFormats = sourceRange.getNumberFormats(); var hAligns = sourceRange.getHorizontalAlignments(); var vAligns = sourceRange.getVerticalAlignments();

// ------------------------------------------------------------- // OPEN DESTINATION, PREP SHEET // ------------------------------------------------------------- if (!destSheet) { // If no such sheet exists, create it destSheet = destSS.insertSheet(destSheetName); } else { // If it already exists, clear only content, leave all formatting including conditional formatting destSheet.getDataRange().clearContent(); }

// We'll ensure the sheet has at least 30 rows, 31 columns (expanded for checkboxes) if (destSheet.getMaxRows() < 30) { destSheet.insertRows(1, 30 - destSheet.getMaxRows()); } if (destSheet.getMaxColumns() < 31) { destSheet.insertColumns(1, 31 - destSheet.getMaxColumns()); }

// ------------------------------------------------------------- // EXPAND DATA AND FORMATTING FOR CHECKBOX COLUMNS // ------------------------------------------------------------- var expandedValues = expandArrayForCheckboxes(displayValues); var expandedBackgrounds = expandArrayForCheckboxes(backgrounds); var expandedFontColors = expandArrayForCheckboxes(fontColors); var expandedFontWeights = expandArrayForCheckboxes(fontWeights); var expandedFontStyles = expandArrayForCheckboxes(fontStyles); var expandedFontFamilies = expandArrayForCheckboxes(fontFamilies); var expandedNumberFormats = expandArrayForCheckboxes(numberFormats); var expandedHAligns = expandArrayForCheckboxes(hAligns); var expandedVAligns = expandArrayForCheckboxes(vAligns);

// This is where we paste everything in the destination var destRange = destSheet.getRange(1, 1, 30, 31);

// ------------------------------------------------------------- // 1) Copy Values // ------------------------------------------------------------- destRange.setValues(expandedValues);

// ------------------------------------------------------------- // 2) Copy Basic Formatting - EXACT SAME METHOD AS ORIGINAL // ------------------------------------------------------------- destRange.setBackgrounds(expandedBackgrounds); destRange.setFontColors(expandedFontColors); destRange.setFontWeights(expandedFontWeights); destRange.setFontStyles(expandedFontStyles); destRange.setFontFamilies(expandedFontFamilies); destRange.setNumberFormats(expandedNumberFormats); destRange.setHorizontalAlignments(expandedHAligns); destRange.setVerticalAlignments(expandedVAligns);

// Force wrap text on entire range destRange.setWrap(true);

// ------------------------------------------------------------- // 3) Copy Merged Cells - Adjusted for checkbox columns // ------------------------------------------------------------- try { // Monday (columns D-G, was C-F) destSheet.getRange(3, 4, 1, 4).merge();

// Tuesday (columns J-M, was H-K)  
destSheet.getRange(3, 10, 1, 4).merge();

// Wednesday (columns P-S, was M-P)
destSheet.getRange(3, 16, 1, 4).merge();

// Thursday (columns V-Y, was R-U)
destSheet.getRange(3, 22, 1, 4).merge();

// Friday (columns AB-AE, was W-Z)
destSheet.getRange(3, 28, 1, 4).merge();

Logger.log("Manually merged day header cells");

} catch (e) { Logger.log("Error applying merged cells: " + e.message); }

// ------------------------------------------------------------- // 4) Copy Row Heights & Column Widths - with checkbox adjustments // ------------------------------------------------------------- // For each row 1..30 for (var r = 1; r <= 30; r++) { var rowHeight = sourceSheet.getRowHeight(r); destSheet.setRowHeight(r, rowHeight); }

// Copy column widths with checkbox column adjustments copyColumnWidths(sourceSheet, destSheet);

// ------------------------------------------------------------- // 5) Add checkboxes to appropriate cells // ------------------------------------------------------------- addCheckboxesToSheet(destSheet, expandedValues, savedCheckboxStates);

// ------------------------------------------------------------- // 6) Write timestamp - EXACT SAME AS ORIGINAL // ------------------------------------------------------------- // Force the time to Eastern Time var now = new Date(); var estString = now.toLocaleString("en-US", { timeZone: "America/New_York" }); var estDate = new Date(estString);

// Convert 24-hour time to 12-hour with am/pm var hours = estDate.getHours(); var minutes = estDate.getMinutes(); var ampm = (hours >= 12) ? "pm" : "am";

if (hours === 0) { hours = 12; // midnight } else if (hours > 12) { hours -= 12; }

// Pad minutes var minutesStr = minutes.toString().padStart(2, "0");

// Final time string var timeStr = hours + ":" + minutesStr + ampm;

// Overwrite A1 with the friendly timestamp var stampCell = destSheet.getRange("A1"); stampCell.setValue("Last Updated: Today at " + timeStr + " EST"); stampCell .setFontWeight("bold") .setHorizontalAlignment("left");

Logger.log("Editor Assignment data updated successfully"); }

/**

Creates separate tabs for each editor with their assignments
ORIGINAL FORMAT - NO CHECKBOXES on individual sheets
Preserves all formatting from source sheet including rows 3 and 4 (headers)
Adds the date from cell A14 to each row with an editor assignment */ function organizeByEditor() { // Log the start of execution Logger.log("Starting to organize data by editor...");

// Access the active spreadsheet and the source sheet with editor assignments var ss = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = ss.getSheetByName("Editor Assignments");

// Check if the source sheet exists if (!sourceSheet) { Logger.log("Error: 'Editor Assignments' sheet not found"); return; }

// Get the ORIGINAL source data (not the expanded version with checkboxes) // We need to reconstruct the original data format var originalData = getOriginalDataFromExpandedSheet(sourceSheet);

// Use original formatting approach on the reconstructed data var sourceRange = sourceSheet.getDataRange(); var sourceFormats = sourceRange.getTextStyles(); var sourceBackgrounds = sourceRange.getBackgrounds(); var sourceFontColors = sourceRange.getFontColors(); var sourceWraps = sourceRange.getWraps(); var sourceHorizontalAlignments = sourceRange.getHorizontalAlignments(); var sourceVerticalAlignments = sourceRange.getVerticalAlignments();

// Get the date value specifically from cell A14 var dateValue = originalData[13][0]; // Index 13 is row 14 Logger.log("Date value from cell A14: " + dateValue);

// Define the headers (rows 1-4) to be included in each editor's sheet var headers = originalData.slice(0, 4); var headerFormats = compressFormatsForOriginal(sourceFormats.slice(0, 4)); var headerBackgrounds = compressFormatsForOriginal(sourceBackgrounds.slice(0, 4)); var headerFontColors = compressFormatsForOriginal(sourceFontColors.slice(0, 4)); var headerWraps = compressFormatsForOriginal(sourceWraps.slice(0, 4)); var headerHorizontalAlignments = compressFormatsForOriginal(sourceHorizontalAlignments.slice(0, 4)); var headerVerticalAlignments = compressFormatsForOriginal(sourceVerticalAlignments.slice(0, 4));

// Extract the column index for the editor names (column B is index 1) var editorColumnIndex = 1;

// Create a map to organize data by editor var editorData = {};

// Start from row 5 (after headers) and organize by editor for (var i = 4; i < originalData.length; i++) { var row = originalData[i]; var editor = row[editorColumnIndex];

// Skip empty editor cells or editors named "placeholder" or "editor"
if (!editor || editor === "editor" || editor === "placeholder") continue;

// Initialize array for new editors
if (!editorData[editor]) {
  editorData[editor] = {
    values: [],
    formats: [],
    backgrounds: [],
    fontColors: [],
    wraps: [],
    horizontalAlignments: [],
    verticalAlignments: []
  };
}

// Add the date to column A of each row if it's empty
if (dateValue && (!row[0] || row[0] === "")) {
  row[0] = dateValue;
}

// Add this row to the editor's data
editorData[editor].values.push(row);
editorData[editor].formats.push(compressFormatsForOriginal([sourceFormats[i]])[0]);
editorData[editor].backgrounds.push(compressFormatsForOriginal([sourceBackgrounds[i]])[0]);
editorData[editor].fontColors.push(compressFormatsForOriginal([sourceFontColors[i]])[0]);
editorData[editor].wraps.push(compressFormatsForOriginal([sourceWraps[i]])[0]);
editorData[editor].horizontalAlignments.push(compressFormatsForOriginal([sourceHorizontalAlignments[i]])[0]);
editorData[editor].verticalAlignments.push(compressFormatsForOriginal([sourceVerticalAlignments[i]])[0]);

}

// Debug: Log the unique editors found var editorsList = Object.keys(editorData).join(", "); Logger.log("Found these editors: " + editorsList); Logger.log("Excluded 'placeholder' and empty editor names");

// Create or update sheets for each editor for (var editor in editorData) { if (!editor) continue;

var sheetName = editor.toString().substring(0, 30);

var editorSheet = ss.getSheetByName(sheetName);
if (!editorSheet) {
  editorSheet = ss.insertSheet(sheetName);
} else {
  // Clear only content, leave conditional formatting alone
  editorSheet.getDataRange().clearContent();
}

// Combine headers with editor data
var combinedValues = headers.concat(editorData[editor].values);
var combinedFormats = headerFormats.concat(editorData[editor].formats);
var combinedBackgrounds = headerBackgrounds.concat(editorData[editor].backgrounds);
var combinedFontColors = headerFontColors.concat(editorData[editor].fontColors);
var combinedWraps = headerWraps.concat(editorData[editor].wraps);
var combinedHorizontalAlignments = headerHorizontalAlignments.concat(editorData[editor].horizontalAlignments);
var combinedVerticalAlignments = headerVerticalAlignments.concat(editorData[editor].verticalAlignments);

// Write data to the sheet
var targetRange = editorSheet.getRange(1, 1, combinedValues.length, combinedValues[0].length);
targetRange.setValues(combinedValues);

// Apply each type of formatting - EXACT SAME AS ORIGINAL
try {
  targetRange.setTextStyles(combinedFormats);
  Logger.log("Applied text styles to " + sheetName);
} catch (e) {
  Logger.log("Error applying text styles: " + e.message);
}

try {
  targetRange.setBackgrounds(combinedBackgrounds);
  Logger.log("Applied backgrounds to " + sheetName);
} catch (e) {
  Logger.log("Error applying backgrounds: " + e.message);
}

try {
  targetRange.setFontColors(combinedFontColors);
  Logger.log("Applied font colors to " + sheetName);
} catch (e) {
  Logger.log("Error applying font colors: " + e.message);
}

try {
  targetRange.setBorder(true, true, true, true, true, true, "black", SpreadsheetApp.BorderStyle.SOLID);
  Logger.log("Applied borders to " + sheetName);
} catch (e) {
  Logger.log("Error applying borders: " + e.message);
}

try {
  targetRange.setWraps(combinedWraps);
  Logger.log("Applied text wrapping to " + sheetName);
} catch (e) {
  Logger.log("Error applying wraps: " + e.message);
}

try {
  targetRange.setHorizontalAlignments(combinedHorizontalAlignments);
  Logger.log("Applied horizontal alignments to " + sheetName);
} catch (e) {
  Logger.log("Error applying horizontal alignments: " + e.message);
}

try {
  targetRange.setVerticalAlignments(combinedVerticalAlignments);
  Logger.log("Applied vertical alignments to " + sheetName);
} catch (e) {
  Logger.log("Error applying vertical alignments: " + e.message);
}

// Manually merge cells for days of the week - ORIGINAL POSITIONS
try {
  // Monday (columns C-F) - BACK TO ORIGINAL
  editorSheet.getRange(3, 3, 1, 4).merge();
  
  // Tuesday (columns H-K) - BACK TO ORIGINAL
  editorSheet.getRange(3, 8, 1, 4).merge();
  
  // Wednesday (columns M-P) - BACK TO ORIGINAL
  editorSheet.getRange(3, 13, 1, 4).merge();
  
  // Thursday (columns R-U) - BACK TO ORIGINAL
  editorSheet.getRange(3, 18, 1, 4).merge();
  
  // Friday (columns W-Z) - BACK TO ORIGINAL
  editorSheet.getRange(3, 23, 1, 4).merge();
  
  Logger.log("Manually merged day cells in row 3 for " + sheetName);
} catch (e) {
  Logger.log("Error merging day cells: " + e.message);
}

// Auto-resize columns to fit content
editorSheet.autoResizeColumns(1, combinedValues[0].length);

}

Logger.log("Successfully created sheets for each editor"); }

// Helper function to expand arrays for checkbox columns (main sheet only) function expandArrayForCheckboxes(originalArray) { var expandedArray = [];

for (var row = 0; row < originalArray.length; row++) { var originalRow = originalArray[row]; var expandedRow = [];

// Copy A, B as-is
expandedRow[0] = originalRow[0] || "";
expandedRow[1] = originalRow[1] || "";

// Insert checkbox column C, then copy C-F to D-G
expandedRow[2] = ""; // Checkbox column
expandedRow[3] = originalRow[2] || "";
expandedRow[4] = originalRow[3] || "";
expandedRow[5] = originalRow[4] || "";
expandedRow[6] = originalRow[5] || "";

// Copy G to H
expandedRow[7] = originalRow[6] || "";

// Insert checkbox column I, then copy H-K to J-M
expandedRow[8] = ""; // Checkbox column
expandedRow[9] = originalRow[7] || "";
expandedRow[10] = originalRow[8] || "";
expandedRow[11] = originalRow[9] || "";
expandedRow[12] = originalRow[10] || "";

// Copy L to N
expandedRow[13] = originalRow[11] || "";

// Insert checkbox column O, then copy M-P to P-S
expandedRow[14] = ""; // Checkbox column
expandedRow[15] = originalRow[12] || "";
expandedRow[16] = originalRow[13] || "";
expandedRow[17] = originalRow[14] || "";
expandedRow[18] = originalRow[15] || "";

// Copy Q to T
expandedRow[19] = originalRow[16] || "";

// Insert checkbox column U, then copy R-U to V-Y
expandedRow[20] = ""; // Checkbox column
expandedRow[21] = originalRow[17] || "";
expandedRow[22] = originalRow[18] || "";
expandedRow[23] = originalRow[19] || "";
expandedRow[24] = originalRow[20] || "";

// Copy V to Z
expandedRow[25] = originalRow[21] || "";

// Insert checkbox column AA, then copy W-Z to AB-AE
expandedRow[26] = ""; // Checkbox column
expandedRow[27] = originalRow[22] || "";
expandedRow[28] = originalRow[23] || "";
expandedRow[29] = originalRow[24] || "";
expandedRow[30] = originalRow[25] || "";

expandedArray.push(expandedRow);

}

return expandedArray; }

// Helper function to get original data format from expanded sheet function getOriginalDataFromExpandedSheet(expandedSheet) { var expandedData = expandedSheet.getDataRange().getValues(); var originalData = [];

for (var row = 0; row < expandedData.length; row++) { var expandedRow = expandedData[row]; var originalRow = [];

// Reverse the expansion mapping
originalRow[0] = expandedRow[0] || "";     // A stays A
originalRow[1] = expandedRow[1] || "";     // B stays B
originalRow[2] = expandedRow[3] || "";     // D -> C
originalRow[3] = expandedRow[4] || "";     // E -> D
originalRow[4] = expandedRow[5] || "";     // F -> E
originalRow[5] = expandedRow[6] || "";     // G -> F
originalRow[6] = expandedRow[7] || "";     // H -> G
originalRow[7] = expandedRow[9] || "";     // J -> H
originalRow[8] = expandedRow[10] || "";    // K -> I
originalRow[9] = expandedRow[11] || "";    // L -> J
originalRow[10] = expandedRow[12] || "";   // M -> K
originalRow[11] = expandedRow[13] || "";   // N -> L
originalRow[12] = expandedRow[15] || "";   // P -> M
originalRow[13] = expandedRow[16] || "";   // Q -> N
originalRow[14] = expandedRow[17] || "";   // R -> O
originalRow[15] = expandedRow[18] || "";   // S -> P
originalRow[16] = expandedRow[19] || "";   // T -> Q
originalRow[17] = expandedRow[21] || "";   // V -> R
originalRow[18] = expandedRow[22] || "";   // W -> S
originalRow[19] = expandedRow[23] || "";   // X -> T
originalRow[20] = expandedRow[24] || "";   // Y -> U
originalRow[21] = expandedRow[25] || "";   // Z -> V
originalRow[22] = expandedRow[27] || "";   // AB -> W
originalRow[23] = expandedRow[28] || "";   // AC -> X
originalRow[24] = expandedRow[29] || "";   // AD -> Y
originalRow[25] = expandedRow[30] || "";   // AE -> Z

originalData.push(originalRow);

}

return originalData; }

// Helper function to compress formatting arrays back to original format function compressFormatsForOriginal(expandedFormats) { var originalFormats = [];

for (var row = 0; row < expandedFormats.length; row++) { var expandedRow = expandedFormats[row]; var originalRow = [];

// Reverse the expansion mapping for formatting
originalRow[0] = expandedRow[0];      // A stays A
originalRow[1] = expandedRow[1];      // B stays B
originalRow[2] = expandedRow[3];      // D -> C
originalRow[3] = expandedRow[4];      // E -> D
originalRow[4] = expandedRow[5];      // F -> E
originalRow[5] = expandedRow[6];      // G -> F
originalRow[6] = expandedRow[7];      // H -> G
originalRow[7] = expandedRow[9];      // J -> H
originalRow[8] = expandedRow[10];     // K -> I
originalRow[9] = expandedRow[11];     // L -> J
originalRow[10] = expandedRow[12];    // M -> K
originalRow[11] = expandedRow[13];    // N -> L
originalRow[12] = expandedRow[15];    // P -> M
originalRow[13] = expandedRow[16];    // Q -> N
originalRow[14] = expandedRow[17];    // R -> O
originalRow[15] = expandedRow[18];    // S -> P
originalRow[16] = expandedRow[19];    // T -> Q
originalRow[17] = expandedRow[21];    // V -> R
originalRow[18] = expandedRow[22];    // W -> S
originalRow[19] = expandedRow[23];    // X -> T
originalRow[20] = expandedRow[24];    // Y -> U
originalRow[21] = expandedRow[25];    // Z -> V
originalRow[22] = expandedRow[27];    // AB -> W
originalRow[23] = expandedRow[28];    // AC -> X
originalRow[24] = expandedRow[29];    // AD -> Y
originalRow[25] = expandedRow[30];    // AE -> Z

originalFormats.push(originalRow);

}

return originalFormats; }

// Helper function to copy column widths with checkbox adjustments (main sheet only) function copyColumnWidths(sourceSheet, destSheet) { // Column mapping: original -> new position var columnMappings = [ {orig: 1, news: [1]}, // A -> A {orig: 2, news: [2]}, // B -> B {orig: 3, news: [4]}, // C -> D {orig: 4, news: [5]}, // D -> E {orig: 5, news: [6]}, // E -> F {orig: 6, news: [7]}, // F -> G {orig: 7, news: [8]}, // G -> H {orig: 8, news: [10]}, // H -> J {orig: 9, news: [11]}, // I -> K {orig: 10, news: [12]}, // J -> L {orig: 11, news: [13]}, // K -> M {orig: 12, news: [14]}, // L -> N {orig: 13, news: [16]}, // M -> P {orig: 14, news: [17]}, // N -> Q {orig: 15, news: [18]}, // O -> R {orig: 16, news: [19]}, // P -> S {orig: 17, news: [20]}, // Q -> T {orig: 18, news: [22]}, // R -> V {orig: 19, news: [23]}, // S -> W {orig: 20, news: [24]}, // T -> X {orig: 21, news: [25]}, // U -> Y {orig: 22, news: [26]}, // V -> Z {orig: 23, news: [28]}, // W -> AB {orig: 24, news: [29]}, // X -> AC {orig: 25, news: [30]}, // Y -> AD {orig: 26, news: [31]} // Z -> AE ];

// Copy widths for mapped columns for (var i = 0; i < columnMappings.length; i++) { var mapping = columnMappings[i]; try { var width = sourceSheet.getColumnWidth(mapping.orig); for (var j = 0; j < mapping.news.length; j++) { destSheet.setColumnWidth(mapping.news[j], width); } } catch (e) { // Set default if copy fails for (var j = 0; j < mapping.news.length; j++) { destSheet.setColumnWidth(mapping.news[j], 100); } } }

// Set checkbox columns to narrow width var checkboxCols = [3, 9, 15, 21, 27]; for (var i = 0; i < checkboxCols.length; i++) { destSheet.setColumnWidth(checkboxCols[i], 50); } }

// Helper function to add checkboxes to appropriate cells (main sheet only) function addCheckboxesToSheet(sheet, data, savedStates) { var checkboxCols = [3, 9, 15, 21, 27]; // C, I, O, U, AA var dataCols = [4, 10, 16, 22, 28]; // D, J, P, V, AB

for (var i = 0; i < checkboxCols.length; i++) { var checkboxCol = checkboxCols[i]; var dataCol = dataCols[i];

// Start from row 5 (after headers)
for (var row = 5; row <= data.length; row++) {
  var dataValue = data[row - 1] && data[row - 1][dataCol - 1];
  if (dataValue && dataValue !== "" && dataValue !== "client") {
    var cell = sheet.getRange(row, checkboxCol);
    cell.insertCheckboxes();
    cell.setBackground("#F0F8FF"); // Light blue background
    
    // Restore saved state if it exists
    var key = "R" + row + "C" + checkboxCol;
    if (savedStates[key] !== undefined) {
      cell.setValue(savedStates[key]);
    } else {
      cell.setValue(false);
    }
  }
}

} }

// Helper function to save checkbox states before clearing (main sheet only) function saveCheckboxStates(sheet) { var states = {}; var checkboxCols = [3, 9, 15, 21, 27];

try { for (var i = 0; i < checkboxCols.length; i++) { var col = checkboxCols[i]; var maxRows = Math.min(sheet.getMaxRows(), 50); // Limit to avoid timeouts

  for (var row = 5; row <= maxRows; row++) {
    try {
      var cell = sheet.getRange(row, col);
      if (cell.getDataValidation()) { // Has checkbox
        var value = cell.getValue();
        if (typeof value === "boolean") {
          var key = "R" + row + "C" + col;
          states[key] = value;
        }
      }
    } catch (e) {
      // Continue if individual cell fails
    }
  }
}

} catch (e) { Logger.log("Error saving checkbox states: " + e.message); }

return states; }

/**

Weekly Summary and Reset Functions */

/**

Main function to send weekly summary email and reset checkboxes
Now includes creating weekly data sheet and updating summary tab / /*
Main function to send weekly summary email and reset checkboxes
UPDATED: Now uses enhanced dashboard system instead of old Summary tab */ function sendWeeklySummaryAndReset() { try { // Get the date range for the week var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Editor Assignments"); var weekDate = sheet.getRange("A14").getValue();

// Create the detailed weekly data sheet first var weeklySheetName = createWeeklyDataSheet(sheet, weekDate);

// Send the enhanced summary email sendWeeklySummaryEmail();

// UPDATE ALL DASHBOARDS - Replace old Summary tab functionality updateAllDashboards();

// Then clear all checkboxes for fresh start clearAllCheckboxes();

Logger.log("Weekly summary sent, data sheet created, dashboards updated, and checkboxes cleared successfully"); } catch (e) { Logger.log("Error in weekly summary and reset: " + e.message); } }

/**

NEW: Update all dashboard tabs with current week data
Replaces the old updateSummaryTab() function */ function updateAllDashboards() { try { Logger.log("🚀 Starting dashboard update process...");

// Step 1: Append current week data to Raw Data sheet appendWeekToRawData(); Logger.log("✅ Raw data updated");

// Step 2: Update Historical Trends Dashboard updateHistoricalTrendsDashboard(); Logger.log("✅ Historical Trends Dashboard updated");

// Step 3: Update Editor Analytics Dashboard updateEditorAnalyticsDashboard(); Logger.log("✅ Editor Analytics Dashboard updated");

Logger.log("🎉 All dashboards updated successfully!");

} catch (error) { Logger.log("❌ Dashboard update error: " + error.message); throw new Error("Dashboard update failed: " + error.message); } }

/**

Creates a detailed weekly data sheet with completed and incomplete assignments
Now creates the sheet in the separate Editor Assignment History spreadsheet */ function createWeeklyDataSheet(sheet, weekDate) { const historySpreadsheetId = "1qwTH-YhxnYNW6P6pKaYswCXAE3l3d2bJm4w-nhKeTQw"; const historySS = SpreadsheetApp.openById(historySpreadsheetId); const weekRange = getWeekDateRange(weekDate); const sheetName = weekRange.displayRange; let existingSheet = historySS.getSheetByName(sheetName); if (existingSheet) historySS.deleteSheet(existingSheet); const weeklySheet = historySS.insertSheet(sheetName); const summarySheet = historySS.getSheetByName("Summary"); if (summarySheet) { historySS.setActiveSheet(summarySheet); historySS.moveActiveSheet(1); historySS.setActiveSheet(weeklySheet); historySS.moveActiveSheet(2); }

const analysis = getDetailedAssignmentAnalysis(sheet); const headers = [ "Done", "Editor", "Due Day", "Status", "Client", "Footage ID", "Notes to Editor", "Additional Notes" ]; weeklySheet.getRange(1, 1, 1, headers.length).setValues([headers]); weeklySheet.getRange(1, 1, 1, headers.length).setFontWeight("bold").setBackground("#E6E6FA");

const sortedAssignments = analysis.allAssignments.sort((a, b) => { if (a.editor !== b.editor) return a.editor.localeCompare(b.editor); if (a.client !== b.client) return a.client.localeCompare(b.client); return a.dayOrder - b.dayOrder; });

const dataRows = sortedAssignments.map(assignment => [ "", assignment.editor, assignment.day, assignment.status, assignment.client || "N/A", assignment.assignment || "N/A", assignment.notesToEditor || "None", assignment.additionalNotes || "None" ]);

if (dataRows.length > 0) { const range = weeklySheet.getRange(2, 1, dataRows.length, headers.length); range.setValues(dataRows); for (let i = 0; i < dataRows.length; i++) { const status = dataRows[i][3]; const rowRange = weeklySheet.getRange(i + 2, 1, 1, headers.length); const checkboxCell = weeklySheet.getRange(i + 2, 1); if (status === "Completed") { rowRange.setBackground("#E8F5E8"); } else { rowRange.setBackground("#FFE8E8"); checkboxCell.insertCheckboxes(); checkboxCell.setValue(false); } } }

weeklySheet.autoResizeColumns(1, headers.length); weeklySheet.insertRows(1, 3); weeklySheet.getRange(1, 1).setValue("Week of " + weekRange.displayRange) .setFontWeight("bold").setFontSize(14); weeklySheet.getRange(2, 1).setValue("Total Assignments: " + analysis.totalCount); weeklySheet.getRange(2, 3).setValue("Completed: " + analysis.completedCount); weeklySheet.getRange(2, 5).setValue("Incomplete: " + analysis.incompleteCount); weeklySheet.setColumnWidth(1, 80);

const rules = [ SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied("=$A2=TRUE") .setBackground("#E8F5E8") .setRanges([weeklySheet.getRange(2, 1, dataRows.length, headers.length)]) .build() ]; weeklySheet.setConditionalFormatRules(rules);

return sheetName; }

/**

Gets detailed assignment analysis with all assignment information
FIXED: Correct data column mapping based on actual sheet structure */ function getDetailedAssignmentAnalysis(sheet) { var checkboxCols = [3, 9, 15, 21, 27]; // C, I, O, U, AA var clientCols = [3, 9, 15, 21, 27]; // D, J, P, V, AB (client names) var footageIdCols = [4, 10, 16, 22, 28]; // E, K, Q, W, AC (footage IDs) var detailsCols = [5, 11, 17, 23, 29]; // F, L, R, X, AD (assignment details) var additionalDetailsCols = [6, 12, 18, 24, 30]; // G, M, S, Y, AE (additional notes) var dayNames = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];

var analysis = { allAssignments: [], totalCount: 0, completedCount: 0, incompleteCount: 0 };

var data = sheet.getDataRange().getValues();

for (var day = 0; day < checkboxCols.length; day++) { var checkboxCol = checkboxCols[day]; var clientCol = clientCols[day]; var footageIdCol = footageIdCols[day]; var detailsCol = detailsCols[day]; var additionalDetailsCol = additionalDetailsCols[day]; var dayName = dayNames[day];

for (var row = 5; row <= data.length; row++) {
  var client = data[row - 1] && data[row - 1][clientCol];
  var footageId = data[row - 1] && data[row - 1][footageIdCol];
  var assignmentDetails = data[row - 1] && data[row - 1][detailsCol];
  var additionalDetails = data[row - 1] && data[row - 1][additionalDetailsCol];
  var editor = data[row - 1] && data[row - 1][1]; // Column B

  // Use footage ID as the primary indicator of an assignment
  if (footageId && footageId !== "" && editor && editor !== "" && editor !== "editor" && editor !== "placeholder") {
    var isCompleted = false;
    try {
      var cell = sheet.getRange(row, checkboxCol);
      if (cell.getDataValidation()) {
        var value = cell.getValue();
        if (typeof value === "boolean") {
          isCompleted = value;
        }
      }
    } catch (e) {}

    // Ensure proper fallbacks
    var displayClient = client || "Unknown Client";
    var displayFootageId = footageId || "N/A";
    var displayDetails = assignmentDetails || "";
    var displayAdditionalDetails = additionalDetails || "";

    var assignmentRecord = {
      editor: editor,
      client: displayClient,
      assignment: displayFootageId,  // This is the footage ID
      day: dayName,
      dayOrder: day,
      status: isCompleted ? "Completed" : "Incomplete",
      details: [displayDetails, displayAdditionalDetails].filter(Boolean).join(" | "),
      isCompleted: isCompleted,
      notesToEditor: displayDetails,
      additionalNotes: displayAdditionalDetails
    };

    analysis.allAssignments.push(assignmentRecord);
    analysis.totalCount++;
    if (isCompleted) {
      analysis.completedCount++;
    } else {
      analysis.incompleteCount++;
    }
  }
}

} return analysis; }

function getWeekDateRange(mondayDate) { Logger.log("🔍 DEBUG - Input mondayDate: " + mondayDate);

var monday = new Date(mondayDate); Logger.log("🔍 DEBUG - Monday calculated: " + monday);

var friday = new Date(monday); friday.setDate(monday.getDate() + 4); // Monday + 4 = Friday Logger.log("🔍 DEBUG - Friday calculated: " + friday);

var mondayStr = Utilities.formatDate(monday, "America/New_York", "MMM dd"); var fridayStr = Utilities.formatDate(friday, "America/New_York", "MMM dd, yyyy");

var displayRange = mondayStr + " - " + fridayStr; Logger.log("🔍 DEBUG - Final displayRange: " + displayRange);

return { displayRange: displayRange, monday: monday, friday: friday }; }

/**

Analyzes checkbox states and sends HTML formatted summary email
UPDATED: Now includes link to Editor Assignment History spreadsheet instead of attachment
UPDATED: Email subject and header use "Weekly Editor Assignment Summary for week range" */ function sendWeeklySummaryEmail() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Editor Assignments");

if (!sheet) { Logger.log("Error: 'Editor Assignments' sheet not found"); return; }

// Get the date from A14 and calculate week range var weekDate = sheet.getRange("A14").getValue(); var weekRange = getWeekDateRange(weekDate); var weekRangeStr = weekRange.displayRange; // e.g., "Jun 09 - Jun 13, 2025"

// Analyze checkboxes and assignments var analysis = analyzeWeeklyProgress(sheet);

// Build the email content with spreadsheet link var emailSubject = "Weekly Editor Assignment Summary for " + weekRangeStr; var historySpreadsheetId = "1qwTH-YhxnYNW6P6pKaYswCXAE3l3d2bJm4w-nhKeTQw"; var historySpreadsheetUrl = "https://docs.google.com/spreadsheets/d/" + historySpreadsheetId + "/edit"; var emailBody = buildSummaryEmailBodyHTML(analysis, weekRangeStr, historySpreadsheetUrl);

// Send the HTML email with link (no attachment) try { GmailApp.sendEmail( "executiveteam@mydashmedia.com", emailSubject, "", // Plain text body (empty) { htmlBody: emailBody // No attachments anymore! } ); Logger.log("Weekly summary email sent successfully to shawna@mydashmedia.com with Google Sheets link"); } catch (e) { Logger.log("Error sending email: " + e.message); throw e; } }

/**

Analyzes all checkboxes and creates summary data with enhanced details
FIXED: Correct data column mapping based on actual sheet structure */ function analyzeWeeklyProgress(sheet) { var checkboxCols = [3, 9, 15, 21, 27]; // C, I, O, U, AA var clientCols = [3, 9, 15, 21, 27]; // D, J, P, V, AB (client names) var footageIdCols = [4, 10, 16, 22, 28]; // E, K, Q, W, AC (footage IDs) var detailsCols = [5, 11, 17, 23, 29]; // F, L, R, X, AD (assignment details) var dayNames = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];

var analysis = { totalAssignments: 0, completedAssignments: 0, byDay: {}, byEditor: {}, incompleteDetails: [] };

// Initialize day analysis for (var d = 0; d < dayNames.length; d++) { analysis.byDay[dayNames[d]] = { total: 0, completed: 0, incomplete: [] }; }

// Get all data to find editors and assignments var data = sheet.getDataRange().getValues();

// Analyze each day's checkboxes for (var day = 0; day < checkboxCols.length; day++) { var checkboxCol = checkboxCols[day]; var clientCol = clientCols[day]; var footageIdCol = footageIdCols[day]; var detailsCol = detailsCols[day]; var dayName = dayNames[day];

// Check each row starting from row 5 (after headers)
for (var row = 5; row <= data.length; row++) {
  var client = data[row - 1] && data[row - 1][clientCol];
  var footageId = data[row - 1] && data[row - 1][footageIdCol];
  var assignmentDetails = data[row - 1] && data[row - 1][detailsCol];
  var editor = data[row - 1] && data[row - 1][1]; // Column B
  
  // Use footage ID as the primary indicator of an assignment
  if (footageId && footageId !== "" && footageId !== "client" && editor && editor !== "" && editor !== "editor" && editor !== "placeholder") {
    analysis.totalAssignments++;
    analysis.byDay[dayName].total++;
    
    // Initialize editor if not exists
    if (!analysis.byEditor[editor]) {
      analysis.byEditor[editor] = {
        total: 0,
        completed: 0,
        incomplete: []
      };
    }
    analysis.byEditor[editor].total++;
    
    // Ensure we have proper fallbacks for display
    var displayClient = client || "Unknown Client";
    var displayFootageId = footageId || "N/A";
    var displayDetails = assignmentDetails || "N/A";
    
    // Check if checkbox is checked
    try {
      var cell = sheet.getRange(row, checkboxCol);
      var isChecked = false;
      
      if (cell.getDataValidation()) { // Has checkbox
        var value = cell.getValue();
        if (typeof value === "boolean") {
          isChecked = value;
        }
      }
      
      if (isChecked) {
        analysis.completedAssignments++;
        analysis.byDay[dayName].completed++;
        analysis.byEditor[editor].completed++;
      } else {
        // Track incomplete assignment with correct field mapping
        var incompleteItem = {
          day: dayName,
          editor: editor,
          client: displayClient,
          assignment: displayFootageId,  // This is the footage ID
          details: displayDetails
        };
        analysis.byDay[dayName].incomplete.push(incompleteItem);
        analysis.byEditor[editor].incomplete.push(incompleteItem);
        analysis.incompleteDetails.push(incompleteItem);
      }
      
    } catch (e) {
      // If can't read checkbox, assume incomplete
      var incompleteItem = {
        day: dayName,
        editor: editor,
        client: displayClient,
        assignment: displayFootageId,  // This is the footage ID
        details: displayDetails
      };
      analysis.byDay[dayName].incomplete.push(incompleteItem);
      analysis.byEditor[editor].incomplete.push(incompleteItem);
      analysis.incompleteDetails.push(incompleteItem);
    }
  }
}

}

return analysis; }

/**

FIXED: Email with spreadsheet link properly included, no problematic emojis */ function buildSummaryEmailBodyHTML(analysis, weekRangeStr, historySpreadsheetUrl) { var html = ` <style> body { font-family: Arial, sans-serif; line-height: 1.6; color: #333; } .header { background-color: #4A90E2; color: white; padding: 20px; text-align: center; border-radius: 8px 8px 0 0; } .content { background-color: #f9f9f9; padding: 20px; border-radius: 0 0 8px 8px; } .summary-box { background-color: white; padding: 15px; margin: 10px 0; border-left: 4px solid #4A90E2; border-radius: 4px; } .completion-rate { font-size: 24px; font-weight: bold; color: #4A90E2; } .stats-grid { display: flex; justify-content: space-around; margin: 20px 0; } .stat-item { text-align: center; background: white; padding: 15px; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); } .breakdown-section { margin: 20px 0; } .breakdown-title { font-size: 18px; font-weight: bold; color: #333; margin-bottom: 10px; border-bottom: 2px solid #4A90E2; padding-bottom: 5px; } .assignment-list { background: white; padding: 15px; border-radius: 8px; margin: 10px 0; } .assignment-table { width: 100%; border-collapse: collapse; margin: 10px 0; background: white; border-radius: 8px; overflow: hidden; box-shadow: 0 2px 4px rgba(0,0,0,0.1); } .assignment-table th { background: #34495e; color: white; padding: 12px; text-align: left; font-weight: bold; } .assignment-table td { padding: 10px 12px; border-bottom: 1px solid #ecf0f1; } .assignment-table tr:nth-child(even) { background: #f8f9fa; } .assignment-table tr:hover { background: #e8f4fd; } .day-header { background: #2c3e50; color: white; font-weight: bold; text-align: center; } .editor-name { font-weight: bold; color: #8e44ad; } .client-name { color: #2c3e50; font-weight: 500; } .assignment-details { font-size: 14px; color: #666; margin-top: 5px; } .celebration { text-align: center; font-size: 24px; color: #27ae60; margin: 20px 0; } .spreadsheet-link { display: inline-block; background: #4A90E2; color: white; padding: 12px 24px; text-decoration: none; border-radius: 6px; font-weight: bold; margin: 10px 0; } .spreadsheet-link:hover { background: #357ABD; } .info-box { background: #e3f2fd; padding: 15px; border-radius: 8px; margin: 20px 0; border-left: 4px solid #2196f3; } .footer { text-align: center; margin-top: 30px; padding: 20px; background: #ecf0f1; border-radius: 8px; color: #7f8c8d; } </style>

Weekly Editor Assignment Summary for ${weekRangeStr}

`;

// FIXED: Spreadsheet link at top with proper formatting html += <div class="summary-box" style="text-align: center;"> <p><strong>Access the Complete Editor Assignment History:</strong></p> <p><a href="${historySpreadsheetUrl}" class="spreadsheet-link">Open Editor Assignment History Spreadsheet</a></p> <p style="font-size: 12px; color: #666;">A new weekly data sheet has been added with your assignment details.</p> </div>;

// Overall summary var completionRate = analysis.totalAssignments > 0 ? Math.round((analysis.completedAssignments / analysis.totalAssignments) * 100) : 0;

html += <div class="summary-box"> <div class="stats-grid"> <div class="stat-item"> <div style="font-size: 14px; color: #666;">Total Assignments</div> <div style="font-size: 28px; font-weight: bold; color: #2c3e50;">${analysis.totalAssignments}</div> </div> <div class="stat-item"> <div style="font-size: 14px; color: #666;">Completed</div> <div style="font-size: 28px; font-weight: bold; color: #27ae60;">${analysis.completedAssignments}</div> </div> <div class="stat-item"> <div style="font-size: 14px; color: #666;">Incomplete</div> <div style="font-size: 28px; font-weight: bold; color: #e74c3c;">${analysis.totalAssignments - analysis.completedAssignments}</div> </div> <div class="stat-item"> <div style="font-size: 14px; color: #666;">Completion Rate</div> <div class="completion-rate">${completionRate}%</div> </div> </div> </div>;

// By day breakdown html += <div class="breakdown-section"> <div class="breakdown-title">Daily Breakdown</div> <div class="assignment-list">;

for (var day in analysis.byDay) { var dayData = analysis.byDay[day]; if (dayData.total > 0) { var dayRate = Math.round((dayData.completed / dayData.total) * 100); var color = dayRate >= 90 ? '#27ae60' : dayRate >= 70 ? '#f39c12' : '#e74c3c'; html += <div style="display: flex; justify-content: space-between; align-items: center; padding: 8px 0; border-bottom: 1px solid #ecf0f1;"> <span style="font-weight: bold;">${day}</span> <span>${dayData.completed}/${dayData.total} <span style="color: ${color}; font-weight: bold;">(${dayRate}%)</span></span> </div>; } } html += </div></div>;

// By editor breakdown html += <div class="breakdown-section"> <div class="breakdown-title">Editor Breakdown</div> <div class="assignment-list">;

for (var editor in analysis.byEditor) { var editorData = analysis.byEditor[editor]; if (editorData.total > 0) { var editorRate = Math.round((editorData.completed / editorData.total) * 100); var color = editorRate >= 90 ? '#27ae60' : editorRate >= 70 ? '#f39c12' : '#e74c3c'; html += <div style="display: flex; justify-content: space-between; align-items: center; padding: 8px 0; border-bottom: 1px solid #ecf0f1;"> <span style="font-weight: bold;">${editor}</span> <span>${editorData.completed}/${editorData.total} <span style="color: ${color}; font-weight: bold;">(${editorRate}%)</span></span> </div>; } } html += </div></div>;

// Info section and incomplete assignments if (analysis.incompleteDetails.length > 0) { html += <div class="info-box"> <p><strong>How to Update Incomplete Assignments:</strong></p> <p>In the Editor Assignment History spreadsheet, check the "Done" boxes for completed assignments.</p> </div>;

html += `
      <div class="breakdown-section">
        <div class="breakdown-title">Incomplete Assignments</div>`;

// Group by editor for better organization
var editorIncomplete = {};
for (var k = 0; k < analysis.incompleteDetails.length; k++) {
  var item = analysis.incompleteDetails[k];
  if (!editorIncomplete[item.editor]) {
    editorIncomplete[item.editor] = [];
  }
  editorIncomplete[item.editor].push(item);
}

// Sort editors alphabetically
var sortedEditors = Object.keys(editorIncomplete).sort();

for (var e = 0; e < sortedEditors.length; e++) {
  var editor = sortedEditors[e];
  var editorAssignments = editorIncomplete[editor];
  
  // Sort assignments within each editor by day
  var dayOrder = {"Monday": 1, "Tuesday": 2, "Wednesday": 3, "Thursday": 4, "Friday": 5};
  editorAssignments.sort(function(a, b) {
    return dayOrder[a.day] - dayOrder[b.day];
  });
  
  html += `
      <div style="margin: 20px 0;">
        <h3 style="background: #8e44ad; color: white; padding: 10px; margin: 0; border-radius: 4px 4px 0 0;">${editor} (${editorAssignments.length} incomplete)</h3>
        <table class="assignment-table" style="margin-top: 0;">
          <thead>
            <tr>
              <th>Day</th>
              <th>Client</th>
              <th>Footage ID</th>
              <th>Assignment Details</th>
            </tr>
          </thead>
          <tbody>`;
  
  for (var j = 0; j < editorAssignments.length; j++) {
    var item = editorAssignments[j];
    
    // Ensure all values have fallbacks to prevent column shifting
    var day = item.day || 'N/A';
    var client = item.client || 'N/A';
    var footageId = item.assignment || 'N/A';
    var assignmentDetails = item.details || 'N/A';
    
    // Clean assignment details without redundant footage ID
    if (assignmentDetails && assignmentDetails !== 'N/A' && footageId && footageId !== 'N/A') {
      if (assignmentDetails.indexOf(footageId) === 0) {
        // Remove the footage ID and any following " | " 
        assignmentDetails = assignmentDetails.replace(footageId, "").replace(/^\s*\|\s*/, "");
      }
    }
    
    // Final check - if cleaned details is empty, set to N/A
    if (!assignmentDetails || assignmentDetails.trim() === '') {
      assignmentDetails = 'N/A';
    }
    
    html += `
            <tr>
              <td><strong>${day}</strong></td>
              <td class="client-name">${client}</td>
              <td>${footageId}</td>
              <td>${assignmentDetails}</td>
            </tr>`;
  }
  
  html += `
          </tbody>
        </table>
      </div>`;
}

html += `</div>`;

} else { html += <div class="celebration"> ALL ASSIGNMENTS COMPLETED! </div>; }

html += <div class="footer"> <p>This summary was automatically generated from your Editor Assignment tracker.</p> </div> </div> </body> </html>;

return html; }

/**

FIXED: Only changes Success Rate column (column 2) to match Historical Dashboard
Does NOT touch Letter Grade or any other formatting */ function applyHistoricalSuccessRateFormatting(sheet, startRow, numRows) { Logger.log("Applying Historical Dashboard success rate formatting to Editor Dashboard...");

// ONLY FORMAT SUCCESS RATE COLUMN (Column 2) - Same as Historical Dashboard for (var i = 0; i < numRows; i++) { var cell = sheet.getRange(startRow + i, 2); // Success rate column ONLY var value = cell.getValue(); var rate = parseFloat(String(value).replace('%', ''));

if (rate >= 90) {
  cell.setBackground("#4caf50").setFontColor("white").setFontWeight("bold");
} else if (rate >= 80) {
  cell.setBackground("#8bc34a").setFontColor("white");
} else if (rate >= 70) {
  cell.setBackground("#ffeb3b").setFontColor("#333333");
} else {
  cell.setBackground("#f44336").setFontColor("white").setFontWeight("bold");
}

}

Logger.log("Success rate formatting applied successfully!"); }

/**

Clears all checkboxes to start fresh for next week */ function clearAllCheckboxes() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Editor Assignments");

if (!sheet) { Logger.log("Error: 'Editor Assignments' sheet not found"); return; }

var checkboxCols = [3, 9, 15, 21, 27]; // C, I, O, U, AA var clearedCount = 0;

for (var i = 0; i < checkboxCols.length; i++) { var col = checkboxCols[i]; var maxRows = Math.min(sheet.getMaxRows(), 50);

for (var row = 5; row <= maxRows; row++) {
  try {
    var cell = sheet.getRange(row, col);
    if (cell.getDataValidation()) { // Has checkbox
      cell.setValue(false);
      clearedCount++;
    }
  } catch (e) {
    // Continue if individual cell fails
  }
}

}

Logger.log("Cleared " + clearedCount + " checkboxes for fresh start"); }

/**

WORKING conditional formatting for fresh start */ function addWorkingConditionalFormatting(sheet, numDataRows) { try { var dataStartRow = 5; // Data starts at row 5

// Clear any existing conditional formatting first sheet.clearConditionalFormatRules();

// Create conditional formatting rule for the entire data range var dataRange = sheet.getRange(dataStartRow, 1, numDataRows, 7);

// Rule: when checkbox in column A is TRUE, make entire row green var rule = SpreadsheetApp.newConditionalFormatRule() .whenFormulaSatisfied('=$A5=TRUE') // When checkbox in column A is checked .setBackground('#E8F5E8') // Light green background .setRanges([dataRange]) .build();

// Apply the rule sheet.setConditionalFormatRules([rule]);

Logger.log("Added working conditional formatting rule for Done checkboxes"); } catch (e) { Logger.log("Error adding conditional formatting: " + e.message); } }

/**

Creates a custom menu when the spreadsheet opens
ONLY: Progress check function */ function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Editor Assignment Tools') .addItem('📊 Check Week Progress So Far', 'checkWeekProgressSoFar') .addToUi(); }

/**

NEW FUNCTION: Mid-week progress check
UNIVERSAL VERSION: Can be run from any spreadsheet to check VER progress
Analyzes completed vs incomplete assignments for the current week up to yesterday
Helps with rescheduling decisions by showing where you stand so far this week */ function checkWeekProgressSoFar() { // VER SPREADSHEET ID - Ready to use! var VER_SPREADSHEET_ID = "19Ugu051TrC7G-CxmG_LFk07buqWVqmabDrEFcd9eOkM";

try { // Open the VER spreadsheet (works from any spreadsheet) var ss = SpreadsheetApp.openById(VER_SPREADSHEET_ID); var sheet = ss.getSheetByName("Editor Assignments");

if (!sheet) {
  Logger.log("Error: 'Editor Assignments' sheet not found in VER spreadsheet");
  SpreadsheetApp.getUi().alert("Error", "'Editor Assignments' sheet not found in VER spreadsheet", SpreadsheetApp.getUi().ButtonSet.OK);
  return;
}

} catch (e) { Logger.log("Error accessing VER spreadsheet: " + e.message); SpreadsheetApp.getUi().alert("Error", "Could not access VER spreadsheet. Check the spreadsheet ID and permissions.\n\nError: " + e.message, SpreadsheetApp.getUi().ButtonSet.OK); return; }

// Rest of the function stays the same... // Determine which days to analyze based on today var today = new Date(); var dayOfWeek = today.getDay(); // 0=Sunday, 1=Monday, etc. var daysToAnalyze = []; var dayNames = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];

// Determine which days of this week to include (up to yesterday) if (dayOfWeek === 1) { // Monday SpreadsheetApp.getUi().alert( "Too Early to Check!", "It's Monday! No previous workdays this week to analyze yet.\n\nRun this function Tuesday or later to see your week's progress.", SpreadsheetApp.getUi().ButtonSet.OK ); return; } else if (dayOfWeek === 2) { // Tuesday daysToAnalyze = ["Monday"]; } else if (dayOfWeek === 3) { // Wednesday
daysToAnalyze = ["Monday", "Tuesday"]; } else if (dayOfWeek === 4) { // Thursday daysToAnalyze = ["Monday", "Tuesday", "Wednesday"]; } else if (dayOfWeek === 5) { // Friday daysToAnalyze = ["Monday", "Tuesday", "Wednesday", "Thursday"]; } else { // Weekend (Saturday/Sunday) daysToAnalyze = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"]; }

// Get current week date range for display var weekDate = sheet.getRange("A14").getValue(); var weekRange = getWeekDateRange(weekDate);

// Analyze assignments for specified days only var progressAnalysis = analyzeProgressForSpecificDays(sheet, daysToAnalyze);

// Create detailed report var reportTitle = "📊 VER Week Progress Check - " + weekRange.displayRange; var todayName = ["Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"][dayOfWeek]; var daysAnalyzedText = daysToAnalyze.join(", ");

var completionRate = progressAnalysis.totalAssignments > 0 ? Math.round((progressAnalysis.completedAssignments / progressAnalysis.totalAssignments) * 100) : 0;

// Build detailed message var message = "Running check on " + todayName + "\n"; message += "Days analyzed: " + daysAnalyzedText + "\n\n"; message += "📈 OVERALL PROGRESS SO FAR:\n"; message += "• Total Assignments: " + progressAnalysis.totalAssignments + "\n"; message += "• Completed: " + progressAnalysis.completedAssignments + "\n"; message += "• Incomplete: " + progressAnalysis.incompleteAssignments + "\n"; message += "• Completion Rate: " + completionRate + "%\n\n";

// Daily breakdown message += "📅 DAILY BREAKDOWN:\n"; for (var i = 0; i < daysToAnalyze.length; i++) { var day = daysToAnalyze[i]; var dayData = progressAnalysis.byDay[day]; if (dayData && dayData.total > 0) { var dayRate = Math.round((dayData.completed / dayData.total) * 100); var status = dayRate >= 90 ? "✅" : dayRate >= 70 ? "⚠️" : "❌"; message += "• " + day + ": " + dayData.completed + "/" + dayData.total + " (" + dayRate + "%) " + status + "\n"; } }

// Editor breakdown message += "\n👥 EDITOR BREAKDOWN:\n"; var sortedEditors = Object.keys(progressAnalysis.byEditor).sort(); for (var i = 0; i < sortedEditors.length; i++) { var editor = sortedEditors[i]; var editorData = progressAnalysis.byEditor[editor]; if (editorData.total > 0) { var editorRate = Math.round((editorData.completed / editorData.total) * 100); var status = editorRate >= 90 ? "✅" : editorRate >= 70 ? "⚠️" : "❌"; message += "• " + editor + ": " + editorData.completed + "/" + editorData.total + " (" + editorRate + "%) " + status + "\n"; } }

// Recommendations message += "\n💡 RECOMMENDATIONS:\n"; if (completionRate >= 90) { message += "🎉 Excellent progress! You're on track for a great week!"; } else if (completionRate >= 70) { message += "⚠️ Good progress but room for improvement. Consider prioritizing incomplete assignments."; } else { message += "🚨 Behind schedule! Consider rescheduling some assignments to balance the workload."; }

// Show the analysis in a popup SpreadsheetApp.getUi().alert( reportTitle, message, SpreadsheetApp.getUi().ButtonSet.OK );

// Also log detailed incomplete assignments for planning if (progressAnalysis.incompleteDetails.length > 0) { Logger.log("=== INCOMPLETE ASSIGNMENTS FOR RESCHEDULING ==="); for (var i = 0; i < progressAnalysis.incompleteDetails.length; i++) { var item = progressAnalysis.incompleteDetails[i]; Logger.log(item.day + " - " + item.editor + " - " + item.client + " - " + item.assignment); } }

Logger.log("VER week progress check completed for " + daysAnalyzedText); }

/**

Helper function to analyze progress for specific days only */ function analyzeProgressForSpecificDays(sheet, daysToInclude) { var checkboxCols = [3, 9, 15, 21, 27]; // C, I, O, U, AA var clientCols = [3, 9, 15, 21, 27]; // D, J, P, V, AB (client names) var footageIdCols = [4, 10, 16, 22, 28]; // E, K, Q, W, AC (footage IDs) var detailsCols = [5, 11, 17, 23, 29]; // F, L, R, X, AD (assignment details) var dayNames = ["Monday", "Tuesday", "Wednesday", "Thursday", "Friday"];

var analysis = { totalAssignments: 0, completedAssignments: 0, incompleteAssignments: 0, byDay: {}, byEditor: {}, incompleteDetails: [] };

// Initialize day analysis for specified days only for (var d = 0; d < daysToInclude.length; d++) { analysis.byDay[daysToInclude[d]] = { total: 0, completed: 0, incomplete: [] }; }

// Get all data to find editors and assignments var data = sheet.getDataRange().getValues();

// Analyze only the specified days for (var day = 0; day < dayNames.length; day++) { var dayName = dayNames[day];

// Skip days not in our analysis list
if (daysToInclude.indexOf(dayName) === -1) {
  continue;
}

var checkboxCol = checkboxCols[day];
var clientCol = clientCols[day];
var footageIdCol = footageIdCols[day];
var detailsCol = detailsCols[day];

// Check each row starting from row 5 (after headers)
for (var row = 5; row <= data.length; row++) {
  var client = data[row - 1] && data[row - 1][clientCol];
  var footageId = data[row - 1] && data[row - 1][footageIdCol];
  var assignmentDetails = data[row - 1] && data[row - 1][detailsCol];
  var editor = data[row - 1] && data[row - 1][1]; // Column B
  
  // Use footage ID as the primary indicator of an assignment
  if (footageId && footageId !== "" && footageId !== "client" && editor && editor !== "" && editor !== "editor" && editor !== "placeholder") {
    analysis.totalAssignments++;
    analysis.byDay[dayName].total++;
    
    // Initialize editor if not exists
    if (!analysis.byEditor[editor]) {
      analysis.byEditor[editor] = {
        total: 0,
        completed: 0,
        incomplete: []
      };
    }
    analysis.byEditor[editor].total++;
    
    // Ensure we have proper fallbacks for display
    var displayClient = client || "Unknown Client";
    var displayFootageId = footageId || "N/A";
    var displayDetails = assignmentDetails || "N/A";
    
    // Check if checkbox is checked
    try {
      var cell = sheet.getRange(row, checkboxCol);
      var isChecked = false;
      
      if (cell.getDataValidation()) { // Has checkbox
        var value = cell.getValue();
        if (typeof value === "boolean") {
          isChecked = value;
        }
      }
      
      if (isChecked) {
        analysis.completedAssignments++;
        analysis.byDay[dayName].completed++;
        analysis.byEditor[editor].completed++;
      } else {
        analysis.incompleteAssignments++;
        // Track incomplete assignment
        var incompleteItem = {
          day: dayName,
          editor: editor,
          client: displayClient,
          assignment: displayFootageId,  // This is the footage ID
          details: displayDetails
        };
        analysis.byDay[dayName].incomplete.push(incompleteItem);
        analysis.byEditor[editor].incomplete.push(incompleteItem);
        analysis.incompleteDetails.push(incompleteItem);
      }
      
    } catch (e) {
      analysis.incompleteAssignments++;
      // If can't read checkbox, assume incomplete
      var incompleteItem = {
        day: dayName,
        editor: editor,
        client: displayClient,
        assignment: displayFootageId,  // This is the footage ID
        details: displayDetails
      };
      analysis.byDay[dayName].incomplete.push(incompleteItem);
      analysis.byEditor[editor].incomplete.push(incompleteItem);
      analysis.incompleteDetails.push(incompleteItem);
    }
  }
}

}

return analysis; } function fixDayMerges() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheets = ss.getSheets();

for (var i = 0; i < sheets.length; i++) { var sheet = sheets[i]; if (sheet.getName() === "Editor Assignments") continue;

try {
  // ORIGINAL positions for individual sheets
  sheet.getRange(3, 3, 1, 4).merge();   // Monday C-F
  sheet.getRange(3, 8, 1, 4).merge();   // Tuesday H-K
  sheet.getRange(3, 13, 1, 4).merge();  // Wednesday M-P
  sheet.getRange(3, 18, 1, 4).merge();  // Thursday R-U
  sheet.getRange(3, 23, 1, 4).merge();  // Friday W-Z
  
  Logger.log("Fixed day merges for sheet: " + sheet.getName());
} catch (e) {
  Logger.log("Error fixing merges in " + sheet.getName() + ": " + e.message);
}

}

Logger.log("Completed fixing day merges in all sheets"); }

// Test function for the new tracking system - perfect for fresh start function testWeeklyTracking() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("Editor Assignments");

if (!sheet) { Logger.log("Editor Assignments sheet not found"); return; }

// Test the weekly data sheet creation try { var weekDate = sheet.getRange("A14").getValue(); Logger.log("Week date: " + weekDate);

var sheetName = createWeeklyDataSheet(sheet, weekDate);
Logger.log("Created weekly sheet: " + sheetName);

updateSummaryTab();
Logger.log("Updated summary tab");

SpreadsheetApp.getUi().alert(
  "Fresh Start Test Completed!", 
  "Weekly tracking test completed successfully!\n\n" +
  "• Created weekly sheet: " + sheetName + "\n" +
  "• Updated summary tab\n" +
  "• Perfect headers: Done, Editor, Footage ID, Client, etc.\n" +
  "• Red checkboxes for incomplete assignments\n" +
  "• Green rows when 'Done' is checked\n" +
  "• Clean data with no redundancy\n\n" +
  "Ready to go live!",
  SpreadsheetApp.getUi().ButtonSet.OK
);

Logger.log("Weekly tracking test completed successfully");

} catch (e) { Logger.log("Error in weekly tracking test: " + e.message); SpreadsheetApp.getUi().alert( "Test Failed", "Error in weekly tracking test: " + e.message, SpreadsheetApp.getUi().ButtonSet.OK ); } }

function createTimeTrigger() { var triggers = ScriptApp.getProjectTriggers(); for (var i = 0; i < triggers.length; i++) { if (triggers[i].getHandlerFunction() === 'updateAndOrganize') { ScriptApp.deleteTrigger(triggers[i]); } }

ScriptApp.newTrigger('updateAndOrganize') .timeBased() .everyHours(1) .create();

Logger.log("Created hourly trigger for automatic updates"); }

// =========================================== // COMPLETE HISTORICAL ANALYTICS SCRIPT - FINAL VERSION // All improvements and fixes included - Perfect formatting and layout // ===========================================

/**

Main function to append current week data to history spreadsheet */ function appendWeekToRawData() { try { Logger.log("📈 Starting raw data append process...");

// Get the Editor Assignment History spreadsheet var historySSId = "1qwTH-YhxnYNW6P6pKaYswCXAE3l3d2bJm4w-nhKeTQw"; // ✅ Your Editor Assignment History var historySS = SpreadsheetApp.openById(historySSId);

// Get the current week data from main VER sheet var mainSS = SpreadsheetApp.getActiveSpreadsheet(); var sourceSheet = mainSS.getSheetByName("Editor Assignments");

if (!sourceSheet) { throw new Error("Editor Assignments sheet not found"); }

// Get week info var weekDate = sourceSheet.getRange("A14").getValue(); var weekRange = getWeekDateRange(weekDate); var weekEndDate = new Date(weekDate); weekEndDate.setDate(weekEndDate.getDate() + 4); // Friday

// Calculate quarter and month var quarter = "Q" + Math.ceil((weekEndDate.getMonth() + 1) / 3) + "_" + weekEndDate.getFullYear(); var month = Utilities.formatDate(weekEndDate, "America/New_York", "MMM_yyyy");

Logger.log("📅 Processing week: " + weekRange.displayRange + " (Quarter: " + quarter + ")");

// Get or create Raw Data sheet var rawDataSheet = historySS.getSheetByName("📈 Raw Data"); if (!rawDataSheet) { Logger.log("Creating Raw Data sheet..."); rawDataSheet = historySS.insertSheet("📈 Raw Data"); }

// FORCE HEADER REBUILD - Always recreate headers to ensure correct structure Logger.log("🔧 Force rebuilding headers to ensure correct structure...");

// Clear row 1 completely and rebuild headers rawDataSheet.getRange(1, 1, 1, 15).clear(); // Clear extra columns to be safe

var headers = [ "Week_End_Date", "Week_Range", "Assignment_Date", "Editor", "Day", "Client", "Assignment", "Completed", "Quarter", "Month", "Year", "Data_Extracted_At" ];

rawDataSheet.getRange(1, 1, 1, headers.length).setValues([headers]); rawDataSheet.getRange(1, 1, 1, headers.length).setBackground("#1f2937").setFontColor("#ffffff").setFontWeight("bold"); Logger.log("✅ Headers force-rebuilt: " + headers.join(" | "));

// Extract all assignment data from current week var assignmentData = extractWeekAssignments(sourceSheet, weekEndDate, weekRange.displayRange, quarter, month);

if (assignmentData.length === 0) { Logger.log("⚠️ No assignments found for this week"); return; }

Logger.log("📊 Extracted " + assignmentData.length + " assignments with " + assignmentData[0].length + " columns each");

// Check if this week already exists in raw data var existingData = rawDataSheet.getDataRange().getValues(); var weekAlreadyExists = false; var firstMatchRow = -1; var lastMatchRow = -1;

// Only check for existing data if we have more than just headers if (existingData.length > 1) { for (var i = 1; i < existingData.length; i++) { if (existingData[i][1] === weekRange.displayRange) { // Check Week_Range column weekAlreadyExists = true; if (firstMatchRow === -1) firstMatchRow = i + 1; // Convert to 1-based indexing lastMatchRow = i + 1; } } }

if (weekAlreadyExists) { // REPLACE existing week data Logger.log("⚠️ Week " + weekRange.displayRange + " already exists - replacing data");

// Delete existing rows for this week var rowsToDelete = lastMatchRow - firstMatchRow + 1; rawDataSheet.deleteRows(firstMatchRow, rowsToDelete);

// Insert new data at the same position rawDataSheet.insertRows(firstMatchRow, assignmentData.length); var replaceRange = rawDataSheet.getRange(firstMatchRow, 1, assignmentData.length, 12); replaceRange.setValues(assignmentData);

Logger.log("✅ Replaced " + assignmentData.length + " assignments for week " + weekRange.displayRange); } else { // APPEND new week data var lastRow = rawDataSheet.getLastRow(); var newDataRange = rawDataSheet.getRange(lastRow + 1, 1, assignmentData.length, 12); newDataRange.setValues(assignmentData);

Logger.log("✅ Added " + assignmentData.length + " assignments for new week " + weekRange.displayRange); }

} catch (error) { Logger.log("❌ Raw data append error: " + error.message); throw new Error("Raw data append failed: " + error.message); } }

/**

Extract Week Assignments from Source Sheet */ function extractWeekAssignments(sheet, weekEndDate, weekRange, quarter, month) { var assignments = []; var data = sheet.getDataRange().getValues();

// Column mappings for each day var dayColumns = { Monday: {checkbox: 2, client: 3, assignment: 4}, Tuesday: {checkbox: 8, client: 9, assignment: 10}, Wednesday: {checkbox: 14, client: 15, assignment: 16}, Thursday: {checkbox: 20, client: 21, assignment: 22}, Friday: {checkbox: 26, client: 27, assignment: 28} };

// INLINE FUNCTION: Calculate assignment date from week end date and day name function getAssignmentDate(weekEndDate, dayName) { var monday = new Date(weekEndDate); monday.setDate(monday.getDate() - 4);

var dayOffsets = {
  "Monday": 0,
  "Tuesday": 1, 
  "Wednesday": 2,
  "Thursday": 3,
  "Friday": 4
};

var offset = dayOffsets[dayName] || 0;
var assignmentDate = new Date(monday);
assignmentDate.setDate(assignmentDate.getDate() + offset);
return assignmentDate;

}

// Process each row starting from row 5 for (var row = 4; row < data.length; row++) { var editor = data[row][1]; // Column B

if (!editor || editor === "" || editor === "editor" || editor === "placeholder") {
  continue;
}

// Process each day
for (var dayName in dayColumns) {
  var cols = dayColumns[dayName];
  
  if (cols.client >= data[row].length || cols.assignment >= data[row].length) {
    continue;
  }
  
  var client = String(data[row][cols.client] || "").trim();
  var assignment = String(data[row][cols.assignment] || "").trim();
  
  // Check if valid assignment
  if (client && assignment && 
      client !== "client" && assignment !== "assignment" && 
      client !== "" && assignment !== "") {
    
    // Check completion status
    var completed = false;
    if (cols.checkbox < data[row].length) {
      var checkboxValue = data[row][cols.checkbox];
      completed = (checkboxValue === true || checkboxValue === "TRUE" || checkboxValue === 1);
    }
    
    // Calculate the actual assignment date
    var assignmentDate = getAssignmentDate(weekEndDate, dayName);
    
    // Get current timestamp for data extraction
    var extractedAt = new Date();
    
    // Add to assignments array
    assignments.push([
      weekEndDate,           // Week_End_Date
      weekRange,             // Week_Range
      assignmentDate,        // Assignment_Date
      editor,                // Editor
      dayName,               // Day
      client,                // Client
      assignment,            // Assignment
      completed,             // Completed
      quarter,               // Quarter
      month,                 // Month
      weekEndDate.getFullYear(), // Year
      extractedAt            // Data_Extracted_At (NEW!)
    ]);
  }
}

}

return assignments; }

/**

Enhanced historical trends dashboard */ function updateHistoricalTrendsDashboard() { try { Logger.log("📊 Starting enhanced historical trends dashboard update...");

var historySSId = "1qwTH-YhxnYNW6P6pKaYswCXAE3l3d2bJm4w-nhKeTQw"; var historySS = SpreadsheetApp.openById(historySSId);

// Get or create dashboard sheet var dashboardSheet = historySS.getSheetByName("📈 Historical Trends Dashboard"); if (!dashboardSheet) { dashboardSheet = historySS.insertSheet("📈 Historical Trends Dashboard"); }

// Clear and set up the dashboard dashboardSheet.clear(); buildTrendsDashboard(dashboardSheet, historySS);

Logger.log("🎉 Historical trends dashboard updated successfully");

} catch (error) { Logger.log("❌ Dashboard update error: " + error.message); throw new Error("Dashboard update failed: " + error.message); } }

/**

FIXED trends dashboard with simplified naming and matching title */ function buildTrendsDashboard(sheet, historySS) { // Get raw data for analysis var rawDataSheet = historySS.getSheetByName("📈 Raw Data"); if (!rawDataSheet) { throw new Error("Raw Data sheet not found. Run appendWeekToRawData first."); }

var rawData = rawDataSheet.getDataRange().getValues(); var analysis = analyzeHistoricalTrends(rawData);

// ===== MATCHING HEADER - Same as tab name ===== sheet.getRange("A1:i1").merge(); sheet.getRange("A1").setValue("📈 HISTORICAL TRENDS DASHBOARD"); sheet.getRange("A1").setHorizontalAlignment("center"); sheet.getRange("A1").setVerticalAlignment("middle"); sheet.getRange("A1").setFontSize(22); sheet.getRange("A1").setFontWeight("bold"); sheet.getRange("A1").setBackground("#0f4c75"); sheet.getRange("A1").setFontColor("#ffffff"); sheet.setRowHeight(1, 45);

// ===== LIVE UPDATE INDICATOR ===== var currentTime = new Date(); var timeStamp = Utilities.formatDate(currentTime, "America/New_York", "EEEE, MMMM dd, yyyy 'at' h:mm a"); sheet.getRange("A2:i2").merge(); sheet.getRange("A2").setValue("🔄 Last Updated: " + timeStamp + " • Data refreshed automatically"); sheet.getRange("A2").setHorizontalAlignment("center"); sheet.getRange("A2").setFontStyle("italic"); sheet.getRange("A2").setBackground("#e3f2fd"); sheet.getRange("A2").setFontSize(10); sheet.setRowHeight(2, 22);

// Add breathing room sheet.setRowHeight(3, 15);

// ===== SIMPLIFIED SCORECARD - NO "EXECUTIVE" ===== sheet.getRange("A4:d4").merge(); sheet.getRange("A4").setValue("🎯 TEAM PERFORMANCE SUMMARY"); sheet.getRange("A4").setFontSize(18); sheet.getRange("A4").setFontWeight("bold"); sheet.getRange("A4").setBackground("#1565c0"); sheet.getRange("A4").setFontColor("white"); sheet.getRange("A4").setHorizontalAlignment("left"); sheet.getRange("A4").setVerticalAlignment("middle"); sheet.setRowHeight(4, 35);

// KEY METRICS GRID - More Visual var kpiData = [ ["🗓️ Tracking Period:", analysis.totalWeeks + " weeks"], ["📈 Team Success Rate:", analysis.avgCompletionRate + "% " + getPerformanceEmoji(parseFloat(analysis.avgCompletionRate))], ["🏆 Top Contributor:", analysis.topPerformer + " ⭐"], ["📋 Total Work Assigned:", analysis.totalAssignments + " assignments"], ["👥 Active Team Size:", analysis.activeEditors + " editors"], ["📊 Performance Direction:", analysis.overallTrend] ];

var kpiRange = sheet.getRange(6, 1, kpiData.length, 2); kpiRange.setValues(kpiData); kpiRange.setVerticalAlignment("middle"); sheet.getRange(6, 1, kpiData.length, 1).setFontWeight("bold"); sheet.getRange(6, 1, kpiData.length, 2).setFontSize(11);

// Color-code the success rate based on performance var successRateCell = sheet.getRange(7, 2); var rate = parseFloat(analysis.avgCompletionRate); if (rate >= 90) successRateCell.setBackground("#c8e6c9").setFontColor("#1b5e20"); else if (rate >= 80) successRateCell.setBackground("#fff3e0").setFontColor("#e65100"); else successRateCell.setBackground("#ffcdd2").setFontColor("#c62828");

// Add spacing sheet.setRowHeight(12, 20);

// ===== PERFORMANCE COMPARISON TABLE - SIMPLIFIED ===== sheet.getRange("A14:i14").merge(); sheet.getRange("A14").setValue("⚡ PERFORMANCE COMPARISON • Current vs Historical"); sheet.getRange("A14").setFontSize(16); sheet.getRange("A14").setFontWeight("bold"); sheet.getRange("A14").setBackground("#2e7d32"); sheet.getRange("A14").setFontColor("white"); sheet.getRange("A14").setHorizontalAlignment("left"); sheet.getRange("A14").setVerticalAlignment("middle"); sheet.setRowHeight(14, 32);

// SIMPLIFIED comparison table - removed team participation var comparisonData = [ ["📊 Metric", "This Week", "4-Week Avg", "All-Time Avg", "📈 Trend", "📝 Insight"], ["Team Success Rate", analysis.currentWeek.avgCompletion + "%", analysis.fourWeekAvg + "%", analysis.avgCompletionRate + "%", analysis.completionTrend, getInsight("completion", parseFloat(analysis.currentWeek.avgCompletion))], ["Weekly Workload", analysis.currentWeek.totalAssignments, analysis.fourWeekAssignments, Math.round(analysis.totalAssignments / analysis.totalWeeks), analysis.volumeTrend, getInsight("volume", analysis.currentWeek.totalAssignments)] ];

var comparisonRange = sheet.getRange(16, 1, comparisonData.length, 6); comparisonRange.setValues(comparisonData); comparisonRange.setVerticalAlignment("middle"); comparisonRange.setHorizontalAlignment("center");

// Style the comparison header var comparisonHeader = sheet.getRange(16, 1, 1, 6); comparisonHeader.setBackground("#4caf50"); comparisonHeader.setFontColor("white"); comparisonHeader.setFontWeight("bold"); comparisonHeader.setHorizontalAlignment("center");

// Add borders and conditional formatting var comparisonTable = sheet.getRange(16, 1, comparisonData.length, 6); comparisonTable.setBorder(true, true, true, true, true, true);

// Add spacing - adjusted for shorter table sheet.setRowHeight(19, 20);

// ===== WEEKLY PERFORMANCE TRACKER ===== sheet.getRange("A21:i21").merge(); sheet.getRange("A21").setValue("📅 WEEKLY PERFORMANCE TRACKER • Recent History"); sheet.getRange("A21").setFontSize(16); sheet.getRange("A21").setFontWeight("bold"); sheet.getRange("A21").setBackground("#ff6f00"); sheet.getRange("A21").setFontColor("white"); sheet.getRange("A21").setHorizontalAlignment("left"); sheet.getRange("A21").setVerticalAlignment("middle"); sheet.setRowHeight(21, 32);

// Weekly data with better column names if (analysis.weeklyBreakdown.length > 0) { var weeklyHeaders = ["📅 Week", "✅ Success Rate", "📋 Total Tasks", "👥 Team Size", "🎯 Grade", "📊 Performance"]; sheet.getRange(23, 1, 1, weeklyHeaders.length).setValues([weeklyHeaders]); sheet.getRange(23, 1, 1, weeklyHeaders.length).setBackground("#ff8f00"); sheet.getRange(23, 1, 1, weeklyHeaders.length).setFontWeight("bold"); sheet.getRange(23, 1, 1, weeklyHeaders.length).setFontColor("white"); sheet.getRange(23, 1, 1, weeklyHeaders.length).setHorizontalAlignment("center"); sheet.getRange(23, 1, 1, weeklyHeaders.length).setVerticalAlignment("middle");

// Enhanced weekly data with performance indicators
var enhancedWeeklyData = [];
for (var i = 0; i < analysis.weeklyBreakdown.length; i++) {
  var week = analysis.weeklyBreakdown[i];
  var rate = parseFloat(week[1].replace('%', ''));
  var performance = getPerformanceLevel(rate);
  
  enhancedWeeklyData.push([
    week[0],                    // Week
    week[1],                    // Success Rate
    week[2],                    // Total Tasks
    week[3],                    // Team Size
    week[4],                    // Grade
    performance                 // Performance Level
  ]);
}

var weeklyDataRange = sheet.getRange(24, 1, enhancedWeeklyData.length, weeklyHeaders.length);
weeklyDataRange.setValues(enhancedWeeklyData);
weeklyDataRange.setHorizontalAlignment("center");
weeklyDataRange.setVerticalAlignment("middle");

// Apply conditional formatting to success rates
applyWeeklyPerformanceFormatting(sheet, 24, enhancedWeeklyData.length);

// Add borders
var weeklyTable = sheet.getRange(23, 1, enhancedWeeklyData.length + 1, weeklyHeaders.length);
weeklyTable.setBorder(true, true, true, true, true, true);

}

// ===== OPTIMAL COLUMN WIDTHS FOR READABILITY ===== sheet.setColumnWidth(1, 180); // Week/Metric names sheet.setColumnWidth(2, 120); // Values sheet.setColumnWidth(3, 120); // Averages sheet.setColumnWidth(4, 120); // Comparisons sheet.setColumnWidth(5, 140); // Trends sheet.setColumnWidth(6, 150); // Insights/Performance

// Freeze header for easy scrolling sheet.setFrozenRows(4);

// Add final professional touch - summary box var finalRow = 24 + (analysis.weeklyBreakdown.length || 0) + 3; sheet.getRange("A" + finalRow + ":F" + finalRow).merge(); sheet.getRange("A" + finalRow).setValue("💡 KEY INSIGHT: " + generateKeyInsight(analysis)); sheet.getRange("A" + finalRow).setBackground("#e1f5fe"); sheet.getRange("A" + finalRow).setFontStyle("italic"); sheet.getRange("A" + finalRow).setFontWeight("bold"); sheet.getRange("A" + finalRow).setWrap(true); sheet.setRowHeight(finalRow, 35); }

/**

Enhanced editor analytics dashboard */ function updateEditorAnalyticsDashboard() { try { Logger.log("👨‍💼 Starting enhanced editor analytics dashboard update...");

var historySSId = "1qwTH-YhxnYNW6P6pKaYswCXAE3l3d2bJm4w-nhKeTQw"; var historySS = SpreadsheetApp.openById(historySSId);

// Get or create dashboard sheet var dashboardSheet = historySS.getSheetByName("👨‍💼 Editor Analytics Dashboard"); if (!dashboardSheet) { dashboardSheet = historySS.insertSheet("👨‍💼 Editor Analytics Dashboard"); }

// Clear and build the dashboard dashboardSheet.clear(); buildEditorAnalyticsDashboard(dashboardSheet, historySS);

Logger.log("🎉 Editor analytics dashboard updated successfully");

} catch (error) { Logger.log("❌ Editor dashboard update error: " + error.message); throw new Error("Editor dashboard update failed: " + error.message); } }

/**

FIXED: Success Rate parsing with robust logic */ function applyHistoricalSuccessRateFormatting(sheet, startRow, numRows) { Logger.log("Applying complete Editor Analytics formatting...");

// SUCCESS RATE FORMATTING (Column 2) - FIXED PARSING for (var i = 0; i < numRows; i++) { var cell = sheet.getRange(startRow + i, 2); var cellValue = cell.getValue();

Logger.log("Row " + (startRow + i) + " - Raw value: '" + cellValue + "' (type: " + typeof cellValue + ")");

// ROBUST parsing for different value types
var rate = 0;
if (typeof cellValue === 'number') {
  // If it's already a number, check if it's a decimal (0.83) or percentage (83)
  if (cellValue <= 1) {
    rate = cellValue * 100; // Convert 0.83 to 83
  } else {
    rate = cellValue; // Already a percentage like 83
  }
} else if (typeof cellValue === 'string') {
  // Remove % sign, spaces, and parse
  var cleanValue = cellValue.replace(/[%\s]/g, '');
  rate = parseFloat(cleanValue);
  Logger.log("String parsed: '" + cellValue + "' -> '" + cleanValue + "' -> " + rate);
}

// Validate the parsed number
if (isNaN(rate)) {
  Logger.log("Could not parse rate, skipping row " + (startRow + i));
  continue;
}

Logger.log("Final rate for formatting: " + rate);

// Apply colors based on rate - Historical Dashboard colors
if (rate >= 90) {
  cell.setBackground("#4caf50").setFontColor("white").setFontWeight("bold");
  Logger.log("Applied GREEN (90%+) for " + rate + "%");
} else if (rate >= 80) {
  cell.setBackground("#8bc34a").setFontColor("white");
  Logger.log("Applied LIGHT GREEN (80-89%) for " + rate + "%");
} else if (rate >= 70) {
  cell.setBackground("#ffeb3b").setFontColor("#333333");
  Logger.log("Applied YELLOW (70-79%) for " + rate + "%");
} else {
  cell.setBackground("#f44336").setFontColor("white").setFontWeight("bold");
  Logger.log("Applied RED (<70%) for " + rate + "%");
}

}

// LETTER GRADE FORMATTING (Column 6) - Keep working perfectly! for (var i = 0; i < numRows; i++) { var cell = sheet.getRange(startRow + i, 6); var grade = String(cell.getValue()).trim();

if (grade.startsWith("A")) {
  cell.setBackground("#1976d2").setFontColor("#ffffff").setFontWeight("bold");  // Blue for A
} else if (grade.startsWith("B")) {
  cell.setBackground("#388e3c").setFontColor("#ffffff").setFontWeight("bold");  // Green for B
} else if (grade.startsWith("C")) {
  cell.setBackground("#f57c00").setFontColor("#ffffff").setFontWeight("normal"); // Orange for C
} else if (grade.startsWith("D")) {
  cell.setBackground("#d32f2f").setFontColor("#ffffff").setFontWeight("normal"); // Red for D
} else if (grade.startsWith("F")) {
  cell.setBackground("#b71c1c").setFontColor("#ffffff").setFontWeight("bold");  // Dark Red for F
}

}

// OVERALL SCORE FORMATTING (Column 9) - Keep working! for (var i = 0; i < numRows; i++) { var cell = sheet.getRange(startRow + i, 9); var value = cell.getValue(); var scoreText = String(value).replace(' pts', '').trim(); var score = parseFloat(scoreText);

if (!isNaN(score)) {
  if (score >= 85) {
    cell.setBackground("#7b1fa2").setFontColor("#ffffff").setFontWeight("bold");
  } else if (score >= 70) {
    cell.setBackground("#1976d2").setFontColor("#ffffff").setFontWeight("normal");
  } else if (score >= 55) {
    cell.setBackground("#f57c00").setFontColor("#ffffff").setFontWeight("normal");
  } else {
    cell.setBackground("#d32f2f").setFontColor("#ffffff").setFontWeight("normal");
  }
}

}

Logger.log("Complete formatting applied successfully!"); }

/**

FIXED editor analytics dashboard with better team overview layout and matching names / /*
FIXED editor analytics dashboard with proper team overview layout */ function buildEditorAnalyticsDashboard(sheet, historySS) { // Get raw data var rawDataSheet = historySS.getSheetByName("📈 Raw Data"); if (!rawDataSheet) { throw new Error("Raw Data sheet not found. Run appendWeekToRawData first."); }

var rawData = rawDataSheet.getDataRange().getValues(); var editorAnalysis = analyzeEditorHistorical(rawData);

// ===== MATCHING HEADER - Same as tab name ===== sheet.getRange("A1:I1").merge(); sheet.getRange("A1").setValue("👨‍💼 EDITOR ANALYTICS DASHBOARD"); sheet.getRange("A1").setHorizontalAlignment("center"); sheet.getRange("A1").setVerticalAlignment("middle"); sheet.getRange("A1").setFontSize(22); sheet.getRange("A1").setFontWeight("bold"); sheet.getRange("A1").setBackground("#4a148c"); sheet.getRange("A1").setFontColor("white"); sheet.setRowHeight(1, 45);

// ===== LIVE UPDATE INDICATOR ===== var currentTime = new Date(); var timeStamp = Utilities.formatDate(currentTime, "America/New_York", "EEEE, MMMM dd, yyyy 'at' h:mm a"); sheet.getRange("A2:I2").merge(); sheet.getRange("A2").setValue("🔄 Last Updated: " + timeStamp + " • Data refreshed automatically"); sheet.getRange("A2").setHorizontalAlignment("center"); sheet.getRange("A2").setFontStyle("italic"); sheet.getRange("A2").setBackground("#f3e5f5"); sheet.getRange("A2").setFontSize(10); sheet.setRowHeight(2, 22);

// Add breathing room sheet.setRowHeight(3, 15);

// ===== TEAM OVERVIEW SCORECARD ===== sheet.getRange("A4:c4").merge(); sheet.getRange("A4").setValue("📊 TEAM OVERVIEW"); sheet.getRange("A4").setFontSize(18); sheet.getRange("A4").setFontWeight("bold"); sheet.getRange("A4").setBackground("#6a1b9a"); sheet.getRange("A4").setFontColor("white"); sheet.getRange("A4").setHorizontalAlignment("left"); sheet.getRange("A4").setVerticalAlignment("middle"); sheet.setRowHeight(4, 35);

// FIXED: Team overview with merged descriptors but single data cells var teamData = [ ["👥 Total Team Members:", "", editorAnalysis.totalEditors + " editors"], ["🌟 Top Performers (95%+):", "", editorAnalysis.exceptionalCount + " editors"], ["🔥 Strong Performers (90%+):", "", editorAnalysis.excellentCount + " editors"], ["⚠️ Need Coaching (<70%):", "", editorAnalysis.needsSupportCount + " editors"], ["📈 Team Average:", "", editorAnalysis.teamAverage + "% " + getPerformanceEmoji(parseFloat(editorAnalysis.teamAverage))] ];

var teamRange = sheet.getRange(6, 1, teamData.length, 3); teamRange.setValues(teamData); teamRange.setVerticalAlignment("middle"); teamRange.setFontSize(11);

// FIXED: Merge only columns A-B for descriptors, leave column C as single cell for data for (var i = 0; i < teamData.length; i++) { // Merge columns A-B for the descriptor labels (keep merged) sheet.getRange(6 + i, 1, 1, 2).merge(); sheet.getRange(6 + i, 1).setFontWeight("bold");

// Column C stays as single cell for the values (NOT merged)

}

// Color-code team average (column C only now) var teamAvgCell = sheet.getRange(10, 3); var avgRate = parseFloat(editorAnalysis.teamAverage); if (avgRate >= 90) teamAvgCell.setBackground("#c8e6c9").setFontColor("#1b5e20"); else if (avgRate >= 80) teamAvgCell.setBackground("#fff3e0").setFontColor("#e65100"); else teamAvgCell.setBackground("#ffcdd2").setFontColor("#c62828");

// Add spacing sheet.setRowHeight(11, 20);

// ===== INDIVIDUAL PERFORMANCE TABLE ===== sheet.getRange("A13:i13").merge(); sheet.getRange("A13").setValue("👤 INDIVIDUAL PERFORMANCE BREAKDOWN"); sheet.getRange("A13").setFontSize(16); sheet.getRange("A13").setFontWeight("bold"); sheet.getRange("A13").setBackground("#7b1fa2"); sheet.getRange("A13").setFontColor("white"); sheet.getRange("A13").setHorizontalAlignment("left"); sheet.getRange("A13").setVerticalAlignment("middle"); sheet.setRowHeight(13, 32);

// MUCH CLEARER column headers var headers = [ "👤 Team Member", "✅ Success Rate", "📊 Visual Progress", "📋 Total Tasks", "🗓️ Active Weeks", "🎯 Letter Grade", "📈 Recent Trend", "⭐ Performance Level", "🏆 Overall Score" ];

var headerRange = sheet.getRange(15, 1, 1, headers.length); headerRange.setValues([headers]); headerRange.setBackground("#8e24aa"); headerRange.setFontColor("white"); headerRange.setFontWeight("bold"); headerRange.setHorizontalAlignment("center"); headerRange.setVerticalAlignment("middle"); sheet.setRowHeight(15, 38);

// Enhanced editor data with better formatting if (editorAnalysis.editors.length > 0) { var editorData = [];

for (var i = 0; i < editorAnalysis.editors.length; i++) {
  var editor = editorAnalysis.editors[i];
  var progressBar = createSuperProgressBar(parseFloat(editor.avgCompletionRate));
  var enhancedStatus = enhanceStatusDescription(editor.status, parseFloat(editor.avgCompletionRate));
  
  editorData.push([
    editor.name + " 👨‍💼",
    editor.avgCompletionRate + "%",
    progressBar,
    editor.totalAssignments + " tasks",
    editor.weeksActive + " weeks",
    editor.grade,
    editor.trend,
    enhancedStatus,
    editor.score + " pts"
  ]);
}

var dataRange = sheet.getRange(16, 1, editorData.length, editorData[0].length);
dataRange.setValues(editorData);
dataRange.setHorizontalAlignment("center");
dataRange.setVerticalAlignment("middle");
dataRange.setFontSize(10);

// Add professional borders
var tableRange = sheet.getRange(15, 1, editorData.length + 1, headers.length);
tableRange.setBorder(true, true, true, true, true, true);

// Apply FIXED conditional formatting with grade-matching colors
applyHistoricalSuccessRateFormatting(sheet, 16, editorData.length);

// Zebra striping for readability
for (var i = 0; i < editorData.length; i++) {
  if (i % 2 === 0) {
    // Only apply zebra to non-colored columns (columns 1, 3, 4, 5, 7, 8)
    var zebraColumns = [1, 3, 4, 5, 7, 8];
    for (var j = 0; j < zebraColumns.length; j++) {
      sheet.getRange(16 + i, zebraColumns[j]).setBackground("#fafafa");
    }
  }
}

}

// ===== FIXED COLUMN WIDTHS - Much wider for full visibility ===== sheet.setColumnWidth(1, 160); // Team Member sheet.setColumnWidth(2, 120); // Success Rate (wider) sheet.setColumnWidth(3, 240); // Visual Progress (much wider) sheet.setColumnWidth(4, 110); // Total Tasks (wider) sheet.setColumnWidth(5, 120); // Active Weeks (wider) sheet.setColumnWidth(6, 120); // Letter Grade (wider) sheet.setColumnWidth(7, 140); // Recent Trend (wider) sheet.setColumnWidth(8, 180); // Performance Level (much wider) sheet.setColumnWidth(9, 120); // Overall Score (wider)

// ===== PROFESSIONAL LEGEND & INSIGHTS ===== var legendRow = 16 + editorAnalysis.editors.length + 2;

// Performance Guide sheet.getRange("A" + legendRow + ":I" + legendRow).merge(); sheet.getRange("A" + legendRow).setValue("📖 PERFORMANCE GUIDE: Exceptional (95%+) • Excellent (90%+) • Strong (80%+) • Good (70%+) • Needs Coaching (<70%)"); sheet.getRange("A" + legendRow).setFontStyle("italic"); sheet.getRange("A" + legendRow).setBackground("#e8f0fe"); sheet.getRange("A" + legendRow).setFontSize(9); sheet.getRange("A" + legendRow).setWrap(true); sheet.setRowHeight(legendRow, 25);

// Score explanation sheet.getRange("A" + (legendRow + 1) + ":I" + (legendRow + 1)).merge(); sheet.getRange("A" + (legendRow + 1)).setValue("🏆 SCORE BREAKDOWN: Success Rate (70 pts max) + Workload Bonus (20 pts max) + Client Variety (10 pts max) = Total Performance Score"); sheet.getRange("A" + (legendRow + 1)).setFontStyle("italic"); sheet.getRange("A" + (legendRow + 1)).setBackground("#f3e5f5"); sheet.getRange("A" + (legendRow + 1)).setFontSize(9); sheet.getRange("A" + (legendRow + 1)).setWrap(true); sheet.setRowHeight(legendRow + 1, 25);

// Team insight sheet.getRange("A" + (legendRow + 3) + ":I" + (legendRow + 3)).merge(); sheet.getRange("A" + (legendRow + 3)).setValue("💡 TEAM INSIGHT: " + generateTeamInsight(editorAnalysis)); sheet.getRange("A" + (legendRow + 3)).setBackground("#e1f5fe"); sheet.getRange("A" + (legendRow + 3)).setFontWeight("bold"); sheet.getRange("A" + (legendRow + 3)).setWrap(true); sheet.setRowHeight(legendRow + 3, 35); }

/**

FIXED historical trends analysis with correct trend calculations */ function analyzeHistoricalTrends(rawData) { var weeks = {}; var editors = {}; var weeklyStats = {}; var totalAssignments = 0;

// Process all historical data for (var i = 1; i < rawData.length; i++) { var row = rawData[i];

var weekEndDate = row[0];      
var weekRange = row[1];        
var assignmentDate = row[2];   
var editor = row[3];           
var day = row[4];              
var client = row[5];           
var assignment = row[6];       
var completed = row[7];        
var quarter = row[8];          
var month = row[9];            
var year = row[10];            

if (!editor || !weekRange || editor === "" || weekRange === "") {
  continue;
}

weeks[weekRange] = true;
totalAssignments++;

if (!weeklyStats[weekRange]) {
  weeklyStats[weekRange] = {
    totalAssignments: 0,
    completedAssignments: 0,
    editors: new Set()
  };
}

weeklyStats[weekRange].totalAssignments++;
if (completed === true || completed === "TRUE" || completed === 1) {
  weeklyStats[weekRange].completedAssignments++;
}
weeklyStats[weekRange].editors.add(editor);

if (!editors[editor]) {
  editors[editor] = {
    totalAssignments: 0,
    completedAssignments: 0,
    weekRanges: new Set()
  };
}

editors[editor].totalAssignments++;
if (completed === true || completed === "TRUE" || completed === 1) {
  editors[editor].completedAssignments++;
}
editors[editor].weekRanges.add(weekRange);

}

// Calculate metrics var totalWeeks = Object.keys(weeks).length; var activeEditors = Object.keys(editors).length;

var topPerformer = ""; var highestCompletionRate = 0;

for (var editorName in editors) { var editor = editors[editorName]; var completionRate = editor.totalAssignments > 0 ? (editor.completedAssignments / editor.totalAssignments) * 100 : 0;

if (completionRate > highestCompletionRate) {
  highestCompletionRate = completionRate;
  topPerformer = editorName;
}

}

var totalCompleted = 0; for (var editorName in editors) { totalCompleted += editors[editorName].completedAssignments; } var avgCompletionRate = totalAssignments > 0 ? ((totalCompleted / totalAssignments) * 100).toFixed(1) : 0;

var sortedWeeks = Object.keys(weeklyStats).sort(); var currentWeekKey = sortedWeeks[sortedWeeks.length - 1]; var currentWeek = weeklyStats[currentWeekKey] || { totalAssignments: 0, completedAssignments: 0, editors: new Set() };

var currentWeekCompletion = currentWeek.totalAssignments > 0 ? ((currentWeek.completedAssignments / currentWeek.totalAssignments) * 100).toFixed(1) : 0;

// FIXED: Calculate 4-week averages and trends properly var recentWeeks = sortedWeeks.slice(-4); var fourWeekAssignments = 0; var fourWeekCompleted = 0; var fourWeekEditors = new Set();

for (var i = 0; i < recentWeeks.length; i++) { var weekData = weeklyStats[recentWeeks[i]]; fourWeekAssignments += weekData.totalAssignments; fourWeekCompleted += weekData.completedAssignments; weekData.editors.forEach(function(editor) { fourWeekEditors.add(editor); }); }

var fourWeekAvg = fourWeekAssignments > 0 ? ((fourWeekCompleted / fourWeekAssignments) * 100).toFixed(1) : 0; var avgEditorsPerWeek = Math.round(activeEditors / totalWeeks);

// FIXED: Proper trend calculations var completionTrend = "➡️ Stable"; if (totalWeeks > 1) { var currentCompletion = parseFloat(currentWeekCompletion); var overallCompletion = parseFloat(avgCompletionRate); if (currentCompletion > overallCompletion + 5) completionTrend = "📈 Improving"; else if (currentCompletion < overallCompletion - 5) completionTrend = "📉 Declining"; }

var volumeTrend = "➡️ Stable"; if (totalWeeks > 1) { var avgWeeklyAssignments = Math.round(totalAssignments / totalWeeks); if (currentWeek.totalAssignments > avgWeeklyAssignments * 1.1) volumeTrend = "📈 Increasing"; else if (currentWeek.totalAssignments < avgWeeklyAssignments * 0.9) volumeTrend = "📉 Decreasing"; }

// Create weekly breakdown var weeklyBreakdown = []; for (var i = 0; i < sortedWeeks.length; i++) { var weekKey = sortedWeeks[i]; var weekData = weeklyStats[weekKey]; var weekCompletion = weekData.totalAssignments > 0 ? ((weekData.completedAssignments / weekData.totalAssignments) * 100).toFixed(1) : 0; var grade = getLetterGrade(parseFloat(weekCompletion));

weeklyBreakdown.push([
  weekKey,
  weekCompletion + "%",
  weekData.totalAssignments,
  weekData.editors.size,
  grade
]);

}

return { totalWeeks: totalWeeks, avgCompletionRate: avgCompletionRate, topPerformer: topPerformer, totalAssignments: totalAssignments, activeEditors: activeEditors, overallTrend: completionTrend, currentWeek: { avgCompletion: currentWeekCompletion, totalAssignments: currentWeek.totalAssignments, activeEditors: currentWeek.editors.size }, fourWeekAvg: fourWeekAvg, fourWeekAssignments: Math.round(fourWeekAssignments / recentWeeks.length), fourWeekEditors: fourWeekEditors.size, avgEditorsPerWeek: avgEditorsPerWeek, completionTrend: completionTrend, volumeTrend: volumeTrend, weeklyBreakdown: weeklyBreakdown }; }

/**

Enhanced editor historical analysis - FIXED for correct raw data structure */ function analyzeEditorHistorical(rawData) { var editors = {};

// Process historical data - FIXED column references for (var i = 1; i < rawData.length; i++) { var row = rawData[i];

// CORRECT column mapping:
var weekEndDate = row[0];      // Week_End_Date
var weekRange = row[1];        // Week_Range  
var assignmentDate = row[2];   // Assignment_Date
var editor = row[3];           // Editor
var day = row[4];              // Day
var client = row[5];           // Client
var assignment = row[6];       // Assignment
var completed = row[7];        // Completed (boolean)
var quarter = row[8];          // Quarter
var month = row[9];            // Month
var year = row[10];            // Year

// Skip invalid rows
if (!editor || editor === "" || !weekRange || weekRange === "") {
  continue;
}

if (!editors[editor]) {
  editors[editor] = {
    totalAssignments: 0,
    completedAssignments: 0,
    weekRanges: new Set(),
    weeklyRates: [],
    clients: new Set()
  };
}

editors[editor].totalAssignments++;
if (completed === true || completed === "TRUE" || completed === 1) {
  editors[editor].completedAssignments++;
}
editors[editor].weekRanges.add(weekRange);
editors[editor].clients.add(client);

}

// Calculate weekly completion rates for each editor var weeklyEditorStats = {}; for (var i = 1; i < rawData.length; i++) { var row = rawData[i]; var weekRange = row[1]; var editor = row[3]; var completed = row[7];

if (!editor || !weekRange) continue;

var key = editor + "|" + weekRange;
if (!weeklyEditorStats[key]) {
  weeklyEditorStats[key] = {
    total: 0,
    completed: 0
  };
}

weeklyEditorStats[key].total++;
if (completed === true || completed === "TRUE" || completed === 1) {
  weeklyEditorStats[key].completed++;
}

}

// Add weekly rates to each editor for (var editorName in editors) { var editor = editors[editorName]; editor.weeklyRates = [];

editor.weekRanges.forEach(function(weekRange) {
  var key = editorName + "|" + weekRange;
  if (weeklyEditorStats[key]) {
    var weekRate = weeklyEditorStats[key].total > 0 ? 
      (weeklyEditorStats[key].completed / weeklyEditorStats[key].total) * 100 : 0;
    editor.weeklyRates.push(weekRate);
  }
});

}

// Convert to enhanced analysis array var result = []; var teamTotal = 0; var editorCount = 0; var exceptionalCount = 0; var excellentCount = 0; var needsSupportCount = 0;

for (var editorName in editors) { var editor = editors[editorName]; var avgCompletionRate = editor.totalAssignments > 0 ? ((editor.completedAssignments / editor.totalAssignments) * 100).toFixed(1) : 0; var grade = getLetterGrade(parseFloat(avgCompletionRate)); var trend = calculateEditorTrend(editor.weeklyRates); var status = getStatusWithEmoji(parseFloat(avgCompletionRate)); var score = calculatePerformanceScore(parseFloat(avgCompletionRate), editor.totalAssignments, editor.clients.size);

result.push({
  name: editorName,
  avgCompletionRate: avgCompletionRate,
  totalAssignments: editor.totalAssignments,
  weeksActive: editor.weekRanges.size,
  grade: grade,
  trend: trend,
  status: status,
  score: score
});

teamTotal += parseFloat(avgCompletionRate);
editorCount++;

// Count performance categories
if (parseFloat(avgCompletionRate) >= 95) exceptionalCount++;
else if (parseFloat(avgCompletionRate) >= 90) excellentCount++;
else if (parseFloat(avgCompletionRate) < 70) needsSupportCount++;

}

// Sort by completion rate (descending) result.sort(function(a, b) { return parseFloat(b.avgCompletionRate) - parseFloat(a.avgCompletionRate); });

var teamAverage = editorCount > 0 ? (teamTotal / editorCount).toFixed(1) : 0;

return { editors: result, totalEditors: editorCount, teamAverage: teamAverage, exceptionalCount: exceptionalCount, excellentCount: excellentCount, needsSupportCount: needsSupportCount }; }

/**

FIXED metrics guide with correct highlighting and visual separation */ function createMetricsGuideTab() { try { var historySSId = "1qwTH-YhxnYNW6P6pKaYswCXAE3l3d2bJm4w-nhKeTQw"; var historySS = SpreadsheetApp.openById(historySSId);

// Get or create metrics guide sheet var guideSheet = historySS.getSheetByName("📖 Metrics Guide"); if (!guideSheet) { guideSheet = historySS.insertSheet("📖 Metrics Guide"); }

guideSheet.clear();

// Header guideSheet.getRange("A1:D1").merge(); guideSheet.getRange("A1").setValue("📖 PERFORMANCE DASHBOARD GUIDE"); guideSheet.getRange("A1").setHorizontalAlignment("center"); guideSheet.getRange("A1").setFontSize(20); guideSheet.getRange("A1").setFontWeight("bold"); guideSheet.getRange("A1").setBackground("#4285f4"); guideSheet.getRange("A1").setFontColor("white"); guideSheet.setRowHeight(1, 40);

// Main table header guideSheet.getRange("A3:D3").setValues([["📊 METRIC", "🔍 DEFINITION", "🧮 HOW IT'S CALCULATED", "💡 WHAT IT MEANS"]]); guideSheet.getRange("A3:D3").setBackground("#34a853").setFontColor("white").setFontWeight("bold"); guideSheet.getRange("A3:D3").setHorizontalAlignment("center");

var metricsData = [ // INDIVIDUAL METRICS SECTION ["", "", "", ""], ["📊 INDIVIDUAL METRICS", "", "", ""], ["", "", "", ""], ["✅ Success Rate", "Percentage of assignments completed successfully", "(Completed Tasks ÷ Total Tasks) × 100", "Higher = Better. 90%+ is excellent, <70% needs coaching"], ["📊 Visual Progress", "Graphical bar showing completion percentage", "Progress bar with filled/empty circles + percentage", "Quick visual indicator of individual performance"], ["📋 Total Tasks", "Number of assignments handled", "Count of all assignments given to editor", "Shows workload capacity and contribution level"], ["🗓️ Active Weeks", "Number of weeks with assignments", "Count of weeks where editor had tasks", "Indicates consistency and ongoing engagement"], ["🎯 Letter Grade", "Academic-style performance rating", "A: 90%+, B: 80-89%, C: 70-79%, D: 65-69%, F: <65%", "Familiar grading system for quick assessment"], ["📈 Recent Trend", "Direction of performance over last 4 weeks", "Compares recent 4 weeks to historical average", "📈 = Improving, 📉 = Declining, ➡️ = Stable"], ["⭐ Performance Level", "Overall performance category", "Based on success rate with descriptive labels", "Clear category for coaching and recognition"], ["🏆 Overall Score", "Composite performance rating (0-100 points)", "Success Rate (70pts) + Workload (20pts) + Variety (10pts)", "Holistic view: 85+ Exceptional, 70+ Strong, 55+ Good"],

// VISUAL SEPARATOR ROW ["", "", "", ""],

// TEAM METRICS SECTION ["👥 TEAM METRICS", "", "", ""], ["", "", "", ""], ["👥 Team Size", "Number of active team members", "Count of editors with assignments in period", "Shows current team capacity"], ["📈 Team Average", "Average success rate across all editors", "Sum of all success rates ÷ Number of editors", "Overall team performance benchmark"], ["🌟 Top Performers", "Editors with 95%+ success rate", "Count of editors achieving exceptional performance", "Recognition tier - share best practices"], ["🔥 Strong Performers", "Editors with 90%+ success rate", "Count of editors achieving excellent performance", "Solid contributors - maintain standards"], ["⚠️ Need Coaching", "Editors with <70% success rate", "Count of editors requiring immediate support", "Priority focus - provide training/support"], ["📋 Weekly Workload", "Total assignments processed per week", "Sum of all individual assignments in week", "Team capacity and volume tracking"],

// VISUAL SEPARATOR ROW ["", "", "", ""],

// PERFORMANCE LEVELS SECTION ["📊 PERFORMANCE LEVELS", "🎯 SUCCESS RATE RANGE", "🎨 COLOR CODE", "🚀 RECOMMENDED ACTION"], ["", "", "", ""], ["🌟 Exceptional", "95% - 100%", "Dark Green", "Recognize publicly, document best practices"], ["🔥 Excellent", "90% - 94%", "Medium Green", "Maintain current approach, consider leadership roles"], ["✅ Strong", "80% - 89%", "Light Green", "Solid performance, look for growth opportunities"], ["👍 Good", "70% - 79%", "Yellow", "Monitor closely, provide targeted support"], ["⚠️ Fair", "60% - 69%", "Orange", "Immediate coaching, identify improvement areas"], ["🔴 Needs Focus", "Below 60%", "Red", "Urgent intervention, comprehensive support plan"],

// VISUAL SEPARATOR ROW ["", "", "", ""],

// SCORING SYSTEM SECTION ["🏆 SCORING SYSTEM BREAKDOWN", "", "", ""], ["", "", "", ""], ["Success Rate Component", "0-70 points maximum", "Your completion % × 0.7", "Core performance measure (70% of total score)"], ["Workload Bonus", "0-20 points maximum", "Number of tasks × 0.5 (capped at 20)", "Rewards high productivity and contribution"], ["Client Variety Bonus", "0-10 points maximum", "Number of clients × 2 (capped at 10)", "Rewards versatility and adaptability"], ["Total Performance Score", "0-100 points possible", "Sum of all three components", "Holistic performance rating"],

// VISUAL SEPARATOR ROW ["", "", "", ""],

// SCORE INTERPRETATION SECTION ["🎯 SCORE INTERPRETATION", "", "", ""], ["", "", "", ""], ["85-100 points", "🌟 Exceptional Overall Performance", "Top tier", "Ready for advanced responsibilities"], ["70-84 points", "🔥 Strong Balanced Performance", "High performer", "Solid contributor, growth potential"], ["55-69 points", "✅ Good Performance", "Meeting standards", "Consistent performer, room for improvement"], ["40-54 points", "⚠️ Needs Improvement", "Below expectations", "Requires focused development plan"], ["Below 40 points", "🔴 Requires Immediate Attention", "Critical", "Comprehensive intervention needed"],

// VISUAL SEPARATOR ROW ["", "", "", ""],

// DASHBOARD FEATURES SECTION ["🔄 DASHBOARD FEATURES", "", "", ""], ["", "", "", ""], ["Real-time Updates", "Shows exact time of last data refresh", "Automatic timestamp", "Ensures you're viewing current information"], ["Visual Progress Bars", "Graphical representation of completion rates", "Filled/empty circles with percentage", "Quick visual assessment without reading numbers"], ["Color Coding", "Performance-based background colors", "Green = Good, Yellow = Caution, Red = Action", "Instant visual priorities and status"], ["Trend Indicators", "Arrows showing performance direction", "📈 Up, 📉 Down, ➡️ = Stable", "Quick assessment of improvement/decline"], ["Team Insights", "Auto-generated coaching recommendations", "AI analysis of team performance patterns", "Actionable next steps for team management"] ];

// Add all data starting from row 4 var dataRange = guideSheet.getRange(4, 1, metricsData.length, 4); dataRange.setValues(metricsData);

// FIXED: Apply section headers with CORRECT row numbers // Individual Metrics Section (row 5) guideSheet.getRange(5, 1, 1, 4).setBackground("#1976d2").setFontColor("white").setFontWeight("bold"); guideSheet.getRange(5, 1, 1, 4).setHorizontalAlignment("center");

// Team Metrics Section (row 16) - FIXED ROW NUMBER guideSheet.getRange(16, 1, 1, 4).setBackground("#ff9800").setFontColor("white").setFontWeight("bold"); guideSheet.getRange(16, 1, 1, 4).setHorizontalAlignment("center");

// Performance Levels Section (row 26) guideSheet.getRange(26, 1, 1, 4).setBackground("#4caf50").setFontColor("white").setFontWeight("bold"); guideSheet.getRange(26, 1, 1, 4).setHorizontalAlignment("center");

// Scoring System Section (row 37) guideSheet.getRange(37, 1, 1, 4).setBackground("#9c27b0").setFontColor("white").setFontWeight("bold"); guideSheet.getRange(37, 1, 1, 4).setHorizontalAlignment("center");

// Score Interpretation Section (row 46) guideSheet.getRange(46, 1, 1, 4).setBackground("#f44336").setFontColor("white").setFontWeight("bold"); guideSheet.getRange(46, 1, 1, 4).setHorizontalAlignment("center");

// Dashboard Features Section (row 56) guideSheet.getRange(56, 1, 1, 4).setBackground("#607d8b").setFontColor("white").setFontWeight("bold"); guideSheet.getRange(56, 1, 1, 4).setHorizontalAlignment("center");

// VISUAL SEPARATION: Add borders to individual sections, NOT connected var sections = [ {start: 3, end: 15}, // Header + Individual Metrics {start: 16, end: 24}, // Team Metrics
{start: 26, end: 35}, // Performance Levels {start: 37, end: 44}, // Scoring System {start: 46, end: 54}, // Score Interpretation {start: 56, end: 62} // Dashboard Features ];

// Apply borders to each section separately for visual separation for (var i = 0; i < sections.length; i++) { var section = sections[i]; var sectionRange = guideSheet.getRange(section.start, 1, section.end - section.start + 1, 4); sectionRange.setBorder(true, true, true, true, true, true); }

// Set optimal column widths guideSheet.setColumnWidth(1, 220); guideSheet.setColumnWidth(2, 320); guideSheet.setColumnWidth(3, 280); guideSheet.setColumnWidth(4, 320);

Logger.log("✅ Fixed Metrics Guide with proper section separation");

} catch (error) { Logger.log("❌ Error creating metrics guide: " + error.message); } }

// =========================================== // HELPER FUNCTIONS // ===========================================

/**

Helper functions for enhanced visual appeal */ function getPerformanceEmoji(rate) { if (rate >= 95) return "🌟"; if (rate >= 90) return "🔥"; if (rate >= 80) return "✅"; if (rate >= 70) return "👍"; if (rate >= 60) return "⚠️"; return "🔴"; }

function getPerformanceLevel(rate) { if (rate >= 95) return "🌟 Exceptional"; if (rate >= 90) return "🔥 Excellent"; if (rate >= 80) return "✅ Strong"; if (rate >= 70) return "👍 Good"; if (rate >= 60) return "⚠️ Fair"; return "🔴 Needs Focus"; }

/**

Simplified getInsight function without participation metric */ function getInsight(type, value) { switch(type) { case "completion": if (value >= 90) return "Excellent! 🎯"; if (value >= 80) return "Strong 💪"; if (value >= 70) return "Good 👍"; return "Needs focus 🔍"; case "volume": if (value >= 50) return "High output 📈"; if (value >= 30) return "Steady pace 📊"; return "Light workload 📋"; default: return "Stable 📊"; } }

function enhanceStatusDescription(status, rate) { if (rate >= 95) return "🌟 Top Performer"; if (rate >= 90) return "🔥 High Achiever"; if (rate >= 80) return "✅ Solid Contributor"; if (rate >= 70) return "👍 Good Worker"; if (rate >= 60) return "⚠️ Needs Support"; return "🔴 Requires Coaching"; }

function createSuperProgressBar(percentage) { var barLength = 15; var filled = Math.round((percentage / 100) * barLength); var empty = barLength - filled;

var filledChar = "●"; var emptyChar = "○";

var bar = ""; for (var i = 0; i < filled; i++) { bar += filledChar; } for (var i = 0; i < empty; i++) { bar += emptyChar; }

return bar + " " + percentage.toFixed(1) + "%"; }

function applyWeeklyPerformanceFormatting(sheet, startRow, numRows) { for (var i = 0; i < numRows; i++) { var cell = sheet.getRange(startRow + i, 2); // Success rate column var value = cell.getValue(); var rate = parseFloat(String(value).replace('%', ''));

if (rate >= 90) {
  cell.setBackground("#4caf50").setFontColor("white").setFontWeight("bold");
} else if (rate >= 80) {
  cell.setBackground("#8bc34a").setFontColor("white");
} else if (rate >= 70) {
  cell.setBackground("#ffeb3b").setFontColor("#333333");
} else {
  cell.setBackground("#f44336").setFontColor("white").setFontWeight("bold");
}

} }

/**

Get status with visual indicators */ function getStatusWithEmoji(rate) { if (rate >= 95) return "🌟 Exceptional"; if (rate >= 90) return "🔥 Excellent"; if (rate >= 80) return "✅ Strong"; if (rate >= 70) return "👍 Good"; if (rate >= 60) return "⚠️ Fair"; return "🔴 Needs Focus"; }

/**

Calculate editor trend from weekly rates */ function calculateEditorTrend(weeklyRates) { if (weeklyRates.length < 2) return "➡️ Stable";

var recentRates = weeklyRates.slice(-4); // Last 4 weeks var olderRates = weeklyRates.slice(0, -4);

if (olderRates.length === 0) return "📊 New";

var recentAvg = recentRates.reduce(function(a, b) { return a + b; }, 0) / recentRates.length; var olderAvg = olderRates.reduce(function(a, b) { return a + b; }, 0) / olderRates.length;

var difference = recentAvg - olderAvg;

if (difference > 5) return "📈 Strong Growth"; if (difference > 2) return "📈 Improving"; if (difference < -5) return "📉 Concerning"; if (difference < -2) return "📉 Declining"; return "➡️ Stable"; }

/**

Calculate comprehensive performance score */ function calculatePerformanceScore(completionRate, assignments, clientCount) { var score = 0;

// Base score from completion rate (0-70 points) score += completionRate * 0.7;

// Volume bonus (0-20 points) score += Math.min(assignments * 0.5, 20);

// Client diversity bonus (0-10 points) score += Math.min(clientCount * 2, 10);

return Math.round(score); }

/**

Get letter grade based on completion rate */ function getLetterGrade(rate) { if (rate >= 97) return "A+"; if (rate >= 93) return "A"; if (rate >= 90) return "A-"; if (rate >= 87) return "B+"; if (rate >= 83) return "B"; if (rate >= 80) return "B-"; if (rate >= 77) return "C+"; if (rate >= 73) return "C"; if (rate >= 70) return "C-"; if (rate >= 67) return "D+"; if (rate >= 65) return "D"; return "F"; }

Name		Name	Last commit message	Last commit date
README.md		README.md

Navigation Menu

Repository navigation

About

Repository files navigation

Weekly Editor Assignment Summary for ${weekRangeStr}

About

Releases

Packages

Contributors

Latest commit

History

Folders and files

Repository files navigation

Weekly Editor Assignment Summary for ${weekRangeStr}

About

Resources

Stars

Watchers

Forks

Releases

Packages

Contributors