Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Oct 28, 2020 08:18 PM
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!
Solved! Go to Solution.
Oct 28, 2020 10:12 PM
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:
Oct 28, 2020 08:58 PM
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",
},
},
]);
Oct 28, 2020 10:12 PM
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: