Emergency_Medic_109

function Emergency_Medic_109() {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();

  // Performance optimizations
  SpreadsheetApp.flush(); // Clear any pending operations
  sheet.getRange('A1:Z' + sheet.getLastRow()).setNumberFormat('@STRING@'); // Temporarily set as text

  // Disable automatic calculations temporarily
  var properties = PropertiesService.getDocumentProperties();
  properties.setProperty('calculationMode', 'MANUAL');

  // Remove any existing filters first
  var existingFilter = sheet.getFilter();
  if (existingFilter) {
    existingFilter.remove();
  }

  // Get the last row with data
  var lastRow = sheet.getLastRow();

  // === First Macro Operations ===

  // Split Started At into Date and Time
  var startedAtCol = 4;
  sheet.insertColumnsBefore(startedAtCol, 2);
  sheet.getRange(1, startedAtCol).setValue('Start Date');
  sheet.getRange(1, startedAtCol + 1).setValue('Start Time');

  var startedAtRange = sheet.getRange(2, startedAtCol + 2, lastRow - 1);
  var startDateRange = sheet.getRange(2, startedAtCol, lastRow - 1);
  var startTimeRange = sheet.getRange(2, startedAtCol + 1, lastRow - 1);

  // Process date/time formulas in batches
  var batchSize = 100;
  for (var i = 2; i <= lastRow; i += batchSize) {
    var currentBatchSize = Math.min(batchSize, lastRow - i + 1);
    var startDateFormulas = Array(currentBatchSize).fill('=IF(INDIRECT("R[0]C[2]",FALSE)<>"",LEFT(INDIRECT("R[0]C[2]",FALSE),10),"")');
    var startTimeFormulas = Array(currentBatchSize).fill('=IF(INDIRECT("R[0]C[1]",FALSE)<>"",MID(INDIRECT("R[0]C[1]",FALSE),12,8),"")');

    sheet.getRange(i, startedAtCol, currentBatchSize, 1).setFormulas(startDateFormulas.map(f => [f]));
    sheet.getRange(i, startedAtCol + 1, currentBatchSize, 1).setFormulas(startTimeFormulas.map(f => [f]));

    if (i % (batchSize * 5) === 0) {
      SpreadsheetApp.flush();
      Utilities.sleep(50);
    }
  }

  // Split Completed At into Date and Time
  var completedAtCol = 7;
  sheet.insertColumnsBefore(completedAtCol, 2);
  sheet.getRange(1, completedAtCol).setValue('Complete Date');
  sheet.getRange(1, completedAtCol + 1).setValue('Complete Time');

  var completedAtRange = sheet.getRange(2, completedAtCol + 2, lastRow - 1);
  var completeDateRange = sheet.getRange(2, completedAtCol, lastRow - 1);
  var completeTimeRange = sheet.getRange(2, completedAtCol + 1, lastRow - 1);

  // Process completed date/time formulas in batches
  for (var i = 2; i <= lastRow; i += batchSize) {
    var currentBatchSize = Math.min(batchSize, lastRow - i + 1);
    var completeDateFormulas = Array(currentBatchSize).fill('=IF(INDIRECT("R[0]C[2]",FALSE)<>"",LEFT(INDIRECT("R[0]C[2]",FALSE),10),"")');
    var completeTimeFormulas = Array(currentBatchSize).fill('=IF(INDIRECT("R[0]C[1]",FALSE)<>"",MID(INDIRECT("R[0]C[1]",FALSE),12,8),"")');

    sheet.getRange(i, completedAtCol, currentBatchSize, 1).setFormulas(completeDateFormulas.map(f => [f]));
    sheet.getRange(i, completedAtCol + 1, currentBatchSize, 1).setFormulas(completeTimeFormulas.map(f => [f]));

    if (i % (batchSize * 5) === 0) {
      SpreadsheetApp.flush();
      Utilities.sleep(50);
    }
  }

  // Format date and time columns
  sheet.getRange(2, startedAtCol, lastRow - 1).setNumberFormat('yyyy-mm-dd');
  sheet.getRange(2, completedAtCol, lastRow - 1).setNumberFormat('yyyy-mm-dd');
  sheet.getRange(2, startedAtCol + 1, lastRow - 1).setNumberFormat('hh:mm:ss');
  sheet.getRange(2, completedAtCol + 1, lastRow - 1).setNumberFormat('hh:mm:ss');

  // Split Reward column
  spreadsheet.getRange('C:C').activate();
  sheet.insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getRange('B1:B' + lastRow).activate();
  spreadsheet.getRange('B1:B' + lastRow).splitTextToColumns('$');

  // Batch process currency formatting
  var rewardRange = sheet.getRange('B:B');
  var rewardUSDRange = sheet.getRange('C:C');
  rewardRange.setNumberFormat('[>0]£#,##0.00;[<0]-£#,##0.00;""');
  rewardUSDRange.setNumberFormat('[>0]$#,##0.00;[<0]-$#,##0.00;""');
  sheet.getRange('B1').setValue('Reward (£)');
  sheet.getRange('C1').setValue('Reward ($)');

  SpreadsheetApp.flush();
  Utilities.sleep(100);

    // Split Bonus column
  spreadsheet.getRange('E:E').activate();
  sheet.insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getRange('D1:D' + lastRow).activate();
  spreadsheet.getRange('D1:D' + lastRow).splitTextToColumns('$');

  // Batch process bonus currency formatting
  var bonusRange = sheet.getRange('D:D');
  var bonusUSDRange = sheet.getRange('E:E');
  bonusRange.setNumberFormat('[>0]£#,##0.00;[<0]-£#,##0.00;""');
  bonusUSDRange.setNumberFormat('[>0]$#,##0.00;[<0]-$#,##0.00;""');
  sheet.getRange('D1').setValue('Bonus (£)');
  sheet.getRange('E1').setValue('Bonus ($)');

  // Move Bonus (£) after Reward (£)
  sheet.moveColumns(sheet.getRange('D:D'), 3);

  SpreadsheetApp.flush();
  Utilities.sleep(100);

  // === Time Calculations Optimization ===

  // Insert Time Taken column and calculate with multi-day support
  var timeCol = sheet.getLastColumn() + 1;
  sheet.insertColumnBefore(timeCol);
  sheet.getRange(1, timeCol).setValue('Time Taken');

  // Batch process time calculations
  var batchSize = 50; // Smaller batch size for complex calculations
  for (var i = 2; i <= lastRow; i += batchSize) {
    var currentBatchSize = Math.min(batchSize, lastRow - i + 1);

    // Create array of formulas for the current batch
    var formulas = Array(currentBatchSize).fill().map(function(_, index) {
      var row = i + index;
      return ['=IF(AND(G' + row + '<>"",J' + row + '<>""),' +
             'IF(J' + row + '<G' + row + ',J' + row + '-G' + row + '+1,J' + row + '-G' + row + '),' +
             '"")'];
    });

    // Apply formulas to the current batch
    sheet.getRange(i, timeCol, currentBatchSize, 1).setFormulas(formulas);

    // Force partial calculation after each batch
    if (i % (batchSize * 2) === 0) {
      SpreadsheetApp.flush();
      Utilities.sleep(50);
    }
  }

  // Set number format for the entire Time Taken column
  sheet.getRange(2, timeCol, lastRow - 1).setNumberFormat('[hh]:mm:ss');

  SpreadsheetApp.flush();
  Utilities.sleep(100);

      // Enhanced auto-resize approach
  var lastColumn = sheet.getLastColumn();
  var allData = sheet.getRange(1, 1, lastRow, lastColumn).getValues();
  var headers = allData[0];

    // Batch process column resizing
  for (var i = 1; i <= lastColumn; i += 5) {
    var batchEnd = Math.min(i + 4, lastColumn);

    for (var j = i; j <= batchEnd; j++) {
      var headerContent = headers[j-1].toString();

      // Column width settings
      if (headerContent === 'Date') {  // Exact match for Date
        sheet.setColumnWidth(j, 25);
      } else if (headerContent === 'Start Date' || headerContent === 'Complete Date') {
        sheet.setColumnWidth(j, 25);
      } else if (headerContent.includes('Time') || headerContent === 'Started At' || headerContent === 'Completed At') {
        sheet.setColumnWidth(j, 140);
      } else if (headerContent === 'Completion Code') {
        sheet.setColumnWidth(j, 200);
      } else if (headerContent.includes('Reward') || headerContent.includes('Bonus')) {
        sheet.setColumnWidth(j, 90);
      } else if (headerContent === 'Status') {
        sheet.setColumnWidth(j, 130);
      } else if (headerContent === 'Year') {  // Exact match for Year
        sheet.setColumnWidth(j, 25);
      } else if (headerContent === 'Applicable Terms') {
        sheet.setColumnWidth(j, 140);
      } else {
        sheet.autoResizeColumn(j);
        var currentWidth = sheet.getColumnWidth(j);
        sheet.setColumnWidth(j, currentWidth + 20);
      }

      // Debug logging
      Logger.log('Column ' + j + ' (' + headerContent + '): Setting width');

      // Column alignment settings
      if (headerContent === 'Date' || headerContent.includes('Time') ||
          headerContent === 'Started At' || headerContent === 'Completed At') {
        sheet.getRange(1, j, lastRow, 1).setHorizontalAlignment('left');
      } else if (headerContent.includes('Reward') || headerContent.includes('Bonus')) {
        sheet.getRange(1, j, lastRow, 1).setHorizontalAlignment('right');
      } else if (headerContent === 'Status' || headerContent === 'Year') {
        sheet.getRange(1, j, lastRow, 1).setHorizontalAlignment('left');
      } else if (headerContent === 'Study') {
        sheet.getRange(1, j, lastRow, 1).setHorizontalAlignment('left');
        sheet.getRange(1, j, lastRow, 1).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP);
      }
    }

    // Force update after each batch
    SpreadsheetApp.flush();
    Utilities.sleep(50);
  }

    // === Second Macro Operations ===

  // Store current column layout
  headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  var startDateCol = headers.indexOf('Start Date') + 1;
  var completeTimeCol = headers.indexOf('Complete Time') + 1;
  var timeTakenCol = headers.indexOf('Time Taken') + 1;
  var completedAtCol = headers.indexOf('Completed At') + 1;

  // Store Time Taken values
  var timeTakenRange = sheet.getRange(1, timeTakenCol, lastRow);
  var timeTakenValues = timeTakenRange.getValues();

  // Copy Start Date to new column at the end
  var lastCol = sheet.getLastColumn();
  sheet.insertColumnAfter(lastCol);
  var newCol = lastCol + 1;
  sheet.getRange(1, startDateCol, lastRow).copyTo(
    sheet.getRange(1, newCol),
    SpreadsheetApp.CopyPasteType.PASTE_VALUES,
    false
  );

  // Move the new column to position 1 and rename it to "Date"
  sheet.moveColumns(sheet.getRange(1, newCol), 1);
  sheet.getRange('A1').setValue('Date');

  // Move Time Taken column after Completed At
  headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  timeTakenCol = headers.indexOf('Time Taken') + 1;
  completedAtCol = headers.indexOf('Completed At') + 1;
  if (timeTakenCol > 0 && completedAtCol > 0) {
    sheet.moveColumns(sheet.getRange(1, timeTakenCol), completedAtCol + 1);
  }

  // Hide columns in batches
  var columnsToHide = ['Start Date', 'Complete Date', 'Started At', 'Completed At'];
  headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  columnsToHide.forEach(function(colName) {
    var colIndex = headers.indexOf(colName) + 1;
    if (colIndex > 0 && colIndex !== 1) {  // Don't hide the first column
      sheet.hideColumn(sheet.getRange(1, colIndex));
    }
  });

  // Add Year column in position Q (17)
  sheet.insertColumnAfter(16);  // Insert after column P
  sheet.getRange(1, 17).setValue('Year');

  // Batch process Year formula
  var batchSize = 100;
  for (var i = 2; i <= lastRow; i += batchSize) {
    var currentBatchSize = Math.min(batchSize, lastRow - i + 1);
    var yearFormulas = Array(currentBatchSize).fill(['=YEAR(A' + i + ')']);
    sheet.getRange(i, 17, currentBatchSize, 1)
      .setFormulas(yearFormulas)
      .setNumberFormat('0');

    if (i % (batchSize * 5) === 0) {
      SpreadsheetApp.flush();
      Utilities.sleep(50);
    }
  }

  // Re-enable automatic calculations
  properties.deleteProperty('calculationMode');

  // Final flush and cleanup
  SpreadsheetApp.flush();
  Utilities.sleep(200);

  // Remove extra columns - more aggressive approach
  var lastColumn = sheet.getLastColumn();
  var yearColIndex = -1;

  // Find the Year column
  var headers = sheet.getRange(1, 1, 1, lastColumn).getValues()[0];
  for (var i = 0; i < headers.length; i++) {
    if (headers[i] === 'Year') {
      yearColIndex = i + 1;
      break;
    }
  }

  // If Year column found, delete all columns after it
  if (yearColIndex > 0) {
    try {
      // Get the total number of columns in the sheet
      var totalColumns = sheet.getMaxColumns();

      // Calculate how many columns to delete
      var columnsToDelete = totalColumns - yearColIndex;

      // Delete extra columns if they exist
      if (columnsToDelete > 0) {
        sheet.deleteColumns(yearColIndex + 1, columnsToDelete);
      }
    } catch (e) {
      Logger.log('Error deleting columns: ' + e.toString());
    }
  }

  // Format specific columns
  headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  headers.forEach(function(header, index) {
    var colIndex = index + 1;

    switch(header) {
      case 'Date':
        sheet.setColumnWidth(colIndex, 85);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('left');
        break;
      case 'Reward (£)':
      case 'Reward ($)':
      case 'Bonus (£)':
      case 'Bonus ($)':
        sheet.setColumnWidth(colIndex, 90);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('right');
        break;
      case 'Start Time':
      case 'Time Taken':
        sheet.setColumnWidth(colIndex, 95);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('left');
        break;
      case 'Complete Time':
        sheet.setColumnWidth(colIndex, 120);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('left');
        break;
      case 'Completion Code':
        sheet.setColumnWidth(colIndex, 200);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('left');
        break;
      case 'Status':
        sheet.setColumnWidth(colIndex, 130);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('left');
        break;
      case 'Applicable Terms':
        sheet.setColumnWidth(colIndex, 140);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('left');
        break;
      case 'Year':
        sheet.setColumnWidth(colIndex, 60);
        sheet.getRange(1, colIndex, lastRow, 1).setHorizontalAlignment('right');
        break;
    }
  });

  SpreadsheetApp.flush();
  Utilities.sleep(100);

  // === Final Operations ===

  // Create filter and freeze row
  sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).createFilter();
  sheet.setFrozenRows(1);

  // Set final cursor position to A2
  sheet.getRange('A2').activate();

  // Final recalculation
  SpreadsheetApp.flush();
}