The Complete Guide to Airtable Scripting

Airtable is one of the best tools on the web for managing data and building apps, and the built-in Airtable scripting language is one of its most powerful features. With a little scripting knowledge you can unlock this power to create productivity-boosting scripts that save you time and enable you to transform your data.

This comprehensive guide, built upon an Airtable cheatsheet we use at Simplescraper, is designed to turn you into an Airtable scripting pro. It contains ready-to-run code examples and step-by-step explanations of how to use every scripting feature essential for creating your own advanced scripts.

So, whether you're new to scripting or an experienced Airtable user, this guide has something of value to offer. Let’s jump in.

Table of contents

• 1. Installing the Scripting Extension
• 2. Fundamentals of Airtable Scripting Syntax
  • 2.1 Working with Bases, Tables and Views
  • 2.2 Interacting with Tables
  • 2.3 Querying Records
  • 2.4 Accessing Cell Values
  • 2.5 Accessing Record Properties
  • 2.6 Creating New Records in Airtable
  • 2.7 Updating Records in Airtable
  • 2.8 Airtable's Unique Approach to Fetch
• 3. Best Scripting Practices
  • 3.1 Configuration at the Top
  • 3.2 Use Async/Await Consistently
  • 3.3 Error Handling
  • 3.4 Modular Functions
• 4. Learning Through Mini Projects
  • 4.1 Call OpenAI for Sentiment Analysis and Update fields in Airtable
  • 4.2 Formatting Text and Updating in a New Field
  • 4.3 Splitting Text into Multiple Fields
• Conclusion and Next Steps

1. Installing the Scripting Extension

Before we deep-dive into the world Airtable scripting, let's quickly set up the Scripting extension. Follow the steps below:

1. Open your Airtable base
2. Click on "Apps" in the top right corner
3. Search for "Scripting" in the marketplace
4. Click "Add app" to install the Scripting extension
5. Choose a table to associate with the extension (you can change this later)

With the Scripting extension installed, you're ready to start coding!

2. Fundamentals of Airtable Scripting Syntax

This section covers the basics of interacting with data in Airtable through scripting. We’ll start by connecting to a table, querying data and then move on to advanced use cases like updating fields using external data. Feel free to copy and paste the data into your own Airtable scripting extension to follow along in real time, and pay special attention to the comments which explain what each part of the script is doing.

2.1 Working with Bases, Tables and Views

Every Airtable script begins with the base object, which represents your entire Airtable base. From there, you can reference specific tables and views. The typical sequence is:

1. Connect to a base
2. Connect to a table
3. Connect to a view (optional)
4. Retrieve or add data

Here's how to access a table and a view:

// retrieve a table by its name to query all records within it
let table = base.getTable('Shopify data');

// retrieve a specific view from the table to query only records visible in this view
let view = table.getView('New customers');

2.2 Interacting with Tables

Once you have a reference to a table, you can perform various operations to retreive info about it:

// get all fields in a table
let fields = table.fields;

// get a specific field by name
let specificField = table.getField('Customer ID');

// loop through all fields and log their names and types
for (let field of fields) {
    console.log(`${field.name}: ${field.type}`);
}

2.3 Querying Records

To read records from a table, use the selectRecordAsync() or selectRecordsAsync() methods.

• selectRecordAsync(): Accepts a record ID as an argument and returns a single record object
• selectRecordsAsync(): Accepts field names or field IDs as arguments and returns an object which includes an array of records

Things to note:

• For both methods, you must pass an array of field names for the fields you wish to return, or use table.fields to return all fields
• For selectRecordsAsync(), the records from your query are available under the records property of the queryResult object, i.e. queryResult.records
• Always use await with these async methods

Examples:

// use selectRecordAsync to get a single record by ID
let singleRecord = await table.selectRecordAsync("rec123456789");


// use selectRecordsAsync to get multiple records, specify which fields to return using an array of field names
let queryResults = await table.selectRecordsAsync({
    fields: ["Customer ID", "Price"]
});

// use table.fields to return all fields (referencing the table variable defined earlier in this guide)
let queryResults = await table.selectRecordsAsync({fields: table.fields});

// specify fields and sort the results
let queryResult = await table.selectRecordsAsync({
    fields: ["Customer ID", "Price"],
    sorts: [
       {field: "Customer ID"},
       {field: "Price", direction: "desc"},
    ]
});

2.4 Accessing Cell Values

Once you’ve retrieved your array of records, you’ll want to access the data. Airtable provides two primary methods for accessing cell values: getCellValue() and getCellValueAsString().

• Use getCellValueAsString() for human-readable format, ideal for display purposes.
• Use getCellValue() for data that may require further processing, like dates, numbers, or arrays.

Things to note:

• The field name is passed as an argument
• You can only access fields that you requested during the selectRecordsAsync step

Let’s look at some examples:

// access the first record from the queryResults response
let customerId = queryResults.records[0].getCellValue("Customer ID");
console.log(customerId);

// loop through all queryResults and access the cell data for specific field
for (let record of queryResults.records) {
    // using getCellValueAsString() method
    let price= record.getCellValueAsString("Price"); // Price is a number field in the table
    console.log("Price:", price);
    console.log(typeof price); // type will be changed to string

    // using getCellValue() method
    price = record.getCellValue("Price");
    console.log("Price:", price);
    console.log(typeof price); // type will be a number (unchanged)
    
    
    // trying to access a field that we did not request during the selectRecordsAsync step
    let address = record.getCellValue("Address");
    // output: Error: No field matching "Address" found in table "Shopify data"
}

Working with Multi-value field types

Certain field types, like Multiple select and Attachment, can contain more than one value. The cell contents of these field types are returned as an array of objects containing the field value(s) so getCellValue() is typically required to retrieve the value for further processing.

Examples:

// query records, returning only the 'Product Category' multiple-select field and the 'Invoice PDF' attachment field
let queryResults = await table.selectRecordsAsync({fields: fields: ["Product Category", "Invoice PDF"]});

for (let record of queryResults.records) {

	// access multiple-select field
    let categories = record.getCellValue("Product Category");
    console.log(categories); // output: '[{name: "Electronics"}, {name: "Smartphones"}]'
    
    // array is returned so process each item individually
    for (let category of categories) {
		    console.log(category.name); // output loop 1: 'Electronics', output loop 2: 'Smartphones
		}
		
		// access the attachment field
		let invoices = record.getCellValue("Invoice PDF");
		
		for (let invoice of invoices) {
		    console.log(invoice.filename); // output: 'myinvoice.pdf'
		    console.log(invoice.url); // output: 'https://v5.airtableusercontent.com/....'
		}
}

// using getCellValueAsString on a multiple-select field returns a comma-seperated string of the values
for (let record of queryResults.records) {
    let categories = record.getCellValueAsString("Product Category");
    console.log(categories); // Output: 'Electronics, Smartphones'    
}

More information on what each field type returns can be found here: https://airtable.com/developers/extensions/api/FieldType.

2.5 Accessing Record Properties

The response from a query also returns the record ID and record name (i.e. the primary key / first column value) of each record.

We can access these values directly without record.getCellValue():

// access record properties of first query result
let record = queryResults.records[0];
console.log(record); // output: {id: "recUPZHnVkhhG3c3g", name: "cust1234567"} (the customer ID field is the primary key)

// in a loop
for (let record of queryResults.records) {
  console.log(record);
}

2.6 Creating New Records in Airtable

To create new records, use the createRecordsAsync() method with an array of objects containing the fields you wish to add. The basic format is { fields: {'Field Name': 'Value'}}, with some variations for multi-value field types and attachment field types.

Let’s look at a few examples:

Creating a basic record

let table = base.getTable("Shopify data");

// configure a single record
let newRecords = [
  {
    fields: {
      Name: "Wile Coyote",
      Email: "wile@acme.com",
      Price: 300,
      'Customer ID': 'CUST001'
    },
  },
];

// create the new record
let createdRecords = await table.createRecordsAsync(newRecords);
console.log('created', createdRecords.length, 'records');

// create an attachment record
let newAttachmentRecords = [
  {
    fields: {
      "Attachment Field Name": [
        {
          url: "https://example.com/image.jpg",
          filename: "image.jpg",
        },
      ],
    },
  },
];

createdRecords = await table.createRecordsAsync(newRecords);

Creating and Mapping Records from External Data

Often, you'll want to create records based on data from an external source whose objects keys (property names) and data types don’t match the field names and field types you’ve created in Airtable.

Here's an example of how we can use map() to transform and modify such data to allow it to be saved to Airtable:

// our 'Orders' table has fields: 'Order ID', 'Customer Name', 'Price', 'Items'
let table = base.getTable('Orders');

// assume we've fetched this data from Shopify API for example
let shopifyOrders = [
    { id: '1001', customer: 'Alice', price: 150.00, items: ['Shirt', 'Pants'] },
    { id: '1002', customer: 'Bob', price: 75.50, items: ['Hat'] }
];

