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);
}
});
});
}
});
}