google sheet

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Update Master Summary', 'updateMasterSummaryBasedOnConfiguration')
      .addItem('Update Configuration', 'updateConfiguration')
      .addItem('Update Consolidated Master', 'updateConsolidatedMaster')
      .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", "MOQ"];

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

  // Clear existing content in Consolidated Master 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 Master 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)
        var statusIndex = statusNames.indexOf(status);

        // If the status is one of the specified statusNames, add it to the Consolidated Master sheet
        if (statusIndex !== -1) {
          var rowData = [status]; // Start with the status in the first column

          // Insert the data into the Consolidated Master sheet
          columnsToCopy.forEach(function(col, colIndex) {
            var colIndexInSheet = columnNames.indexOf(col);
            if (colIndexInSheet !== -1 && rowIdx > 1) {
              rowData.push(row[colIndexInSheet]);
            }
          });

          masterSheet.getRange(rowIndex, 1, 1, rowData.length).setValues([rowData]);
          rowIndex++;
        }
      });
    }
  });
}
function updateConsolidatedRejected() {
  var statusNames = ["TERMINATED", "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 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);
  }
}