Im trying to do  Rotation Overtime for associate goes to the button of the list of their Deparment
Google Sheet App Script but i cant make the rotation after hitting submit

function onOpen() {

  var ui = SpreadsheetApp.getUi();

 

 

  ui.createMenu('Custom Menu')

    .addItem('Run Employee Rotation', 'rotateAssociatesWithinDepartments')

    .addItem('Submit Data', 'onSubmitButtonClick')

    .addItem('Clear Overtime Type', 'onClearOvertimeTypeButtonClick')

    .addToUi();

}

 

// Helper function to get column index by name

function getColumnIndex(sheet, columnName) {

  var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];

  return headers.indexOf(columnName) + 1;

}

 

function rotateAssociatesWithinDepartments() {

  var otSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('OT List');

  var rotationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Team Rotation');

 

  var otData = otSheet.getDataRange().getValues();

  var departmentRanges = {

    'Surface': { startRow: 2, endRow: 11, startCol: 1, endCol: 2 },

    'AR': { startRow: 12, endRow: 19, startCol: 1, endCol: 2 },

    'Finish': { startRow: 20, endRow: 29, startCol: 1, endCol: 2 }

    // Add more departments as needed

  };

 

  for (var i = 1; i < otData.length; i++) {

    var otEmployee = otData[i];

    var departmentName = otEmployee[0];

    var associateName = otEmployee[1];

    var overtimeType = otEmployee[2];

    var date = otEmployee[3];

 

    if (overtimeType === 'Voluntary (Half Shift)') {

      continue;  // Skip processing for Voluntary (Half Shift)

    }

 

    if (!departmentRanges.hasOwnProperty(departmentName)) {

      continue;

    }

 

    var departmentRange = departmentRanges[departmentName];

    var departmentColumnIndex = getColumnIndex(rotationSheet, departmentRange.startCol);

 

    if (departmentColumnIndex > 0) {

      // If it's Mandate or Voluntary (Full Shift), move to the bottom of the list

      if (overtimeType === 'Mandated' || overtimeType === 'Voluntary (Full Shift)') {

        var lastOccupiedRow = departmentRange.endRow;

        rotationSheet.getRange(lastOccupiedRow + 1, departmentColumnIndex).setValue(associateName);

      } else {

        // If it's not Mandated or Voluntary (Full Shift), move within the list

        var departmentValues = rotationSheet.getRange(departmentRange.startRow, departmentColumnIndex, departmentRange.endRow - departmentRange.startRow + 1, 1).getValues();

        departmentValues.unshift(departmentValues.pop());

        rotationSheet.getRange(departmentRange.startRow, departmentColumnIndex, departmentValues.length, 1).setValues(departmentValues);

      }

    }

  }

}

 

function onSubmitButtonClick() {

  // Execute the rotation function

  rotateAssociatesWithinDepartments();

 

  // Log information to help with debugging

  Logger.log('Submitted Data');

  Logger.log('Before Rotation (OT List):');

  printOTList();

 

  // Now, trigger the rotation in the "Team Rotation" sheet

  rotateAssociateNamesInTeamRotation();

 

  // Log information after rotation

  Logger.log('After Rotation (Team Rotation):');

  printTeamRotation();

}

 

function printOTList() {

  var otSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('OT List');

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

  for (var i = 0; i < data.length; i++) {

    Logger.log(data[i].join(', '));

  }

}

 

function printTeamRotation() {

  var rotationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Team Rotation');

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

  for (var i = 0; i < data.length; i++) {

    Logger.log(data[i].join(', '));

  }

}

 

function onClearOvertimeTypeButtonClick() {

  var otSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('OT List');

  var overtimeTypeColumn = otSheet.getRange(2, 3, otSheet.getLastRow() - 1, 1);

  overtimeTypeColumn.clearContent();

  Browser.msgBox('Overtime Type cleared successfully!');

}

 

function rotateAssociateNamesInTeamRotation() {

  var otSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('OT List');

  var rotationSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Team Rotation');

 

  // Get data from "OT List" sheet

  var otData = otSheet.getDataRange().getValues();

 

  // Iterate through each row in "OT List" sheet

  for (var i = 1; i < otData.length; i++) {

    var teamName = otData[i][0];

    var associateName = otData[i][1];

    var overtimeType = otData[i][2];

    var date = otData[i][3];

 

    // Check if the overtime type is "Voluntary (Half Shift)"

    if (overtimeType === 'Voluntary (Half Shift)') {

      continue; // Skip processing for Voluntary (Half Shift)

    }

 

    // Find the team column index in "Team Rotation" sheet

    var teamColumnIndex = getColumnIndex(rotationSheet, teamName);

 

    // Add a check to ensure rotation is only within the same team

    if (teamColumnIndex > 0) {

      var teamRange = rotationSheet.getRange(2, teamColumnIndex, rotationSheet.getLastRow() - 1, 1);

      var teamValues = teamRange.getValues();

 

      // Check if it's Mandated or Voluntary (Full Shift)

      if (overtimeType === 'Mandated' || overtimeType === 'Voluntary (Full Shift)') {

        // Rotate to the bottom of the list

        var lastRowIndex = teamValues.length;

        teamValues[lastRowIndex][0] = associateName;

      } else {

        // Find the last occupied row index in the team column

        var lastRowIndex = teamValues.length - 1;

        while (lastRowIndex >= 0 && teamValues[lastRowIndex][0] === '') {

          lastRowIndex--;

        }

 

        // Add the associate name below the last occupied row

        teamValues.splice(lastRowIndex + 2, 0, [associateName]);

      }

 

      // Write the updated column back to the sheet

      teamRange.setValues(teamValues);

 

      // Log rotation details for better debugging

      Logger.log('Rotated ' + associateName + ' in Team ' + teamName + ' to row ' + (lastRowIndex + 2));

    }

  }

}