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