I'm a total novice with scripting and automation, so I had ChatGPT write a script. It looks right to me (I've double-checked the strings) and does not throw out any errors, but it is not having the desired effect on my base.
Would love some pointers... Thanks in advance!
// Define the table and view
let table = base.getTable("Transactions");
let view = table.getView("All Transactions");
// Fetch records from the view
let query = await view.selectRecordsAsync();
// Get the current date
let currentDate = new Date();
let currentYear = currentDate.getFullYear();
let currentMonth = currentDate.getMonth();
let currentDay = currentDate.getDate();
// Helper function to determine if a date is within the current month
function isWithinCurrentMonth(date) {
return date.getFullYear() === currentYear && date.getMonth() === currentMonth;
}
// Helper function to determine if a date is within seven days of the current date
function isWithinSevenDays(date) {
let diff = Math.abs(currentDate - date);
let daysDiff = diff / (1000 * 60 * 60 * 24);
return daysDiff <= 7;
}
// Loop through each record
for (let record of query.records) {
let status = record.getCellValue("Status");
let transactionDate = new Date(record.getCellValue("**Date"));
// Skip if the status is "Pending"
if (status === "Pending") continue;
// Determine the new status
let newStatus = status;
if (!isWithinCurrentMonth(transactionDate) && !isWithinSevenDays(transactionDate)) {
if (status === "Cleared") {
newStatus = `Archive ${transactionDate.getFullYear()}`;
} else {
newStatus = `${status} ${transactionDate.getFullYear()}`;
}
}
// Ensure the new status is a valid single-select option
let validStatuses = [
"Autopay", "Planned", "Cleared", "Pending",
"Archive 2024", "Archive 2023", "Archive 2022", "Archive 2021",
"Autopay 2024", "Autopay 2025", "Autopay 2026",
"Planned 2024"
];
if (!validStatuses.includes(newStatus)) {
continue;
}
// Update the record if the status has changed
if (newStatus !== status) {
await table.updateRecordAsync(record.id, {
"Status": { name: newStatus }
});
}
}