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!
My goal is to run this each night to ensure that I'm only seeing the current month's transactions in my view, but never less than the seven days before or after today.
When I run the script, it finishes without error, but does not change my base and there is no output when testing the automation.
// 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 }
});
}
}