Let’s refine the logic to ensure it matches the amount as closely as possible. Here’s how we’ll adjust the script:
1. Accurate Selection: The script will prioritize matching the total amount exactly or as close as possible, without exceeding the target.
2. Adjustments Only at the End: If the exact match isn’t possible, the last selected item’s price will be adjusted downward slightly to bridge the gap.
3. Feedback: The script will display the total before and after adjustments.
Here’s the improved Google Apps Script:
Refined Google Apps Script Code
function selectItemsToMatchTarget() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const ui = SpreadsheetApp.getUi();
// Prompt for target amount and category
const targetTotal = parseFloat(ui.prompt("Enter the target total amount:").getResponseText());
const sheetName = ui.prompt("Enter the category (sheet name):").getResponseText();
// Get the specified sheet
const sheet = spreadsheet.getSheetByName(sheetName);
if (!sheet) {
ui.alert(`Sheet '${sheetName}' not found.`);
return;
}
// Get data from the sheet
const data = sheet.getDataRange().getValues();
// Extract headers and rows
const headers = data[0];
const rows = data.slice(1);
// Find relevant column indices
const descriptionCol = headers.indexOf("Désignations");
const totalPriceCol = headers.indexOf("PRIX T H.T");
if (descriptionCol === -1 || totalPriceCol === -1) {
ui.alert("Required columns not found: 'Désignations' or 'PRIX T H.T'. Please check the sheet.");
return;
}
// Convert Total Price column to numbers
rows.forEach(row => row[totalPriceCol] = parseFloat(row[totalPriceCol]));
// Sort rows by Total Price in descending order
rows.sort((a, b) => b[totalPriceCol] - a[totalPriceCol]);
let selectedItems = [];
let currentTotal = 0;
// Select items to match or get close to the target total
for (let i = 0; i < rows.length; i++) {
const totalPrice = rows[i][totalPriceCol];
if (currentTotal + totalPrice <= targetTotal) {
selectedItems.push(rows[i]);
currentTotal += totalPrice;
}
if (currentTotal >= targetTotal) break;
}
// Adjust prices if needed
const adjustmentNeeded = targetTotal - currentTotal;
if (adjustmentNeeded > 0 && selectedItems.length > 0) {
const lastItem = selectedItems[selectedItems.length - 1];
lastItem[totalPriceCol] -= adjustmentNeeded; // Adjust the last item's price
currentTotal += adjustmentNeeded; // Update total to match target
}
// Output the results to a new sheet
const resultSheetName = `${sheetName} Selection`;
let resultSheet = spreadsheet.getSheetByName(resultSheetName);
if (!resultSheet) {
resultSheet = spreadsheet.insertSheet(resultSheetName);
} else {
resultSheet.clear(); // Clear existing data
}
// Write headers and selected items to the new sheet
resultSheet.appendRow(headers);
selectedItems.forEach(item => resultSheet.appendRow(item));
// Add total row
resultSheet.appendRow(["", "", "", "Total", "", currentTotal]);
ui.alert(`Selection completed! Final total: ${currentTotal}. Check the sheet '${resultSheetName}' for details.`);
}
Improvements in Logic
1. Descending Order Selection: Prioritizes expensive items first for optimal total alignment.
2. Accurate Adjustment: Adjusts only the last item to ensure the closest match to the target.
3. Error Handling: Alerts you if required columns are missing or if the sheet isn’t found.
4. Feedback: Confirms the final total and whether adjustments were made.
Usage Instructions
1. Copy the script into the Apps Script editor in Google Sheets.
2. Save the script and reload the Google Sheet.
3. Run the script by selecting Extensions > Macros > Select Items to Match Target.
4. When prompted:
• Enter the desired target amount.
• Enter the category/sheet name.
5. The script will create a new sheet with the selected items, adjusted prices (if necessary), and the total.
This ensures the total is as close as possible to your target. Let me know how it performs or if you’d like further tweaks!