// transform Shopify data into Airtable record format
let shopifyOrdersTransformed = shopifyOrders.map(order => ({
    fields: {
        'Order ID': order.id * 1, // convert to number to match Airtable number field type
        'Customer Name': order.customer,
        'Price': order.price,
        // for multiple-select fields convert each array item to an object with a 'name' property 
        'Items': order.items.map(item => ({ name: item }))
    }
}));

// Create the records
let createdOrders = await table.createRecordsAsync(shopifyOrdersTransformed);
console.log('created', createdOrders.length, 'orders from Shopify data');

Things to note:

• When creating a record with Multiple-select values, each value you add must exist as a predefined option in the Multiple-select field. So in the example above, 'Shirt', 'Pants' and 'Hat' must be predefined options.

Creating Large Number of Records (Batch creating records)

Airtable imposes a limit of 50 records at a time when creating, deleting or updating records. When you need to create more than 50 records, we can work around this limit using a while loop to process every record in an array of records:

let table = base.getTable("Shopify data");

// assume this function returns a large array of records we wish to create
let newRecords = getLargeNumberOfRecords();

// function to create records in batches
async function createRecordsInBatches(newRecords ) {
  while (newRecords.length > 0) {
    
    let batch = newRecords.splice(0, 50); // take up to 50 records from the array

    // create the batch of records
    await table.createRecordsAsync(batch);
    console.log(`created${batch.length} records. ${records.length} remaining.`);
  }
}

// run the function
await createRecordsInBatches(newRecords );
console.log("all records created successfully!");

2.7 Updating Records in Airtable

Updating records is similar to creating them, but with a key difference: you must include the record ID along with the fields you want to update.

You can update a single record using the updateRecordAsync() method or multiple records simultaneously with the updateRecordsAsync() method. When updating, only include the fields you wish to change.

Here's how to do both:

let table = base.getTable('Customers');

// prepare a single record update
let singleRecordId = 'rec1234567';
let fieldsToUpdate = {
    'Address': '123 fake st',
    'Age': 30
};

// update a single record
await table.updateRecordAsync(singleRecordId, fieldsToUpdate);


// prepare multiple records for bulk update. note the addition of the id keys
let recordsToUpdate = [
    {id: 'rec1234567', fields: {'Address': '123 fake st', 'Age': 31}},
    {id: 'rec7654321', fields: {'Email': 'jane.new@example.com'}}
];

// update multiple records at once
await table.updateRecordsAsync(recordsToUpdate);

Read, Modify, Update a Batch of Records

A typical scenario is to read a bunch of records, modify them, and update the table with new data. In this example assume we want to update the price of inventory, and also to set particular categories of inventory to unavailable. This is a task that is particularly tedious if done manually, but is straightforward using Airtable scripting:

let table = base.getTable("Products");

// 1. read records and return required  fields
let query = await table.selectRecordsAsync({
  fields: ["Price", "Category", "Available"],
});

// 2. modify data function - will be called from step 3
function modifyRecord(record) {

  let category = record.getCellValue("Category");
  let isAvailable = record.getCellValue("Available");
  let price = record.getCellValue("Price");
	 
	// define what categories we wish to mark out of stock
  const categoriesUnavailable = ['Shirts', 'Caps'];
  
  return {
    newPrice: price  * 1.1, // 10% price increase
    // set Available field to 'No' if in categoriesUnavailable array
    newStockStatus: categoriesUnavailable.includes(category) ? 'No' : isAvailable 
  };
}

// 3. construct array of updated records (notice the format of {id, fields:{}})
let recordsToUpdate = query.records.map(record => {
    let { newPrice, newStockStatus } = modifyRecord(record); // call the modify function
    return {
        id: record.id,
        fields: {
            'Price': newPrice,
            'Available': newStockStatus
        }
    };
});

// 4. batch update records
// note that we're using the while loop to update records as covered in the 'Creating Large Number of Records' section
async function updateRecordsInBatches(records) {
  while (records.length > 0) {
    let batch = records.splice(0, 50);
    await table.updateRecordsAsync(batch);
    console.log(
      `updated ${batch.length} records. ${records.length} remaining.`
    );
  }
}

await updateRecordsInBatches(recordsToUpdate);

Updating Large Number of Records

Similar to when creating records, Airtable limits you to updating 50 records at a time. Here's how to update a large number of records:

let table = base.getTable('Customers');

async function updateRecordsInBatches(records) {
    while (records.length > 0) {
        let batch = records.splice(0, 50);
        await table.updateRecordsAsync(batch);
        console.log(`Updated ${batch.length} records. ${records.length} remaining.`);
        await new Promise(resolve => setTimeout(resolve, 225)); // Rate limiting
    }
}

