Help

Re: Scripting Block: A (hopefully) Helpful Batching Function

3746 0
cancel
Showing results for 
Search instead for 
Did you mean: 

3/17/2020 Edit: updated the the ‘Create’ case to return the recordId's of the created records, rather than just a count of created records. Thanks @kuovonne for the idea.

The updateRecordsAsync(), createRecordsAsync(), and deleteRecordsAsync() functions that Airtable provides in the Scripting Block only allow the action to perform on 50 records at a time at most.

While Airtable does provide a helpful example of how to run a loop that batches a set of records greater than 50 for processing in the “Find & Replace” example of the Scripting Block documentation…

// Only up to 50 updates are allowed at one time, so do it in batches
while (updates.length > 0) {
    await table.updateRecordsAsync(updates.slice(0, 50));
    updates = updates.slice(50);
}

I thought it might be nice to have a single function to drop into any script that would handle this for me, regardless of the action I want to take on the records. (Admittedly, it would be nice if this was in the Scripting Block’s SDK by default – but I understand Airtable’s hesitance to provide functions that require multiple parameter inputs).

So here’s what I came up with:

/*
    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.

    ::PARAMETERS::
    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to
            perform

    ::RETURNS::
    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
*/
async function batchAnd(action, table, records) {
    let recordsActedOn;

    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;
        
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                recordsActedOn.push(...recordIds)
                records = records.slice(50);
            };
            break;

        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.deleteRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            }
            break;

        default:
            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    }
    return recordsActedOn;
}

The function is called batchAnd() and it takes 3 parameters:

  • action - expects a string value of either ‘Update’, ‘Create’, or ‘Delete’ (if some other string value is provided, the function will add a message to the block’s output informing the user of the error)
  • table - expects a Table object reference to call .[action]RecordsAsync() on
  • records - expects an Array of records to perform the action on, formatted in the way the .[action]RecordsAsync() function from the SDK expects

In the case of success, the function returns either an integer equal to the number of records that were processed by the function (this is the case for the ‘Update’ and ‘Delete’ paths), or an array of the recordId’s of the new records created by the function (this is the case for the ‘Create’ path); if the function doesn’t run due to a bad parameter, it returns null. This return value can be used to check if the function was successful before deciding what to output at the end of a Script.

Just copy-paste that function into your Scripting Block code (probably at the very end), and you can use the function to handle all of your .createRecordsAsync(), .updateRecordsAsync(), and .deleteRecordsAsync() calls.

The function is asynchronous, so it needs to be called with the await keyword, since it contains calls to asynchronous functions itself.

Here’s an example of the function in use that would update all the records in the example table with the text “New Value” in the example field:

const table = base.getTable("Table name");
const query = await table.selectRecordsAsync();
const records = query.records;

let recordsToUpdate = records.map(record => {
    let update = {id: record.id, fields: {"Example Field name": "New Value"}};
    return update;
});

// Assuming the 'batchAnd()` function is pasted into the code further down
let recordsUpdated = await batchAnd('Update', table, recordsToUpdate);

// Explicitly check for not null, since the value could potentially be
// the integer '0', which would eval to false
if (recordsUpdated !== null) {
    output.markdown(`Success! ${recordsUpdate} records were updated with "New Value"`);
};

Here’s an example using the ‘Create’ branch:

const tableToLink = base.getTable("Table name");
const tableToCreateIn = base.getTable("Other Table name");

const query = await tableToLink.selectRecordsAsync();
const records = query.records;

let recordsToCreate = records.map(recordToLink => {
    let newLinkedRecord = {fields: {"Linked Field name": recordToLink.id}};
    return newLinkedRecord;
});

// Assuming the 'batchAnd()` function is pasted into the code further down
let recordsCreated = await batchAnd('Create', table, recordsToCreate);

// Check that the create did not return null and records were created
if (recordsCreated && recordsCreated.count > 0) {
    output.markdown(`Success! ${recordsCreated.count} records were created in ${tableToCreateIn.name}`);
    
    console.log(recordsCreated); // => ['rec9da0gf94', 'reca08g8a0g', etc...]
};

Other examples of this function in use can be seen in my Expense Tracking with Match Payee Scripting Block and Business Development CRM w/ Form->Contact Linking Script demos in Airtable Universe.

This is my first second version of this function – if you have feedback on how it could be improved, or if there are issues with my approach that I am blind to, please let me know in a constructive manner :slightly_smiling_face:

Otherwise, feel free to drop this function into your Scripts and use it to handle care-free batch updating.

6 Replies 6

Here are some ideas to consider.

Consider throwing a JavaScript exception instead of outputting the error message to the user, since it could only happen as a result in a problem in the code elsewhere, not anything the user did.

For the return value of the function, consider returning an answer based on the return values of the Async functions. For example, for creating records, you could consolidate the responses from each batch together to sent a single response.


I tried refactoring the script with the idea of passing the function to be run as a parameter to the function. It was mostly an academic experiment because it would reduce the number of parameters and I wondered if it would be allowed. But your way is better than passing the function as a parameter; your way doesn’t obfuscates the code as much.


I hope you find these comments useful, and can take them in the spirit offered. Scripting block has motivated me to attempt many things that I have never done before. Commenting publicly on someone else’s code is one of those things.

VictoriaPlummer
7 - App Architect
7 - App Architect

Thank you! This is very helpful.

Steven_Zhang
6 - Interface Innovator
6 - Interface Innovator

This is great! OOC, how do you/what have you seen others do to keep code modular? Do you copy/paste your code between the scripting app and some version control system?

openside
10 - Mercury
10 - Mercury

Hi @Steven_Zhang - We built On2Air: Scripts to improve the scripting environment and make it more modular. Check it out here: https://on2air.com/on2air-scripts

Here is intro video showcasing it:

Hi @Steven_Zhang,

I just have this one saved in a local text file and I copy-paste it when needed.
This is really my only modular piece of code that I use in Script block at the moment.

I do not currently do anything to version control my scripts. I don’t really feel I have anything heavy enough to warrant that at the moment. I’ve mostly been keeping my scripts small and specialized.

I do have one of my larger scripts stored in a gist on GitHub.

tools
4 - Data Explorer
4 - Data Explorer

@Jeremy_Oglesby great script, thanks for taking the time to make this. We just used it successfully on a complex script. One issue we ran into was we were having silent error failures. We added try/catch to your script to improve DX

/*
    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.

    ::PARAMETERS::
    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to
            perform

    ::RETURNS::
    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
*/
async function batchAnd(action, table, records) {
    let recordsActedOn;

    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                try {
                    await table.updateRecordsAsync(records.slice(0, 50));
                    records = records.slice(50);
                } catch(err) {
                    console.log(err.message)
                }
            };
            break;
        
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                try {
                    let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                    recordsActedOn.push(...recordIds)
                    records = records.slice(50);
                } catch(err) {
                    console.log(err.message)
                }
            };
            break;

        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                try {
                    await table.deleteRecordsAsync(records.slice(0, 50));
                    records = records.slice(50);
                } catch(err) {
                    console.log(err.message)
                }
            }
            break;

        default:
            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    }
    return recordsActedOn;
}

One other thing we found was that by dependency injecting the table we lost some type inference that was helpful during debugging. We actually ended up removing this and just hard coding the function to a specific table, now that we have fixed it we could probably go back to injecting the table. Regardless I thought I’d just share this here in case it was helpful to you.