Untitled

/**
 * Adds a custom menu when the spreadsheet is opened.
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Scripts')
    .addItem('Update BREAKAID', 'updateBreakAidFromNewReport')
    .addToUi();
}

/**
 * Retrieves the newest file in the designated folder based on its last updated date.
 *
 * @param {string} folderId - The ID of the folder containing the reports.
 * @return {File} The newest file found, or null if none.
 */
function getNewestBreakAidFile(folderId) {
  var folder = DriveApp.getFolderById(folderId);
  var files = folder.getFiles();
  var newestFile = null;
  var newestDate = 0;
  while (files.hasNext()){
    var file = files.next();
    // Use getLastUpdated() instead of getDateCreated()
    var updatedTime = file.getLastUpdated().getTime();
    if (updatedTime > newestDate) {
      newestDate = updatedTime;
      newestFile = file;
    }
  }
  return newestFile;
}

/**
 * Updates the BREAKAID master sheet from the newest Break Aid Excel report.
 *
 * Assumptions:
 *   - The new report is in an Excel file stored in a specific folder.
 *   - The report (converted to Google Sheet) has data in "Sheet1".
 *   - Data is located in rows 19 to 100:
 *       Column B: Job Title
 *       Column C: Name
 *       Column E: Start Time
 *   - The BREAKAID master sheet has a header row (row 1) with columns:
 *       "Job Title", "Name", and "Mon" (for Monday start time).
 *
 * For existing employees (matched by Name), the script updates their Job Title
 * and Monday start time. New employees are appended to the bottom.
 */
function updateBreakAidFromNewReport() {
  // Get the active spreadsheet and the BREAKAID master sheet.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = ss.getSheetByName("BREAKAID");
  if (!masterSheet) {
    Logger.log("BREAKAID sheet not found.");
    return;
  }
  
  // Read all data from the master sheet.
  var masterData = masterSheet.getDataRange().getValues();
  if (masterData.length < 2) {
    Logger.log("Not enough data in BREAKAID sheet.");
    return;
  }
  var masterHeaders = masterData[0];
  
  // Determine column indices in the master sheet.
  var masterJobTitleIdx = masterHeaders.indexOf("Job Title");
  var masterNameIdx = masterHeaders.indexOf("Name");
  var masterMonIdx = masterHeaders.indexOf("Mon");
  
  if (masterJobTitleIdx === -1 || masterNameIdx === -1 || masterMonIdx === -1) {
    Logger.log("BREAKAID sheet must have 'Job Title', 'Name', and 'Mon' headers.");
    return;
  }
  
  // Set your designated folder ID where new Break Aid reports are stored.
  var folderId = "YOUR_FOLDER_ID"; // <-- Replace with your actual folder ID.
  var newestFile = getNewestBreakAidFile(folderId);
  if (!newestFile) {
    Logger.log("No files found in the folder.");
    return;
  }
  
  // Convert the newest Excel file into a Google Sheet.
  var blob = newestFile.getBlob();
  var resource = {
    name: newestFile.getName(),
    mimeType: MimeType.GOOGLE_SHEETS
  };
  // Note: Ensure the Advanced Drive Service is enabled.
  var convertedFile = Drive.Files.create(resource, blob, {convert: true});
  var importedSpreadsheet = SpreadsheetApp.openById(convertedFile.id);
  
  // Assume the report data is in "Sheet1".
  var sourceSheet = importedSpreadsheet.getSheetByName("Sheet1");
  if (!sourceSheet) {
    Logger.log("Sheet1 not found in the imported report.");
    return;
  }
  
  // Get data from rows 19 to 100; columns: B (Job Title), C (Name), E (Start Time).
  var sourceRange = sourceSheet.getRange("B19:E100");
  var sourceData = sourceRange.getValues();
  
  // Build an object from the new data keyed by employee name.
  // Format: { "Employee Name": { jobTitle: ..., startTime: ... } }
  var newData = {};
  for (var i = 0; i < sourceData.length; i++) {
    var row = sourceData[i];
    var jobTitle = row[0];   // Column B
    var empName = row[1];    // Column C
    var startTime = row[3];  // Column E
    if (empName && empName.toString().trim() !== "") {
      newData[empName.toString().trim()] = {
        jobTitle: jobTitle,
        startTime: startTime
      };
    }
  }
  
  // Clear the existing Monday start time data (from row 2 onward) in the master sheet.
  var lastRow = masterSheet.getLastRow();
  if (lastRow > 1) {
    masterSheet.getRange(2, masterMonIdx + 1, lastRow - 1, 1).clearContent();
  }
  
  // Loop through the master sheet rows (starting at row 2) and update matching employees.
  for (var i = 1; i < masterData.length; i++){
    var row = masterData[i];
    var empName = row[masterNameIdx];
    if (empName && newData[empName.toString().trim()]) {
      var info = newData[empName.toString().trim()];
      // Update the Job Title if provided.
      if (info.jobTitle && info.jobTitle.toString().trim() !== "") {
        masterSheet.getRange(i + 1, masterJobTitleIdx + 1).setValue(info.jobTitle);
      }
      // Update the Monday start time.
      masterSheet.getRange(i + 1, masterMonIdx + 1).setValue(info.startTime);
      // Remove this employee from newData.
      delete newData[empName.toString().trim()];
    }
  }
  
  // Append any new employees (those remaining in newData) to the bottom of the master sheet.
  var newRows = [];
  for (var emp in newData) {
    var info = newData[emp];
    var numCols = masterHeaders.length;
    var newRow = [];
    for (var j = 0; j < numCols; j++){
       newRow.push("");
    }
    newRow[masterJobTitleIdx] = info.jobTitle;
    newRow[masterNameIdx] = emp;
    newRow[masterMonIdx] = info.startTime;
    newRows.push(newRow);
  }
  
  if (newRows.length > 0) {
    masterSheet.getRange(masterSheet.getLastRow() + 1, 1, newRows.length, newRows[0].length)
               .setValues(newRows);
  }
  
  Logger.log("BREAKAID master sheet updated successfully.");
  
  // Optionally, you can trash the temporary converted file:
  // DriveApp.getFileById(convertedFile.id).setTrashed(true);
}