let recordsToUpdate = getLotsOfRecordsToUpdate(); // some function that returns lots of records
await updateRecordsInBatches(recordsToUpdate);

2.8 Airtable's Unique Approach to Fetch

Airtable provides a remoteFetchAsync() function that allows you to make HTTP requests from Airtable's servers, bypassing CORS restrictions:

let response = await remoteFetchAsync('https://simplescraper.io/api', {
    method: 'GET',
    headers: {'Content-Type': 'application/json'}
});
let data = await response.json();

This is particularly useful when interacting with external APIs that might not allow cross-origin requests from the browser.

3. Best Scripting Practices

In this section we’ll cover some good ways to organize your scripts. If you create many scripts, especially complex ones, things can get confusing fast. Ideally you want to be able to jump into a script you haven’t visited in a while and by productive straight away. These steps help with this.

3.1 Configuration at the Top

Place all your configuration variables at the top of your script for easy maintenance. This allows you to easily make changes to key variables without having to search through the entire script.

const CONFIG = {
    TABLE_NAME: 'Shopify customers',
    API_KEY_SHOPIFY: 'your-api-key-here',
    BATCH_SIZE_ADD_RECORDS: 50
};

// reference like so:
let table = base.getTable(CONFIG.TABLE_NAME);

3.2 Use Async/Await Consistently

Airtable's scripting environment supports async/await syntax. Use it consistently for better readability and error handling:

async function fetchAndProcessData() {
	let response = await remoteFetchAsync('https://simplescraper.io/api');
    let data = await response.json();
    await processData(data);
}

await fetchAndProcessData();

3.3 Error Handling

Implement try...catch error handling to make your scripts more robust:

try {
    let response = await remoteFetchAsync('https://simplescraper.io/api');
    if (!response.ok) {
        throw new Error(`HTTP error! status: ${response.status}`);
    }
    let data = await response.json();
    // Process your data here
} catch (error) {
    console.error('There was a problem with the fetch operation:', error);
}

3.4 Modular Functions

Break your script into smaller, reusable functions. This makes your code easier to read, test, and maintain:

async function fetchData(url) {
    let response = await remoteFetchAsync(url);
    return response.json();
}

async function processData(data) {
    // Process the data
}

async function updateAirtable(processedData) {
    // Update Airtable with the processed data
}

async function main() {
    let data = await fetchData('https://simplescraper.io/api');
    let processedData = await processData(data);
    await updateAirtable(processedData);
}

await main();

4. Learning Through Mini Projects

Now let’s apply everything we’ve learned so far to create some scripts to enhance and refine data in Airtable.

4.1 Call OpenAI for Sentiment Analysis and Update fields in Airtable

Let’s say we want to determine the sentiment of a list of comments. We’ll retrieve the comments from Airtable, use AI to perform sentiment analysis, and update Airtable with the results. Everything should be familiar from earlier sections of this guide, and the code has been modularized for better readability.

// Configuration for Airtable and OpenAI API
const CONFIG = {
  TABLE_NAME: "Shopify Comments",
  API_KEY: "your-openai-api-key-here",
  ENDPOINT: "https://api.openai.com/v1/chat/completions",
};

//  function to fetch records from Airtable table
async function fetchRecords(table) {
  try {
    return await table.selectRecordsAsync({ fields: ["Comments"] }); // Select records asynchronously
  } catch (error) {
    console.error("fetchRecords: ", error.message); // Log fetch errors
    return []; // Return empty array on failure
  }
}

