Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Batch updateRecordsAsync loop

Topic Labels: Scripting extentions
Solved
Jump to Solution
3223 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Joshua_Michaels
4 - Data Explorer
4 - Data Explorer

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
Joshua_Michaels
4 - Data Explorer
4 - Data Explorer

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",
        },
    },
]);
Joshua_Michaels
4 - Data Explorer
4 - Data Explorer

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: