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.
