GoogleSheet(final)

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Update Master Summary', 'updateMasterSummaryBasedOnConfiguration')
      .addItem('Update Configuration', 'updateConfiguration')
      .addItem('Update Consolidated Master', 'updateConsolidatedMaster')
      .addItem('Update Consolidated Rejected', 'updateConsolidatedRejected')
      .addItem('Synchronize Column with Template Dev Sheet', 'synchronizeHeadersWithTemplate')
      .addItem('Synchronize Column Name with Template Dev Sheet', 'synchronizeHeaderNamesWithTemplate') 

      .addToUi();
}

function updateMasterSummaryBasedOnConfiguration() {
  var statusNames = ["RFQ SENT", "PART NUMBER SET UP", "SOURCED", "DEVELOPING", "AWAITING SAMPLE", "SAMPLE RECEIVED", "PIES COLLECTION", "PIES APPROVED", "PIES REJECTED", "PM APPROVED", "PRICING", "COMPLETE", "TERMINATED"];

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName("Master Summary");
  var configSheet = spreadsheet.getSheetByName("Configuration");

  // Clear existing content in Master Summary sheet excluding the first column
  var rangeToClear = masterSheet.getRange("B:ZZ");
  rangeToClear.clear();

  // Get tab names and their statuses from the Configuration sheet
  var rangeData = configSheet.getRange("A:B").getValues();
  var tabNames = [];
  var tabStatuses = [];

  // Populate tabNames and tabStatuses arrays
  for (var i = 0; i < rangeData.length; i++) {
    var tabName = rangeData[i][0];
    var status = rangeData[i][1];
    if (tabName && status) { // Ensure both tab name and status exist
      tabNames.push(tabName);
      tabStatuses.push(status.toLowerCase()); // Convert status to lowercase for consistency
    }
  }

  // Set the headers for active tabs and count status for each tab
  var activeTabs = tabNames.filter(function(_, index) {
    return tabStatuses[index] === "active";
  });

  // Set the headers for active tabs in Master Summary
  var headerRowData = ['Status', 'Total Parts Count'].concat(activeTabs);
  masterSheet.getRange(1, 1, 1, headerRowData.length).setValues([headerRowData]);

  // Create a 2D array to hold all the data to be written to the Master Summary sheet
  var outputData = statusNames.map(function(statusName) {
    return [statusName, 0].concat(new Array(activeTabs.length).fill(0));
  });

  // Add a row for the total counts
  var totalCountsRow = ['TotTotal Parts Count', 0].concat(new Array(activeTabs.length).fill(0));
  outputData.push(totalCountsRow);

  // Iterate over active tabs and count the statuses
  activeTabs.forEach(function(tabName, tabIndex) {
    var sheet = spreadsheet.getSheetByName(tabName);
    if (sheet) {
      var values = sheet.getRange("A:A").getValues().flat();
      var statusCounts = statusNames.reduce(function(counts, status) {
        counts[status] = 0;
        return counts;
      }, {});

      // Count the statuses
      values.forEach(function(value) {
        var upperValue = value.toString().toUpperCase();
        if (statusCounts.hasOwnProperty(upperValue)) {
          statusCounts[upperValue]++;
        }
      });

      // Fill the outputData array with counts
      statusNames.forEach(function(statusName, statusIndex) {
        var count = statusCounts[statusName] || 0;
        outputData[statusIndex][tabIndex + 2] = count; // Insert count into corresponding column
        outputData[statusIndex][1] += count; // Add count to the total column
        totalCountsRow[tabIndex + 2] += count; // Add count to the total row
      });
      totalCountsRow[1] += totalCountsRow[tabIndex + 2]; // Add total of current tab to the grand total
    }
  });

  // Write the collected data to the sheet in one operation
  masterSheet.getRange(2, 1, outputData.length, outputData[0].length).setValues(outputData);
}

