Hi, you created the following code for me which work great. I want to iterate on this…
- User
Hi, you created the following code for me which work great. I want to iterate on this version. In the Transactions Worksheet once a transaction has been categorized, you create a new Column called Processed. I like this, rather than put Yes, could you enter the Category name in the appropriate field. i.e. Use one of the following: Revenue Donations House Food Auto Debts E&R Clothes Investments Health care Life & disability Insurance Misc. Here is the code: /**
- Main function to import and categorize transactions. */ function importAndCategorizeTransactions() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var transactionsSheet = ss.getSheetByName("Transactions"); var expensesSheet = ss.getSheetByName("Expenses");
// Validate Sheet Existence if (!transactionsSheet) { SpreadsheetApp.getUi().alert("The 'Transactions' sheet does not exist."); return; }
if (!expensesSheet) { SpreadsheetApp.getUi().alert("The 'Expenses' sheet does not exist."); return; }
// Define Constants var TRANSACTION_START_ROW = 2; // Transactions start at Row 2 var EXPENSES_START_ROW = 6; // Expenses data starts at Row 6 var PROCESSED_COLUMN = 4; // Column D (1-based index)
// Check if 'Processed' header exists; if not, add it var processedHeader = transactionsSheet.getRange(1, PROCESSED_COLUMN).getValue(); // Row 1, Column D if (processedHeader.toString().trim().toUpperCase() !== "PROCESSED") { transactionsSheet.getRange(1, PROCESSED_COLUMN).setValue("Processed"); }
// Fetch Transactions Data (Columns A-C and D) var lastTransactionRow = transactionsSheet.getLastRow(); if (lastTransactionRow < TRANSACTION_START_ROW) { SpreadsheetApp.getUi().alert("No transactions found to process."); return; }
// Fetch data from Columns A-C and D (Processed) var transactionDataRange = transactionsSheet.getRange(TRANSACTION_START_ROW, 1, lastTransactionRow - TRANSACTION_START_ROW + 1, PROCESSED_COLUMN); var transactionsData = transactionDataRange.getValues();
// Fetch Expenses Category Headers (Row 4, Columns E onwards) var expensesHeaderRange = expensesSheet.getRange(4, 5, 1, expensesSheet.getLastColumn() - 4); // Row 4, Columns E onwards var expensesHeaders = expensesHeaderRange.getValues()[0]; // First (and only) row
// Create Category to Column Mapping var categoryMap = {}; for (var c = 0; c < expensesHeaders.length; c++) { var category = expensesHeaders[c].toString().trim().toUpperCase(); if (category !== "") { categoryMap[category] = c + 5; // Column E is 5 } }
// Iterate Through Each Transaction for (var i = 0; i < transactionsData.length; i++) { var row = transactionsData[i]; var processed = row[PROCESSED_COLUMN - 1]; // Column D is index 3 (0-based indexing)
// Skip if already processed if (processed && processed.toString().toLowerCase() === "yes") { continue; } // Extract Transaction Details var transactionDateRaw = row[0]; // Column A var transactionDesc = row[1]; // Column B var transactionAmount = row[2]; // Column C // Parse Date to Extract Day var transactionDay = extractDay(transactionDateRaw); if (transactionDay === null) { SpreadsheetApp.getUi().alert("Unable to parse date in 'Transactions' sheet at row " + (TRANSACTION_START_ROW + i) + ". Skipping this transaction."); // Mark as processed to avoid repeated alerts transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Yes"); continue; } // Validate Day if (transactionDay < 1 || transactionDay > 31) { SpreadsheetApp.getUi().alert("Invalid day (" + transactionDay + ") extracted from date in 'Transactions' sheet at row " + (TRANSACTION_START_ROW + i) + ". Skipping this transaction."); // Mark as processed to avoid repeated alerts transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Yes"); continue; } // Prompt User for Category var ui = SpreadsheetApp.getUi(); var availableCategories = Object.keys(categoryMap).join(", "); var promptMsg = "Transaction Details:\n\n" + "Date: " + formatDate(transactionDateRaw) + "\n" + "Description: " + transactionDesc + "\n" + "Amount: $" + parseFloat(transactionAmount).toFixed(2) + "\n\n" + "Enter category for this transaction (e.g., " + availableCategories + "):"; var response = ui.prompt("Categorize Transaction", promptMsg, ui.ButtonSet.OK_CANCEL); // Handle User Response if (response.getSelectedButton() == ui.Button.CANCEL) { SpreadsheetApp.getUi().alert("Transaction categorization canceled. Remaining transactions will not be processed."); return; } var categoryInput = response.getResponseText().trim().toUpperCase(); if (categoryInput === "") { categoryInput = "MISC"; // Default to 'Misc' if no input } // Validate Category if (!categoryMap.hasOwnProperty(categoryInput)) { var availableCategoriesList = Object.keys(categoryMap).join(", "); SpreadsheetApp.getUi().alert("Category '" + categoryInput + "' not found in 'Expenses' sheet.\n\nAvailable categories: " + availableCategoriesList + "\n\nPlease ensure you enter a valid category. This transaction will be skipped."); // Mark as processed to skip this transaction transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Yes"); continue; } var categoryColumn = categoryMap[categoryInput]; // Locate Row in Expenses Sheet Based on Day var expensesLastRow = expensesSheet.getLastRow(); var dayRow = -1; // Assuming Dates (1-31) are in Column A starting from Row 6 var dateColumnRange = expensesSheet.getRange(EXPENSES_START_ROW, 1, expensesLastRow - EXPENSES_START_ROW + 1, 1); var dateValues = dateColumnRange.getValues(); for (var r = 0; r < dateValues.length; r++) { var cellDay = dateValues[r][0]; if (cellDay === transactionDay) { dayRow = EXPENSES_START_ROW + r; break; } } if (dayRow === -1) { SpreadsheetApp.getUi().alert("No matching day (" + transactionDay + ") found in 'Expenses' sheet for transaction at row " + (TRANSACTION_START_ROW + i) + ". Skipping this transaction."); // Mark as processed to skip this transaction transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Yes"); continue; } // Prepare Transaction Entry (Only Amount) var transactionAmountValue = parseFloat(transactionAmount).toFixed(2); // e.g., "150.00" or "-75.50" var numericAmount = parseFloat(transactionAmountValue); // Convert to number: 150.00 or -75.50 // Fetch Existing Cell Value in Expenses Sheet var targetCell = expensesSheet.getRange(dayRow, categoryColumn); var existingValue = targetCell.getValue(); var newTotal = numericAmount; if (typeof existingValue === 'number') { newTotal = existingValue + numericAmount; } else if (typeof existingValue === 'string') { // Attempt to parse all amounts if stored as text entries // Example: "$50.00\n$25.00" var amountLines = existingValue.split('\n'); var sumExisting = 0; for (var j = 0; j < amountLines.length; j++) { // Extract numerical value from each line var amt = parseFloat(amountLines[j].replace(/[^-\d\.]/g, '')); if (!isNaN(amt)) { sumExisting += amt; } } newTotal = sumExisting + numericAmount; } // Else (empty or other types), newTotal remains as numericAmount // Round to two decimal places to avoid floating point issues newTotal = Math.round((newTotal + Number.EPSILON) * 100) / 100; // Set the new total back to the Expenses sheet // Assumes the cell is formatted as Currency or Number targetCell.setValue(newTotal); targetCell.setNumberFormat("$#,##0.00"); // Optional: Ensures currency format // Mark Transaction as Processed transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Yes");}
SpreadsheetApp.getUi().alert("All transactions have been processed and categorized successfully."); }
/**
- Extracts the day from a date input, ignoring the month.
- @param {string|Date} dateInput - The date input which can be a Date object or a string.
- @return {number|null} - Returns the day as a number (1-31) or null if parsing fails. */ function extractDay(dateInput) { if (dateInput instanceof Date) { return dateInput.getDate(); } else if (typeof dateInput === 'string') { // Attempt to parse the day from the string // Assuming formats like "Dec-01", "2023-12-01", "12/01/2023", etc. var parsedDate = new Date(dateInput); if (!isNaN(parsedDate)) { return parsedDate.getDate(); } else { // If parsing fails, attempt to extract the day manually var dayMatch = dateInput.match(/(\d{1,2})$/); if (dayMatch && dayMatch[1]) { var day = parseInt(dayMatch[1], 10); if (!isNaN(day) && day >= 1 && day <= 31) { return day; } } } } return null; // Failed to parse day }
/**
- Formats the date input into a readable string.
- @param {string|Date} dateInput - The date input which can be a Date object or a string.
- @return {string} - Returns the formatted date string. */ function formatDate(dateInput) { if (dateInput instanceof Date) { return Utilities.formatDate(dateInput, Session.getScriptTimeZone(), "EEE MMM dd yyyy"); } else if (typeof dateInput === 'string') { var parsedDate = new Date(dateInput); if (!isNaN(parsedDate)) { return Utilities.formatDate(parsedDate, Session.getScriptTimeZone(), "EEE MMM dd yyyy"); } else { return dateInput; // Return as-is if unable to parse } } return ""; }
/**
- Adds a custom menu to the Google Sheets UI upon opening the spreadsheet. */ function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Transaction Tools') .addItem('Import & Categorize Transactions', 'importAndCategorizeTransactions') .addToUi(); }
- Assistant
Certainly! I’m glad to hear that your existing script is working well. Based on your request, we’ll modify the script so that instead of marking transactions as "Yes" in the
Processedcolumn, it will record the specific category name assigned to each transaction. This approach provides more detailed tracking and makes it easier to review categorized transactions.Here's a step-by-step explanation of the changes, followed by the updated script:
Changes Overview:
-
Processed Column Content:
- Successful Categorization: Record the exact category name (e.g., "Revenue", "Donations").
- Failed Categorization (e.g., parsing errors or invalid categories): Record "Error" to indicate issues during processing.
-
Preserving Original Category Names:
- While the script uses uppercase for internal processing to ensure case-insensitivity, it will preserve and record the original category names as defined in the
Expensessheet.
- While the script uses uppercase for internal processing to ensure case-insensitivity, it will preserve and record the original category names as defined in the
-
Enhanced User Feedback:
- The
Processedcolumn will now provide clear insights into each transaction’s status, whether successfully categorized or encountered issues.
- The
Updated Script:
/** * Main function to import and categorize transactions. */ function importAndCategorizeTransactions() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var transactionsSheet = ss.getSheetByName("Transactions"); var expensesSheet = ss.getSheetByName("Expenses"); // Validate Sheet Existence if (!transactionsSheet) { SpreadsheetApp.getUi().alert("The 'Transactions' sheet does not exist."); return; } if (!expensesSheet) { SpreadsheetApp.getUi().alert("The 'Expenses' sheet does not exist."); return; } // Define Constants var TRANSACTION_START_ROW = 2; // Transactions start at Row 2 var EXPENSES_START_ROW = 6; // Expenses data starts at Row 6 var PROCESSED_COLUMN = 4; // Column D (1-based index) // Check if 'Processed' header exists; if not, add it var processedHeader = transactionsSheet.getRange(1, PROCESSED_COLUMN).getValue(); // Row 1, Column D if (processedHeader.toString().trim().toUpperCase() !== "PROCESSED") { transactionsSheet.getRange(1, PROCESSED_COLUMN).setValue("Processed"); } // Fetch Transactions Data (Columns A-C and D) var lastTransactionRow = transactionsSheet.getLastRow(); if (lastTransactionRow < TRANSACTION_START_ROW) { SpreadsheetApp.getUi().alert("No transactions found to process."); return; } // Fetch data from Columns A-C and D (Processed) var transactionDataRange = transactionsSheet.getRange(TRANSACTION_START_ROW, 1, lastTransactionRow - TRANSACTION_START_ROW + 1, PROCESSED_COLUMN); var transactionsData = transactionDataRange.getValues(); // Fetch Expenses Category Headers (Row 4, Columns E onwards) var expensesHeaderRange = expensesSheet.getRange(4, 5, 1, expensesSheet.getLastColumn() - 4); // Row 4, Columns E onwards var expensesHeaders = expensesHeaderRange.getValues()[0]; // First (and only) row // Create Category to Column Mapping with Original Names var categoryMap = {}; for (var c = 0; c < expensesHeaders.length; c++) { var categoryOriginal = expensesHeaders[c].toString().trim(); var categoryKey = categoryOriginal.toUpperCase(); if (categoryOriginal !== "") { categoryMap[categoryKey] = { column: c + 5, name: categoryOriginal }; } } // Iterate Through Each Transaction for (var i = 0; i < transactionsData.length; i++) { var row = transactionsData[i]; var processed = row[PROCESSED_COLUMN - 1]; // Column D is index 3 (0-based indexing) // Skip if already processed if (processed && processed.toString().trim() !== "") { continue; } // Extract Transaction Details var transactionDateRaw = row[0]; // Column A var transactionDesc = row[1]; // Column B var transactionAmount = row[2]; // Column C // Parse Date to Extract Day var transactionDay = extractDay(transactionDateRaw); if (transactionDay === null) { SpreadsheetApp.getUi().alert("Unable to parse date in 'Transactions' sheet at row " + (TRANSACTION_START_ROW + i) + ". Skipping this transaction."); // Mark as processed with "Error" to avoid repeated alerts transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Error"); continue; } // Validate Day if (transactionDay < 1 || transactionDay > 31) { SpreadsheetApp.getUi().alert("Invalid day (" + transactionDay + ") extracted from date in 'Transactions' sheet at row " + (TRANSACTION_START_ROW + i) + ". Skipping this transaction."); // Mark as processed with "Error" to avoid repeated alerts transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Error"); continue; } // Prompt User for Category var ui = SpreadsheetApp.getUi(); var availableCategories = Object.keys(categoryMap).map(function(key) { return categoryMap[key].name; }).join(", "); var promptMsg = "Transaction Details:\n\n" + "Date: " + formatDate(transactionDateRaw) + "\n" + "Description: " + transactionDesc + "\n" + "Amount: $" + parseFloat(transactionAmount).toFixed(2) + "\n\n" + "Enter category for this transaction (e.g., " + availableCategories + "):"; var response = ui.prompt("Categorize Transaction", promptMsg, ui.ButtonSet.OK_CANCEL); // Handle User Response if (response.getSelectedButton() == ui.Button.CANCEL) { SpreadsheetApp.getUi().alert("Transaction categorization canceled. Remaining transactions will not be processed."); return; } var categoryInputRaw = response.getResponseText().trim(); var categoryInput = categoryInputRaw.toUpperCase(); if (categoryInput === "") { categoryInput = "MISC"; // Default to 'Misc' if no input } // Validate Category if (!categoryMap.hasOwnProperty(categoryInput)) { var availableCategoriesList = Object.keys(categoryMap).map(function(key) { return categoryMap[key].name; }).join(", "); SpreadsheetApp.getUi().alert("Category '" + categoryInputRaw + "' not found in 'Expenses' sheet.\n\nAvailable categories: " + availableCategoriesList + "\n\nPlease ensure you enter a valid category. This transaction will be skipped."); // Mark as processed with "Error" to skip this transaction transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Error"); continue; } var categoryData = categoryMap[categoryInput]; var categoryColumn = categoryData.column; var categoryName = categoryData.name; // Locate Row in Expenses Sheet Based on Day var expensesLastRow = expensesSheet.getLastRow(); var dayRow = -1; // Assuming Dates (1-31) are in Column A starting from Row 6 var dateColumnRange = expensesSheet.getRange(EXPENSES_START_ROW, 1, expensesLastRow - EXPENSES_START_ROW + 1, 1); var dateValues = dateColumnRange.getValues(); for (var r = 0; r < dateValues.length; r++) { var cellDay = dateValues[r][0]; if (cellDay === transactionDay) { dayRow = EXPENSES_START_ROW + r; break; } } if (dayRow === -1) { SpreadsheetApp.getUi().alert("No matching day (" + transactionDay + ") found in 'Expenses' sheet for transaction at row " + (TRANSACTION_START_ROW + i) + ". Skipping this transaction."); // Mark as processed with "Error" to skip this transaction transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Error"); continue; } // Prepare Transaction Entry (Only Amount) var transactionAmountValue = parseFloat(transactionAmount).toFixed(2); // e.g., "150.00" or "-75.50" var numericAmount = parseFloat(transactionAmountValue); // Convert to number: 150.00 or -75.50 // Fetch Existing Cell Value in Expenses Sheet var targetCell = expensesSheet.getRange(dayRow, categoryColumn); var existingValue = targetCell.getValue(); var newTotal = numericAmount; if (typeof existingValue === 'number') { newTotal = existingValue + numericAmount; } else if (typeof existingValue === 'string') { // Attempt to parse all amounts if stored as text entries // Example: "$50.00\n$25.00" var amountLines = existingValue.split('\n'); var sumExisting = 0; for (var j = 0; j < amountLines.length; j++) { // Extract numerical value from each line var amt = parseFloat(amountLines[j].replace(/[^-\d\.]/g, '')); if (!isNaN(amt)) { sumExisting += amt; } } newTotal = sumExisting + numericAmount; } // Else (empty or other types), newTotal remains as numericAmount // Round to two decimal places to avoid floating point issues newTotal = Math.round((newTotal + Number.EPSILON) * 100) / 100; // Set the new total back to the Expenses sheet // Assumes the cell is formatted as Currency or Number targetCell.setValue(newTotal); targetCell.setNumberFormat("$#,##0.00"); // Optional: Ensures currency format // Mark Transaction as Processed with Category Name transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue(categoryName); } SpreadsheetApp.getUi().alert("All transactions have been processed and categorized successfully."); } /** * Extracts the day from a date input, ignoring the month. * @param {string|Date} dateInput - The date input which can be a Date object or a string. * @return {number|null} - Returns the day as a number (1-31) or null if parsing fails. */ function extractDay(dateInput) { if (dateInput instanceof Date) { return dateInput.getDate(); } else if (typeof dateInput === 'string') { // Attempt to parse the day from the string // Assuming formats like "Dec-01", "2023-12-01", "12/01/2023", etc. var parsedDate = new Date(dateInput); if (!isNaN(parsedDate)) { return parsedDate.getDate(); } else { // If parsing fails, attempt to extract the day manually var dayMatch = dateInput.match(/(\d{1,2})$/); if (dayMatch && dayMatch[1]) { var day = parseInt(dayMatch[1], 10); if (!isNaN(day) && day >= 1 && day <= 31) { return day; } } } } return null; // Failed to parse day } /** * Formats the date input into a readable string. * @param {string|Date} dateInput - The date input which can be a Date object or a string. * @return {string} - Returns the formatted date string. */ function formatDate(dateInput) { if (dateInput instanceof Date) { return Utilities.formatDate(dateInput, Session.getScriptTimeZone(), "EEE MMM dd yyyy"); } else if (typeof dateInput === 'string') { var parsedDate = new Date(dateInput); if (!isNaN(parsedDate)) { return Utilities.formatDate(parsedDate, Session.getScriptTimeZone(), "EEE MMM dd yyyy"); } else { return dateInput; // Return as-is if unable to parse } } return ""; } /** * Adds a custom menu to the Google Sheets UI upon opening the spreadsheet. */ function onOpen() { var ui = SpreadsheetApp.getUi(); ui.createMenu('Transaction Tools') .addItem('Import & Categorize Transactions', 'importAndCategorizeTransactions') .addToUi(); }Detailed Explanation of Changes:
-
Mapping Original Category Names:
- Before: The
categoryMapobject mapped uppercase category names to their corresponding column indices. - After: The
categoryMapnow maps uppercase category keys to an object containing both the column index and the original category name. This ensures that when recording the category in theProcessedcolumn, the original name (with proper capitalization and formatting) is used.
// Create Category to Column Mapping with Original Names var categoryMap = {}; for (var c = 0; c < expensesHeaders.length; c++) { var categoryOriginal = expensesHeaders[c].toString().trim(); var categoryKey = categoryOriginal.toUpperCase(); if (categoryOriginal !== "") { categoryMap[categoryKey] = { column: c + 5, name: categoryOriginal }; } } - Before: The
-
Updating the Prompt Message:
- The prompt now displays the original category names, making it clearer for users to input the correct category.
var availableCategories = Object.keys(categoryMap).map(function(key) { return categoryMap[key].name; }).join(", "); -
Processing the
ProcessedColumn:-
Successful Categorization: After successfully categorizing a transaction, the script records the exact category name in the
Processedcolumn.// Mark Transaction as Processed with Category Name transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue(categoryName); -
Failed Categorization (Errors): If there's an error during processing (e.g., date parsing failure or invalid category), the script records "Error" in the
Processedcolumn to indicate that the transaction wasn't successfully categorized.// In error cases transactionsSheet.getRange(TRANSACTION_START_ROW + i, PROCESSED_COLUMN).setValue("Error");
-
-
Skipping Already Processed Transactions:
-
The script now checks if the
Processedcolumn is non-empty to determine if a transaction has already been processed. This ensures that transactions won’t be reprocessed unless theProcessedcolumn is cleared.// Skip if already processed if (processed && processed.toString().trim() !== "") { continue; }
-
-
Handling User Cancellation:
-
If the user cancels the categorization prompt, the script alerts the user and stops processing further transactions.
if (response.getSelectedButton() == ui.Button.CANCEL) { SpreadsheetApp.getUi().alert("Transaction categorization canceled. Remaining transactions will not be processed."); return; }
-
-
Default Category Handling:
-
If the user doesn't input a category, the script defaults to "Misc".
if (categoryInput === "") { categoryInput = "MISC"; // Default to 'Misc' if no input }
-
-
Enhanced User Alerts:
- The script provides specific alerts based on the type of error encountered, aiding in troubleshooting and ensuring data integrity.
Final Notes:
-
Category Names Consistency: Ensure that the category names in the
Expensessheet (Row 4, Columns E onwards) exactly match the ones you intend to use. This consistency ensures smooth categorization and accurate data recording. -
Processed Column Management: With the
Processedcolumn now holding category names or "Error", it becomes a valuable reference for reviewing transaction statuses. If needed, you can further enhance the script to handle retries for transactions marked as "Error" or implement additional logging mechanisms. -
Script Deployment: After updating the script, make sure to save your changes. To apply the updates, you might need to refresh the Google Sheets document to trigger the
onOpenfunction and update the custom menu if necessary.
Feel free to test the updated script with a few transactions to ensure it behaves as expected. If you encounter any issues or need further enhancements, don’t hesitate to ask!
-