I’m Using this code to merge two updates. Col c contains current status of a tenant in each cell its for a different tenant. My team ads in col F the update. At the moment code is running but no issues.
function mergeColumnsCAndF() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Get all data in columns C and F
const data = sheet.getRange(1, 3, sheet.getLastRow(), 2).getValues(); // Column C (Status) and Column F (Updates)
// Define categories
const categories = {
problems: “Problems”,
payments: “Payments”,
tenantCommitments: “Tenant Commitments”,
landlordObligations: “Landlord Obligations”,
ebm: “EBM Codes”
};
// Loop through each row and merge updates into the status
const mergedData = data.map(([status, update], index) => {
if (!update.trim()) return [status]; // No update, keep the original status
let updatedStatus = status || ""; // Start with existing status
let extractedCategories = {}; // Object to log categories and updates for debugging
// Normalize update string: remove extra spaces and standardize formatting
const normalizedUpdate = update.replace(/s+:s+/g, ":").trim();
// Split updates from F into individual categories
const updates = normalizedUpdate.split(/(?=b(?:Problems|Payments|Tenant Commitments|Landlord Obligations|EBM Codes):)/i);
// Helper function to update a specific category
function updateCategory(category, newInfo) {
const regex = new RegExp(`${category}:.*?(\n|$)`, "gi"); // Match category in the existing status
const cleanedStatus = updatedStatus.replace(regex, "").trim(); // Remove existing category info
return `${cleanedStatus}n${category}: ${newInfo}`.trim(); // Append new category info
}
// Process each update and match it to its category
updates.forEach((item) => {
const lowerItem = item.toLowerCase();
if (lowerItem.includes("problems")) {
updatedStatus = updateCategory(categories.problems, item.split(":")[1].trim());
extractedCategories[categories.problems] = item.split(":")[1].trim(); // Log Problems
} else if (lowerItem.includes("payments")) {
updatedStatus = updateCategory(categories.payments, item.split(":")[1].trim());
extractedCategories[categories.payments] = item.split(":")[1].trim(); // Log Payments
} else if (lowerItem.includes("tenant commitments")) {
updatedStatus = updateCategory(categories.tenantCommitments, item.split(":")[1].trim());
extractedCategories[categories.tenantCommitments] = item.split(":")[1].trim(); // Log Tenant Commitments
} else if (lowerItem.includes("landlord")) {
updatedStatus = updateCategory(categories.landlordObligations, item.split(":")[1].trim());
extractedCategories[categories.landlordObligations] = item.split(":")[1].trim(); // Log Landlord Obligations
} else if (lowerItem.includes("ebm")) {
updatedStatus = updateCategory(categories.ebm, item.split(":")[1].trim());
extractedCategories[categories.ebm] = item.split(":")[1].trim(); // Log EBM Codes
}
});
// Log the extracted categories for debugging
Logger.log(`Row ${index + 1}: Extracted Categories: ${JSON.stringify(extractedCategories)}`);
// Remove duplicate lines from the final status
const uniqueLines = [...new Set(updatedStatus.split("n"))].join("n").trim();
return [uniqueLines]; // Return the updated status
});
// Write merged data back into Column C
sheet.getRange(1, 3, mergedData.length, 1).setValues(mergedData);
// Clear all updates in Column F after processing
sheet.getRange(1, 6, data.length, 1).clearContent();
SpreadsheetApp.getUi().alert(“Columns C have been updated with merged statuses, and Column F has been cleared.”);
}