function updateConfiguration() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var configSheet = spreadsheet.getSheetByName("Configuration");

  // Fetch existing sheet names from Configuration sheet
  var existingSheetNames = configSheet.getRange("A2:A").getValues().flat().filter(function(name) {
    return name; // Filter out empty values
  });

  // Fetch all sheet names excluding "Configuration" and "Master Summary"
  var allSheetNames = spreadsheet.getSheets().map(function(sheet) {
    return sheet.getName();
  }).filter(function(name) {
    return name !== "Configuration" && name !== "Master Summary";
  });

  // Filter out existing sheet names from all sheet names
  var newSheetNames = allSheetNames.filter(function(name) {
    return !existingSheetNames.includes(name);
  });

  // Append new sheet names to the Configuration sheet
  if (newSheetNames.length > 0) {
    var startRow = existingSheetNames.length + 2;
    configSheet.getRange(startRow, 1, newSheetNames.length, 1).setValues(newSheetNames.map(function(name) {
      return [name];
    }));

    // Calculate status for new sheet names
    var statusNames = ["RFQ SENT", "PART NUMBER SET UP", "SOURCED", "DEVELOPING", "AWAITING SAMPLE", "SAMPLE RECEIVED", "PIES COLLECTION", "PIES APPROVED", "PIES REJECTED", "PM APPROVED", "PRICING", "COMPLETE", "TERMINATED"];

    for (var k = 0; k < newSheetNames.length; k++) {
      var tabName = newSheetNames[k];
      var isActive = false;

      // Check each status for the current sheet
      for (var i = 0; i < statusNames.length; i++) {
        var status = statusNames[i];
        var count = getCountForStatusInSheet(status, tabName);
        if (count > 0) {
          isActive = true;
          break;
        }
      }

      // Set the status for the current sheet in the Configuration sheet
      var statusCell = configSheet.getRange(startRow + k, 2);
      statusCell.setValue(isActive ? "Active" : "Inactive");
      var statusValidationRule = SpreadsheetApp.newDataValidation()
          .requireValueInList(["Active", "Inactive"], true)
          .build();
      statusCell.setDataValidation(statusValidationRule);
      statusCell.setFontColor(isActive ? "#00FF00" : "#FF0000");
    }
  }
}

function getCountForStatusInSheet(status, sheetName) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  
  // Return 0 if sheet doesn't exist
  if (!sheet) {
    return 0;
  }

  var statusColumn = sheet.getRange("A:A").getValues().flat(); // Assuming statuses are in column A

  // Count occurrences of status
  var count = statusColumn.filter(function(value) {
    return value === status;
  }).length;

  return count;
}

 
function updateConsolidatedMaster() {
  var statusNames = ["RFQ SENT", "PART NUMBER SET UP", "SOURCED", "DEVELOPING", "AWAITING SAMPLE", "SAMPLE RECEIVED", "PIES COLLECTION", "PIES APPROVED", "PIES REJECTED", "PM APPROVED", "PRICING", "COMPLETE"];
  var columnsToCopy = ["Status", "Start Date", "Part Type", "HOL P/N", "OE#", "ALT OE", "MAM Status (change to Dev)", "FP Status (Change to Electronically Announced)", "PartCat Status (Changed to Electronically Announced)", "Interchange", "Interchange Completion", "Parts List/RFQ Submitted to Warren", "Parts List/RFQ Returned to Holstein", "Production Sourced Part is requested from Warren", "ETA of Sample", "Date Prod Sample Delivered to Holstein", "Factory Code"];

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  try {
    var masterSheet = spreadsheet.getSheetByName("Consolidated Master");
    var configSheet = spreadsheet.getSheetByName("Configuration");

    // Clear existing content in Consolidated Master sheet
    masterSheet.clear();

    // Get active tab names and their statuses from the Configuration sheet
    var rangeData = configSheet.getRange("A:B").getValues();
    var activeTabs = rangeData.filter(function(row) {
      return row[1] && row[1].toLowerCase() === "active";
    }).map(function(row) {
      return row[0];
    });

    // Initialize variables
    var allData = [];
    var rowIndex = 2;

    // Insert headers
    allData.push(columnsToCopy.concat("MOQ")); // Add MOQ to the header row
    masterSheet.getRange(1, 1, 1, allData[0].length).setValues([allData[0]]);

    // Iterate through active tabs
    activeTabs.forEach(function(tabName) {
      var sheet = spreadsheet.getSheetByName(tabName);
      if (sheet) {
        var sheetData = sheet.getDataRange().getValues();

        // Get column names
        var columnNames = sheetData[1];

        // Iterate through rows (excluding header rows)
        sheetData.slice(2).forEach(function(row) {
          var status = row[0];

          // Check if status is in the list and copy relevant data
          if (statusNames.includes(status)) {
            var rowData = [status];
            columnsToCopy.forEach(function(col) {
              var colIndex = columnNames.indexOf(col);
              if (colIndex !== -1) {
                rowData.push(row[colIndex]);
              }
            });

            // Find MOQ column index and convert date to "1" if found
            var moqIndex = columnNames.indexOf("MOQ");
            if (moqIndex !== -1) {
              var moqValue = row[moqIndex];
              if (typeof moqValue === 'object' && moqValue instanceof Date) {
                rowData.push("1");
              } else {
                rowData.push(moqValue);
              }
            } else {
              rowData.push(""); // Add empty string if MOQ column not found
            }
            allData.push(rowData);
          }
        });
      }
    });

    // Insert all data at once
    if (allData.length > 1) { // Check if there's data to insert
      masterSheet.getRange(rowIndex, 1, allData.length - 1, allData[0].length).setValues(allData.slice(1));
    }
  } catch (error) {
    // Handle errors gracefully (e.g., log error or display message to user)
    console.error("Error occurred:", error);
  }
}