// function to analyze sentiment of a single comment
async function callOpenAI(comment) {
  // setup openAI request
  let prompt = `Analyze the sentiment of this comment: "${comment}"`;

  let messages = [];
  messages.push({ role: "user", content: prompt });

  let body = {
    messages: messages,
    temperature: 0.4,
    max_tokens: 800,
    model: "gpt-4o",
    top_p: 1.0,
    frequency_penalty: 0.0,
    presence_penalty: 0.0,
  };

  const response = await remoteFetchAsync(CONFIG.ENDPOINT, {
    method: "POST",
    headers: {
      Authorization: `Bearer ${CONFIG.API_KEY}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify(body),
  });

  if (!response.ok) throw new Error(`API error: ${response.status}`); // handle errors
  const data = await response.json();
  return data.choices[0].text; // Return processed sentiment
}

// function to analyze sentiments for all records sequentially
async function analyzeSentiments(records) {
  const sentimentResults = []; // array to store results
  for (const record of records) {
    try {
      const comment = record.getCellValue("Comments"); // extract comment from record
      const sentiment = await callOpenAI(comment); // analyze sentiment
      sentimentResults.push({ id: record.id, sentiment }); // add result to array
    } catch (error) {
      console.error(
        `Error analyzing sentiment for record ${record.id}: ${error}`
      ); // Log errors
    }
  }
  return sentimentResults; // return all sentiment analysis results
}

// function to update records in Airtable in batches
async function updateRecords(recordsArr, table) {
  try {
    while (recordsArr.length > 0) {
      const batch = recordsArr.splice(0, 20); // Process in batches of 20
      await table.updateRecordsAsync(
        batch.map((record) => ({
          id: record.id,
          fields: { Sentiment: record.sentiment },
        }))
      );
      console.log(`Updated ${batch.length} records. ${recordsArr.length} remaining.`);
    }
    console.log("All records updated!"); // Confirmation message
  } catch (err) {
    console.error(`Error updating records: ${err.message}`); // Log update errors
  }
}

// main execution function
async function main() {
    try {
        const table = base.getTable(CONFIG.TABLE_NAME); // get table from base
        const records = await fetchRecords(table); // fetch all records from the table
        if (records.length > 0) {
            const recordsWithSentiment = await analyzeSentiments(records); // analyze sentiments of fetched records
            await updateRecords(recordsWithSentiment, table); // update records in Airtable
        } else {
            console.log("No records to process.");
        }
    } catch (error) {
        console.error(`Main function error: ${error.message}`);
    }
}

await main(); // run main function

4.2 Formatting Text and Updating in a New Field

This script capitalizes the first letter of each word in a field and stores it in a new field:

const CONFIG = {
    TABLE_NAME: 'Contacts',
    SOURCE_FIELD: 'Name',
    TARGET_FIELD: 'Formatted Name'
};

// get the table based on table name from the configuration
let table = base.getTable(CONFIG.TABLE_NAME);

// function to capitalize the first letter of each word in a string
function capitalizeWords(str) {
    return str.replace(/\b\w/g, letter => letter.toUpperCase());
}

// main function to process records and update them
async function main() {
    // select all records from the table
    let query = await table.selectRecordsAsync();

    // loop through each record
    for (let record of query.records) {
        // get the current value of the source field
        let name = record.getCellValue(CONFIG.SOURCE_FIELD);

        // capitalize each word in the name
        let formattedName = capitalizeWords(name);

        // update the record with the new formatted name in the target field
        await table.updateRecordAsync(record.id, {
            [CONFIG.TARGET_FIELD]: formattedName
        });
    }
}

// run the main function
main();

4.3 Splitting Text into Multiple Fields

This script takes a multi-line text field and splits it into multiple fields:

const CONFIG = {
    TABLE_NAME: 'Customer Addresses',
    SOURCE_FIELD: 'Full Address',
    TARGET_FIELDS: ['Street Address', 'City', 'State', 'Zip Code', 'Country']
};

// get the table from the base using the configured table name
let table = base.getTable(CONFIG.TABLE_NAME);

// main function to process and update records
async function main() {
    // select all records asynchronously from the table
    let query = await table.selectRecordsAsync();
    
    // iterate over each record in the query
    for (let record of query.records) {
        // retrieve the full address from the current record
        let fullAddress = record.getCellValue(CONFIG.SOURCE_FIELD);
        
        // split the full address into components assuming a common delimiter ', '
        let addressParts = fullAddress.split(', ');

        // prepare the object to hold updates for the record
        let updateObject = {};
        // assign each address component to its respective field, using an empty string if not available
        CONFIG.TARGET_FIELDS.forEach((field, index) => {
            updateObject[field] = addressParts[index] || ''; // use the address part or an empty string if undefined
        });

        // update the current record asynchronously with the new address components
        await table.updateRecordAsync(record.id, updateObject);
    }
}

// execute the main function
main();

Conclusion and Next Steps

In this guide we’ve covered the fundamentals of Airtable scripting, as well as more advanced features that reveal the full capabilities of the scripting extension. With this knowledge, and the code examples provided, you’re well on your way to building powerful scripts that can save you time and frustration, and which are tailored to your exact business needs.

To continue learning more about Airtable scripting we recommend:

1. Experimenting with the mini-projects provided, adapting them to your specific needs and data structure
2. Exploring Airtable's scripting documentation for more scripting examples
3. Learning from the scripting section of Airtable’s community forum
4. Reaching out to us via chat with questions about this guide. We use Airtable daily at Simplescraper and so may be able to help you (or update the guide with more examples) .

Happy scripting!