Hi, Airtable community
Recently I wrote a script for one of my automations in Airtable, however it's taking so much time so it's going over the limit of 30 seconds. I would high appreciate if you could review and tell me what I could optimize or improve in order for it to work faster.
Here's the code:
let emailSendersTable = base.getTable("Emails to clients");
let listingsTable = base.getTable("Listings");
let emailSendersQuery = await emailSendersTable.selectRecordsAsync();
let listingsQuery = await listingsTable.selectRecordsAsync({
view: "NEVER TOUCH THIS VIEW! 2",
fields: [
"Monthly rent",
"Size in m²",
"Area (ku and district)",
"Layout filter",
"Features",
"Created date",
"Clients (Send emails)",
],
});
let updateBatchSize = 50;
let recordsToUpdate = [];
for (let emailSender of emailSendersQuery.records) {
if (!emailSender.getCellValue("Budget") || !emailSender.getCellValue("Size") || !emailSender.getCellValue("Area") || !emailSender.getCellValue("Layout") || !emailSender.getCellValue("Features")) {
continue;
}
// Access client requirements directly from the "Emails to clients" table
let budgetValue = emailSender.getCellValueAsString("Budget");
let budgetRange = budgetValue.match(/(?:~\s*)?￥(\d+,\d+)(?:\s*-\s*￥(\d+,\d+))?(?:\s*~)?/);
let minBudget = 0;
let maxBudget = Infinity;
if (budgetRange) {
if (budgetRange[1] && budgetRange[2]) {
minBudget = parseInt(budgetRange[1].replace(/[^0-9]/g, ""));
maxBudget = parseInt(budgetRange[2].replace(/[^0-9]/g, ""));
} else if (budgetRange[1] && !budgetRange[2]) {
if (budgetValue.startsWith("~")) {
maxBudget = parseInt(budgetRange[1].replace(/[^0-9]/g, ""));
} else if (budgetValue.endsWith("~")) {
minBudget = parseInt(budgetRange[1].replace(/[^0-9]/g, ""));
}
}
}
let size = emailSender.getCellValue("Size") ? emailSender.getCellValue("Size").name : '0m²';
let sizeValue = parseInt(size.match(/(\d+)m²/)[1], 10);
let areaValues = emailSender.getCellValue("Area") ? emailSender.getCellValue("Area").map(area => area.name) : [];
let areaValuesInListings = areaValues.map(area => area + "-ku");
let layoutValue = emailSender.getCellValue("Layout") ? emailSender.getCellValue("Layout").name : null;
let featuresValues = emailSender.getCellValue("Features") ? emailSender.getCellValue("Features").map(feature => feature.name) : [];
let isPetAllowedRequired = featuresValues.includes("Pet allowed");
let sixDaysAgo = new Date();
sixDaysAgo.setDate(sixDaysAgo.getDate() - 10);
let listingsQuery = await listingsTable.selectRecordsAsync({
view: "NEVER TOUCH THIS VIEW! 2",
fields: ["Monthly rent", "Size in m²", "Area (ku and district)", "Layout filter", "Features", "Created date", "Clients (Send emails)"]
});
let filteredListings = listingsQuery.records.filter(record => {
let monthlyRent = record.getCellValue("Monthly rent");
let sizeInSquareMeters = record.getCellValue("Size in m²");
let listingAreaValues = record.getCellValue("Area (ku and district)") ? record.getCellValue("Area (ku and district)").map(area => area.name) : [];
let listingLayoutValue = record.getCellValue("Layout filter") ? record.getCellValue("Layout filter").name : null;
let listingFeaturesValues = record.getCellValue("Features") ? record.getCellValue("Features").map(feature => feature.name) : [];
let isListingCreatedWithinLastSixDays = record.getCellValue("Created date") && new Date(record.getCellValue("Created date")) > sixDaysAgo;
let hasMatchingArea = areaValuesInListings.some(area => listingAreaValues.includes(area));
let hasMatchingLayout = layoutValue ? listingLayoutValue === layoutValue : true;
let hasPetAllowed = isPetAllowedRequired ? listingFeaturesValues.includes("Pet allowed") : true;
return (
monthlyRent > minBudget &&
monthlyRent < maxBudget &&
sizeInSquareMeters > sizeValue &&
hasMatchingArea &&
hasMatchingLayout &&
hasPetAllowed &&
isListingCreatedWithinLastSixDays
);
});
let latestSevenListings = [];
for (let i = 0; i < 7; i++) {
if (filteredListings.length === 0) {
break;
}
let latestListing = filteredListings.reduce((latest, record) => {
return record.getCellValue("Created date") > latest.getCellValue("Created date") ? record : latest;
});
latestSevenListings.push(latestListing);
filteredListings = filteredListings.filter(record => record.id !== latestListing.id);
}
for (let listing of listingsQuery.records) {
let currentSendEmails = listing.getCellValue("Clients (Send emails)") || [];
let filteredSendEmails = currentSendEmails.filter(sendEmail => sendEmail.id !== emailSender.id);
let updateData;
if (latestSevenListings.some(l => l.id === listing.id)) {
updateData = [...filteredSendEmails, { "id": emailSender.id }];
} else {
updateData = filteredSendEmails;
}
recordsToUpdate.push({ id: listing.id, fields: { "Clients (Send emails)": updateData } });
if (recordsToUpdate.length >= updateBatchSize) {
await listingsTable.updateRecordsAsync(recordsToUpdate);
recordsToUpdate = [];
}
}
}
if (recordsToUpdate.length > 0) {
await listingsTable.updateRecordsAsync(recordsToUpdate);
}