function updateConsolidatedRejected() {
  var statusNames = ["PIES REJECTED"];
  var columnsToCopy = ["STATUS", "Part Type", "HOL P/N", "OE#", "QC Inspection/PIES Collection", "HOL Feedback Sent", "New Sample Requested", "New Sample Received"];
 
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var masterSheet = spreadsheet.getSheetByName("Consolidated Rejected");
  var configSheet = spreadsheet.getSheetByName("Configuration");
 
  // Clear existing content in Consolidated Rejected sheet
  masterSheet.clear();
 
  // Get tab names and their statuses from the Configuration sheet
  var rangeData = configSheet.getRange("A:B").getValues();
  var activeTabs = rangeData.filter(function(row) {
    return row[1] && row[1].toLowerCase() === "active";
  }).map(function(row) {
    return row[0];
  });
 
  // Initialize a variable to keep track of the row to insert data into
  var rowIndex = 2;
 
  // Insert headers for the Consolidated Rejected sheet
  var headers = columnsToCopy;
  masterSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
 
  // Iterate through each active tab
  activeTabs.forEach(function(tabName) {
    var sheet = spreadsheet.getSheetByName(tabName);
    if (sheet) {
      var sheetData = sheet.getDataRange().getValues();
 
      // Get the column names from the second row
      var columnNames = sheetData[1];
 
      // Iterate through each row in the sheet
      sheetData.forEach(function(row, rowIdx) {
        if (rowIdx === 0 || rowIdx === 1) return; // Skip the header rows
 
        var status = row[0]; // Assuming status is in the first column (A)
 
        // If the status is "TERMINATED" or "PIES REJECTED", add it to the Consolidated Rejected sheet
        if (statusNames.includes(status)) {
          var rowData = [];
 
          // Insert the data into the Consolidated Rejected sheet
          columnsToCopy.forEach(function(col) {
            var colIndexInSheet = columnNames.indexOf(col);
            if (colIndexInSheet !== -1) {
              rowData.push(row[colIndexInSheet]);
            } else if (col === "STATUS") {
              rowData.push(status); // Add status directly for the STATUS column
            } else {
              rowData.push(''); // Fill in with an empty string if the column is not found
            }
          });
 
          masterSheet.getRange(rowIndex, 1, 1, rowData.length).setValues([rowData]);
          rowIndex++;
        }
      });
    }
  });
}

