Help

I need help with Scripting

Topic Labels: Automations Community Data
678 1
cancel
Showing results for 
Search instead for 
Did you mean: 
nshnv
4 - Data Explorer
4 - Data Explorer

Hi Airtable community,
My script is taking too much time to complete and going over 30 seconds limit. I would appreciate if you could review it and give me some tips. Here's the code:

let emailSendersTable = base.getTable("Emails to clients");
let listingsTable = base.getTable("Listings");

let emailSendersQuery = await emailSendersTable.selectRecordsAsync();

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);
}
1 Reply 1
Alexey_Gusev
13 - Mars
13 - Mars

Hi,

on a quick view:
move that query outside the main loop (i think, it better to be placed at the  beginning part, under the first query)
let listingsQuery = await .....

remove this piece:

    if (recordsToUpdate.length >= updateBatchSize) {
        await listingsTable.updateRecordsAsync(recordsToUpdate);
        recordsToUpdate = [];
    }

Modify the code outside the main loop in the end of script to update by batches according to updateBatchSize limit.

In common, typical Airtable script is: 
- get/define input data
- transform it into array of output data
- write output data to the table

for loop is a part of 'transform'. So, all await select... must be located before it, and await updateRec.. - after