Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Batch updateRecordsAsync loop

Topic Labels: Scripting extentions
Solved
Jump to Solution
1678 2
cancel
Showing results for 
Search instead for 
Did you mean: 

I’m trying to batch update records using the Scripting app. I’m able to update one-by-one using this script:

// set the table
let productsTbl = base.getTable('PRODUCTS')
// get the table records
let products = await productsTbl.selectRecordsAsync()

// loop through the records
for (let record of products.records) {
    // set variables to the record values
    let recordID = record.getCellValue('ID')
    let option1Name = record.getCellValue('Option1 Name')
    let option1Value = record.getCellValue('Option1 Value')
    let tags = record.getCellValue('Tags')

    if (option1Name === 'Pattern') {
        let id_pattern =
            '[pattern:' +
            option1Value +
            ']' +
            'id_pattern:' +
            recordID +
            '_' +
            slugify(option1Value)

        let newTags = id_pattern + ', ' + tags
        await productsTbl.updateRecordAsync(record, {
            Tags: newTags,
        })
    }
}

function slugify(string) {
    const a =
        'àáâäæãåāăąçćčđďèéêëēėęěğǵḧîïíīįìłḿñńǹňôöòóœøōõőṕŕřßśšşșťțûüùúūǘůűųẃẍÿýžźż·/_,:;'
    const b =
        'aaaaaaaaaacccddeeeeeeeegghiiiiiilmnnnnoooooooooprrsssssttuuuuuuuuuwxyyzzz------'
    const p = new RegExp(a.split('').join('|'), 'g')

    return string
        .toString()
        .toLowerCase()
        .replace(/\s+/g, '-') // Replace spaces with -
        .replace(p, (c) => b.charAt(a.indexOf(c))) // Replace special characters
        .replace(/&/g, '-and-') // Replace & with 'and'
        .replace(/[^\w\-]+/g, '') // Remove all non-word characters
        .replace(/\-\-+/g, '-') // Replace multiple - with single -
        .replace(/^-+/, '') // Trim - from start of text
        .replace(/-+$/, '') // Trim - from end of text
}

After viewing this topic: Why is my createRecordAsync loop terminating at 15 records?

I’d like to batch in groups of 50 but what is the correct syntax for updating records, not creating records?

In my script, I’m grabbing everything in the ‘Tags’ column for each record, then creating a new tag, and prepending the new tag to existing tags.

Thanks!

1 Solution

Accepted Solutions

Thanks to @Kamille_Parks pointing me in the right direction.

Here is the final code:

// set the table
let productsTbl = base.getTable('BATCH_SCRIPT_TEST')
// get the table records
let products = await productsTbl.selectRecordsAsync()

let recordsData = [];

// loop through the records
for (let record of products.records) {
    // set variables to the record values
    let recordID = record.getCellValue('ID')
    let recordVariantID = record.getCellValue('Variant ID')
    let option1Name = record.getCellValue('Option1 Name')
    let option1Value = record.getCellValue('Option1 Value')
    let tags = record.getCellValue('Tags')

    if (option1Name === 'Pattern') {
        let id_pattern =
            '[pattern:' +
            option1Value +
            ']' +
            'id_pattern:' +
            recordID +
            '_' +
            slugify(option1Value)

        let newTags = id_pattern + ', ' + tags

        let newRecord = { id: record.id, fields: {"Tags": newTags}}

        console.log('newRecord', newRecord, record.id)

        recordsData.push(newRecord)
    }
}

console.log('recordsData', recordsData)

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

function slugify(string) {
    const a =
        'àáâäæãåāăąçćčđďèéêëēėęěğǵḧîïíīįìłḿñńǹňôöòóœøōõőṕŕřßśšşșťțûüùúūǘůűųẃẍÿýžźż·/_,:;'
    const b =
        'aaaaaaaaaacccddeeeeeeeegghiiiiiilmnnnnoooooooooprrsssssttuuuuuuuuuwxyyzzz------'
    const p = new RegExp(a.split('').join('|'), 'g')

    return string
        .toString()
        .toLowerCase()
        .replace(/\s+/g, '-') // Replace spaces with -
        .replace(p, (c) => b.charAt(a.indexOf(c))) // Replace special characters
        .replace(/&/g, '-and-') // Replace & with 'and'
        .replace(/[^\w\-]+/g, '') // Remove all non-word characters
        .replace(/\-\-+/g, '-') // Replace multiple - with single -
        .replace(/^-+/, '') // Trim - from start of text
        .replace(/-+$/, '') // Trim - from end of text
}

Hopefully this will help someone else.

:sparkles: :rocket: :taco:

See Solution in Thread

2 Replies 2

Syntax for it is in the documentation: Airtable Scripting

await table.updateRecordsAsync([
    {
        id: records[0].id,
        fields: {
            "Description": "Update one",
        },
    },
    {
        id: records[1].id,
        fields: {
            "Description": "Update two",
        },
    },
]);

Thanks to @Kamille_Parks pointing me in the right direction.

Here is the final code:

// set the table
let productsTbl = base.getTable('BATCH_SCRIPT_TEST')
// get the table records
let products = await productsTbl.selectRecordsAsync()

let recordsData = [];

// loop through the records
for (let record of products.records) {
    // set variables to the record values
    let recordID = record.getCellValue('ID')
    let recordVariantID = record.getCellValue('Variant ID')
    let option1Name = record.getCellValue('Option1 Name')
    let option1Value = record.getCellValue('Option1 Value')
    let tags = record.getCellValue('Tags')

    if (option1Name === 'Pattern') {
        let id_pattern =
            '[pattern:' +
            option1Value +
            ']' +
            'id_pattern:' +
            recordID +
            '_' +
            slugify(option1Value)

        let newTags = id_pattern + ', ' + tags

        let newRecord = { id: record.id, fields: {"Tags": newTags}}

        console.log('newRecord', newRecord, record.id)

        recordsData.push(newRecord)
    }
}

console.log('recordsData', recordsData)

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

function slugify(string) {
    const a =
        'àáâäæãåāăąçćčđďèéêëēėęěğǵḧîïíīįìłḿñńǹňôöòóœøōõőṕŕřßśšşșťțûüùúūǘůűųẃẍÿýžźż·/_,:;'
    const b =
        'aaaaaaaaaacccddeeeeeeeegghiiiiiilmnnnnoooooooooprrsssssttuuuuuuuuuwxyyzzz------'
    const p = new RegExp(a.split('').join('|'), 'g')

    return string
        .toString()
        .toLowerCase()
        .replace(/\s+/g, '-') // Replace spaces with -
        .replace(p, (c) => b.charAt(a.indexOf(c))) // Replace special characters
        .replace(/&/g, '-and-') // Replace & with 'and'
        .replace(/[^\w\-]+/g, '') // Remove all non-word characters
        .replace(/\-\-+/g, '-') // Replace multiple - with single -
        .replace(/^-+/, '') // Trim - from start of text
        .replace(/-+$/, '') // Trim - from end of text
}

Hopefully this will help someone else.

:sparkles: :rocket: :taco: