Quota in form

[Doug]

Below is my code.

Our table is fed by a form that our real estate agents use to track their marketing efforts.

At 8AM daily, we have an automation that runs the following script.

// query for all the records in my ON MARKET table
let table = base.getTable("Ads");
let view = table.getView('On Market')
let query = await view.selectRecordsAsync();

// for each record, check rental platform api for any updates in status or pricing
for (let record of query.records) {

    let response = await fetch(`https://www.domain.com/api/rentals/search.php?key=API_KEY_REMOVED&request_type=%22JSON%22&listing_id=${record.getCellValueAsString("ID")}`);
    let data = await response.json();
    data = data.listings[0];

    // console.log(data) **testing only**

    await table.updateRecordAsync(record, {
        'Status': {name: data.status},
        'Street Number': data.streetNumber,
        'Street Name': data.streetName,
        'Unit': data.unit,
        'City': data.city,
        'Rent': data.price,
        'Source': {name: data.source}
    });

    // output.set('data', data)
}

It worked perfectly, until the table got a little bigger.

Today I started receiving the following error:

Error: Exceeded quota of 50 fetch requests per script invocation.

The issue is, my table will continue to grow to 5,000+ records.

Is there any way that I can invoke the script in batches? I know it’s possible because DataFetcher does something similar.

[kuovonne]

DataFetch is not run as an automation, so it does not have the same limitations.

There are various ways of splitting the script into batches. Most methods will need to have a field in the table that indicates when the update was last performed. Then the script will read that field to determine which records to include in the current batch.

You can split the processing across multiple scripting actions in the same automation (25 actions per automation at 50 fetches per script = 1250 records/automation), as well across multiple automations.

By the way, your script also is updating records one-at-a-time with updateRecordAsync. You will get better performance if you update records in batches of 50 using updateRecordsAsync.

Another option is to have a human remember to press an update button that runs a Scripting App at 8am every day. Scripting app does not have the same limitations as automation scripts.

[Bill_French]

Doug:

let query = await view.selectRecordsAsync();

Unless you need to update all fields in the target records, you should scope the query to read only the fields required for the process. This will improve performance just a little bit, but it could be significant if the table is loaded with fields unneeded for this process.

@kuovonne is spot on - this process is likely to be 50 times faster if the code were designed in a manner that compresses the number of writes into the target table.

Doug:

await fetch(https://www.domain.com/api…`

This is another big one - you are making an API fetch for each record which is seriously impacting performance. Consider creating a query that extracts all rental data in a single fetch, and then perform the iterative process against the complete result set to update your Airtable records.

[kuovonne]

Bill.French:

Unless you need to update all fields in the target records, you should scope the query to read only the fields required for the process.

Both the code editor and the documentation currently discourage querying for records without specifying the fields. It is currently supported, but is likely to be depreciated eventually.

So, you should indicate the fields you need, even if you really need all fields in the table.

[Doug]

Bill.French:

Unless you need to update all fields in the target records, you should scope the query to read only the fields required for the process. This will improve performance just a little bit, but it could be significant if the table is loaded with fields unneeded for this process.

I hadn’t thought of this but it makes sense. I’m new to JS and building scripts on AirTable so there was a lot of trial and error to get to this point. I gave it a shot below…

Bill.French:

Doug:

await fetch(https://www.domain.com/api…`

This is another big one - you are making an API fetch for each record which is seriously impacting performance. Consider creating a query that extracts all rental data in a single fetch, and then perform the iterative process against the complete result set to update your Airtable records.

So just to confirm, something like this:

let table = base.getTable("Ads");
let view = table.getView('On Market')
let query = await view.selectRecordsAsync({
    fields: [
        'Listing ID',
        'Status',
        'Street Number',
        'Street Name',
        'Unit',
        'City',
        'Rent',
        'Source']
});

let listings = await fetch(`https://www.domain.com/api/rentals/search.php?key=API_KEY`);
let listings = await listings.json();

for (let record of query.records) {

    for (let listing of listings) {

        if (record.listing_id == listing.id) {

            UPDATE RECORD

        }
    }
}

[Doug]

Thanks you @kuovonne

For the time being, I have implemented your last suggestion (human clicking a button in the scripting app) until I figure out how to properly implement some of the suggestions above in an automation.

[Kamille_Parks]

Doug:

So just to confirm, something like this:

The placement of the UPDATE RECORD placeholder still suggests that you’ll be updating records one at a time. To follow through on one of Kuovonne’s suggestions, a common method of batch updating would be to

• declare let updates = [] (an empty array soon to be filled with record values to update) outside of your loops
• inside your if () statement do updates.push({id: record.id, {'Field Name': listing.field}})
• include something like this after and outside your loops to execute the record updates in batches of 50 at a time:

while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

[Bill_French]

Doug:

something like this

Yes, this will eliminate n(fetches) where n is the number of records in the table.

BUT!!!

There’s another seemingly innocuous optimization opportunity - nested loops.

What if - instead of looping through all listings looking for the ID of each table record, you used a more direct approach to that listing - a hash index that allowed you to point to the listing in 2 milliseconds.

If you created a hash index of the listings, you could lookup each listing instantly with a single reference. The for...listing loop is performed once to create the index, and all references to it are like this:

 if (oListings[record.listing_id])
   UPDATE RECORD

This eliminates a vast number of executions.

[kuovonne]

Doug:

I’m new to JS and building scripts on AirTable so there was a lot of trial and error to get to this point.

Congratulations on what you have accomplished thus far in your coding journey. Simply getting a working script at all can be a major accomplishment.

You are getting great suggestions in this thread from some very experienced programmers. Some of the suggestions may feel a little overwhelming. That’s okay. Just see what you are capable of digesting in the moment, and work on that concept. If you find coding interesting, you might go through several versions of this script as you have time to figure out new things.