function synchronizeHeadersWithTemplate() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = spreadsheet.getSheetByName("Template Development Sheet");
  var configSheet = spreadsheet.getSheetByName("Configuration");

  // Error handling for missing sheets
  if (!templateSheet) {
    SpreadsheetApp.getUi().alert('Error: "Template Development" sheet not found.');
    return;
  }
  if (!configSheet) {
    SpreadsheetApp.getUi().alert('Error: "Configuration" sheet not found.');
    return;
  }

  // Get the first two header rows from the Template Development sheet
  var templateHeaders = templateSheet.getRange(1, 1, 2, templateSheet.getLastColumn()).getValues();
  
  // Get active tab names from the Configuration sheet
  var rangeData = configSheet.getRange("A:B").getValues();
  var activeTabs = rangeData.filter(function(row) {
    return row[1] && row[1].toLowerCase() === "active";
  }).map(function(row) {
    return row[0];
  });

  // Iterate through each active tab and synchronize headers
  activeTabs.forEach(function(tabName) {
    var sheet = spreadsheet.getSheetByName(tabName);
    if (sheet) {
      // Get the current headers of the active tab
      var currentHeaders = sheet.getRange(1, 1, 2, sheet.getLastColumn()).getValues();

      // Find new columns that are in the template but not in the current sheet
      var newColumns = [];
      var existingColumns = [];
      templateHeaders[1].forEach(function(header, index) {
        if (header) {
          var currentHeaderIndex = currentHeaders[1].indexOf(header);
          if (currentHeaderIndex === -1) {
            newColumns.push({
              header: header,
              templateColumnIndex: index
            });
          } else {
            existingColumns.push({
              currentHeaderIndex: currentHeaderIndex,
              templateColumnIndex: index
            });
          }
        }
      });

      // If there are new columns, add them to the current sheet
      if (newColumns.length > 0) {
        newColumns.forEach(function(column) {
          var columnIndex = column.templateColumnIndex + 1; // Adjust for 1-based index in Sheets
          
          // Insert a new column at the correct position
          sheet.insertColumnBefore(columnIndex);

          // Set the new header values
          sheet.getRange(1, columnIndex, 2, 1).setValues([
            [templateHeaders[0][column.templateColumnIndex]],
            [column.header]
          ]);

          // Shift data to include the new column
          var dataRange = sheet.getRange(3, columnIndex, sheet.getLastRow() - 2, 1);
          var dataValues = dataRange.getValues();
          for (var row = 0; row < dataValues.length; row++) {
            dataValues[row][0] = ""; // Set the new column values to empty
          }
          dataRange.setValues(dataValues);
        });
      }

      // Find columns that are in the current sheet but not in the template and remove them
      var columnsToDelete = [];
      currentHeaders[1].forEach(function(header, index) {
        if (header && !templateHeaders[1].includes(header)) {
          columnsToDelete.push(index + 1); // Adjust for 1-based index in Sheets
        }
      });

      // If there are columns to delete, remove them from the current sheet
      if (columnsToDelete.length > 0) {
        // Sort columns to delete in descending order to avoid shifting issues
        columnsToDelete.sort(function(a, b) { return b - a; });

        columnsToDelete.forEach(function(columnIndex) {
          sheet.deleteColumn(columnIndex);
        });
      }
    }
  });
}



function synchronizeHeaderNamesWithTemplate() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var templateSheet = spreadsheet.getSheetByName("Template Development Sheet");
  var configSheet = spreadsheet.getSheetByName("Configuration");

  // Error handling for missing sheets
  if (!templateSheet) {
    SpreadsheetApp.getUi().alert('Error: "Template Development" sheet not found.');
    return;
  }
  if (!configSheet) {
    SpreadsheetApp.getUi().alert('Error: "Configuration" sheet not found.');
    return;
  }

  // Get the header rows from the Template Development sheet
  var templateHeaders = templateSheet.getRange(1, 1, 2, templateSheet.getLastColumn()).getValues();

  // Get active tab names from the Configuration sheet
  var rangeData = configSheet.getRange("A:B").getValues();
  var activeTabs = rangeData.filter(function(row) {
    return row[1] && row[1].toLowerCase() === "active";
  }).map(function(row) {
    return row[0];
  });

  // Iterate through each active tab and synchronize header names
  activeTabs.forEach(function(tabName) {
    var sheet = spreadsheet.getSheetByName(tabName);
    if (sheet) {
      // Get the current headers of the active tab
      var currentHeaders = sheet.getRange(1, 1, 2, sheet.getLastColumn()).getValues();

      // Iterate through each header in the template sheet
      templateHeaders.forEach(function(templateRow, rowIndex) {
        templateRow.forEach(function(templateHeader, columnIndex) {
          var currentHeader = currentHeaders[rowIndex][columnIndex];
          // If the header name in the template sheet is different from the header name in the active tab
          if (templateHeader !== currentHeader) {
            // Update the header name in the active tab
            sheet.getRange(rowIndex + 1, columnIndex + 1).setValue(templateHeader);
          }
        });
      });
    }
  });
}