Google Sheets Macro

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

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

  // Insert Time Taken column
  spreadsheet.getRange('F:F').activate();
  sheet.insertColumnsBefore(spreadsheet.getActiveRange().getColumn(), 1);
  spreadsheet.getRange('F2').activate();
  spreadsheet.getCurrentCell().setFormula('=IF(AND(D2<>"",E2<>""),E2-D2,"")');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('F2:F' + lastRow), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
  spreadsheet.getRange('F:F').activate();
  spreadsheet.getActiveRangeList().setNumberFormat('[hh]:mm:ss');
  spreadsheet.getRange('F1').activate();
  spreadsheet.getCurrentCell().setValue('Time Taken');

  // 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('$');
  spreadsheet.getRange('B:B').activate();
  spreadsheet.getActiveRangeList().setNumberFormat('[>0]£#,##0.00;[<0]-£#,##0.00;""');
  spreadsheet.getRange('C:C').activate();
  spreadsheet.getActiveRangeList().setNumberFormat('[>0]$#,##0.00;[<0]-$#,##0.00;""');
  spreadsheet.getRange('B1').activate();
  spreadsheet.getCurrentCell().setValue('Reward (£)');
  spreadsheet.getRange('C1').activate();
  spreadsheet.getCurrentCell().setValue('Reward ($)');

  // 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('$');
  spreadsheet.getRange('D:D').activate();
  spreadsheet.getActiveRangeList().setNumberFormat('[>0]£#,##0.00;[<0]-£#,##0.00;""');
  spreadsheet.getRange('E:E').activate();
  spreadsheet.getActiveRangeList().setNumberFormat('[>0]$#,##0.00;[<0]-$#,##0.00;""');
  spreadsheet.getRange('D1').activate();
  spreadsheet.getCurrentCell().setValue('Bonus (£)');
  spreadsheet.getRange('E1').activate();
  spreadsheet.getCurrentCell().setValue('Bonus ($)');

  // Move columns
  spreadsheet.getRange('D:D').activate();
  sheet.moveColumns(spreadsheet.getRange('D:D'), 3);

  // Enhanced auto-resize approach with special handling for date/time columns
  var lastColumn = sheet.getLastColumn();

  // Force load all data
  var allData = sheet.getRange(1, 1, lastRow, lastColumn).getValues();
  var headers = allData[0];

  // Wait for calculations
  Utilities.sleep(1000);

  // Resize columns with specific handling for different column types
  for (var i = 1; i <= lastColumn; i++) {
    // First auto-resize based on all content
    sheet.autoResizeColumn(i);

    // Get header content
    var headerContent = headers[i-1].toString();

    // Define fixed widths for date/time columns
    if (headerContent === 'Started At' || headerContent === 'Completed At') {
      sheet.setColumnWidth(i, 150); // Fixed width for datetime columns
      sheet.getRange(1, i, lastRow, 1).setHorizontalAlignment('left');
    } else if (headerContent === 'Time Taken') {
      sheet.setColumnWidth(i, 100); // Fixed width for time duration column
      sheet.getRange(1, i, lastRow, 1).setHorizontalAlignment('left');
    } else if (headerContent === 'Completion Code') {
      sheet.setColumnWidth(i, 200); // Fixed width for Completion Code column
      sheet.getRange(1, i, lastRow, 1).setHorizontalAlignment('left');
    } else {
      // For other columns, calculate width based on content
      var currentWidth = sheet.getColumnWidth(i);
      var headerWidth = headerContent.length * 8;

      // Get maximum content width in column
      var maxContentWidth = 0;
      for (var row = 0; row < lastRow; row++) {
        var cellContent = allData[row][i-1].toString();
        var contentWidth = cellContent.length * 8;
        maxContentWidth = Math.max(maxContentWidth, contentWidth);
      }

      // Use the larger of header width or content width, plus padding
      var newWidth = Math.max(headerWidth, maxContentWidth, currentWidth) + 20;
      sheet.setColumnWidth(i, newWidth);
    }
  }

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

  // Final force refresh
  sheet.getRange(1, 1, lastRow, lastColumn).activate();
}