By the way, with some vibe coding and code review, I made a small Apps Script for editing category and tag names while keeping transaction dropdown data in sync / valid. This is extremely useful for myself and I think others might benefit from it. For renaming category however there’s already a community tool so I’d recommend you use that first. If you’re like me you want it for tag too, then continue to the following.
What the script does and assumes:
- A tab (i.e., sheet) named “Transactions“
- Contains header of “Tags“ and “Category“. Which column doesn’t matter - it just have to exist (but should only have exactly one column named as Tags and Category).
- A tab named “Tags“ AND a tab named “Categories“. For both, the script uses A2:A (entire column A, less the 1st row because that’s header) as a list for source of dropdown options.
- In sheet “Transactions“, you have data validation set up for Category and Tags column. Both configured as
Criteria = Dropdown (from a range) . It’s up to you to “Allow multiple selections” or not. The script supports either way. If multiple selection, do make sure not to have leading or trailing spaces.
DO NOT include comma in your tag or category name, for obvious reason.
Then there you go, each time you finish editing your list in sheet Tags and Categories (e.g. when you finish editing a cell and hit enter), wait for a few seconds and you shall see a toast notification at the right bottom notifying you the operation succeed:
In google sheet menu bar, select “Extensions > Apps Script”, then paste the following in Code.gs file. Click save (no need to deploy or anything). Now navigate back to your sheet, go to Tags, try edit a tag (or the Categories sheet). You should see the toast, and then go check your Transaction sheet to see the results.
I recommend test with a new tag/category and small scope of data so that it doesn’t get mess up with your data in case some error happen.
/**
* Helper function to find a column index by its header name.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet The sheet object to search.
* @param {string} headerName The text of the header to find.
* @param {number} headerRow The row number where headers are located (e.g., 1).
* @returns {number} The 1-based column index, or -1 if not found.
*/
function getColumnByHeaderName(sheet, headerName, headerRow) {
const lastColumn = sheet.getLastColumn();
// Get all header values in the specified row (e.g., A1:R1)
const headers = sheet.getRange(headerRow, 1, 1, lastColumn).getValues()[0];
// Find the index of the header name (+1 because arrays are 0-indexed, but Sheets columns are 1-based)
for (let i = 0; i < headers.length; i++) {
// Note: toString().trim() is used for robust comparison against the header string
if (headers[i].toString().trim() === headerName.trim()) {
return i + 1; // Return the 1-based column index
}
}
return -1;
}
/**
* Automatically updates all transaction tags/categories when a master list item is renamed.
* This function is triggered automatically on any manual cell edit.
*
* @param {GoogleAppsScript.Events.SheetsOnEdit} e The event object.
*/
function onEdit(e) {
// --- CONFIGURATION ---
// Maps the master list sheet name to the target column header in the transaction sheet.
const SOURCE_TO_TARGET_MAP = {
// If master sheet 'Tags' is edited, update transaction column 'Tags'
'Tags': 'Tags',
// If master sheet 'Categories' is edited, update transaction column 'Category'
'Categories': 'Category'
};
// Column where the master list items (Tags/Categories) are located in their respective sheets
const MASTER_LIST_COLUMN_INDEX = 1;
// Name of the sheet with your transaction data (where the 'Tags' and 'Category' columns are)
const TRANSACTION_SHEET_NAME = 'Transactions';
// Row number where the headers are located in the transaction sheet
const HEADER_ROW = 1;
// Set to true if tags/categories should be matched with case-sensitivity
const IS_CASE_SENSITIVE = false;
// ---------------------
const range = e.range;
const sourceSheet = range.getSheet();
const sourceSheetName = sourceSheet.getName();
// 1. Check if the edited sheet is one of the configured master lists
const targetHeader = SOURCE_TO_TARGET_MAP[sourceSheetName];
if (!targetHeader) {
return;
}
// 2. Check if the edited column is the master list column and it is not the header row
if (range.getColumn() === MASTER_LIST_COLUMN_INDEX && range.getRow() > HEADER_ROW) {
// The event object 'e' automatically provides the old and new value. Trim them.
const newName = e.value.toString().trim();
const oldName = e.oldValue.toString().trim();
// A check to ensure both old and new values are present and different
if (!oldName || !newName || oldName === newName) {
return;
}
if (oldName.includes(',') || newName.includes(',')) {
e.source.toast(`🚨 Error: item contains illegal comma, please remove them in your ${sourceSheetName}`, true, 5);
return;
}
// Get the transaction sheet
const transactionSheet = e.source.getSheetByName(TRANSACTION_SHEET_NAME);
if (!transactionSheet) {
e.source.toast(`🚨 Error: Transaction sheet not found: ${TRANSACTION_SHEET_NAME}`, true, 5);
return;
}
// DYNAMICALLY FIND THE TARGET COLUMN INDEX in the transaction sheet
const targetColIndex = getColumnByHeaderName(
transactionSheet,
targetHeader,
HEADER_ROW
);
if (targetColIndex === -1) {
e.source.toast(`🚨 Error: Column header '${targetHeader}' not found in sheet '${TRANSACTION_SHEET_NAME}'.`, true, 5);
return;
}
// Determine the range to apply the updates
const lastRow = transactionSheet.getLastRow();
const dataStartRow = HEADER_ROW + 1;
if (lastRow < dataStartRow) {
e.source.toast("No transaction data found to update.", true, 5);
return;
}
const numRows = lastRow - HEADER_ROW;
const transactionRange = transactionSheet.getRange(dataStartRow, targetColIndex, numRows, 1);
// Get all tag/category values as a 2D array
const values = transactionRange.getValues();
// 3. Process the array to update names while preserving comma-separated structure
const updatedValues = values.map(row => {
const cellValue = row[0] ? row[0].toString().trim() : '';
if (!cellValue) {
return ['']; // Return empty for empty cells
}
// Split the string into an array of individual items, handling various space/comma separators
const itemsArray = cellValue.split(/\s*,\s*/).map(item => item.trim());
// Map over the items and replace the old item with the new one if it's an exact match
const newItemsArray = itemsArray.map(item => {
// Determine comparison values based on IS_CASE_SENSITIVE setting
const compareItem = IS_CASE_SENSITIVE ? item : item.toLowerCase();
const compareOldName = IS_CASE_SENSITIVE ? oldName : oldName.toLowerCase();
if (compareItem === compareOldName) {
return newName;
}
return item;
});
// Join the items back into a comma-separated string
// Filter out any empty strings and enforce a clean list format
const newCellValue = newItemsArray
.filter(item => item)
.join(', ');
return [newCellValue];
});
// Write the updated values back to the sheet
transactionRange.setValues(updatedValues);
e.source.toast(`✨ ${sourceSheetName} Renamed: '${oldName}' updated to '${newName}' across all transactions in column '${targetHeader}'.`, true, 10);
}
}
