The Scripting block is a powerful game changer for Airtable, though you don’t need to be a developer to use it. Before using the scripting block, the most experience I had was writing a couple of Macros in Excel (but even that isn’t necessary knowledge.) Now, I mostly use scripts as complex formulas that can also create or update your records on a base.
I encourage you to get into scripting if you.
- Like building formulas
- Kinda sorta know what variables, arrays, objects are
- Understand how loops work
If you want to brush up on any of these concepts, try Mosh Hamedani’s 1-hour intro to JavaScript. Mosh also has an entire course on JavaScript that, at the time of writing this, is $15 and it’s SO worth it!
Why Use a Script?
The Airtable team has covered this really well here, but adding my thoughts as well.
Scripts pick up where formulas leave off
Formulas can get long and clunky, and are sometimes too rigid for your needs - think not being able to move dates created from formulas in calendar & gantt. Also, some things don’t quite work as you’d like them to without doing a lot of extra work, like using the sum of an entire field. Scripts allow you to get as complex as you need, while giving you the tools to structure/simplify that complexity that you don’t really get with formulas.
Create direct routes instead of using workarounds
Let me start by saying that Airtable is leaps and bounds better than any tool that I’ve used. However, I’ll never stop trying to push the limits on a tool and Airtable is no exception. This has resulted in devising some non-ideal workarounds for creating a list of repeatable tasks, or adding/subtracting from the previous value. But, now you can tackle some of those thorny problems with a handy script!
They’re familiar to Excel Nerds :nerd_face:
I think it’s worthwhile to begin by comparing scripts to Excel Macros, which allow you to “automate repetitive tasks to save you time.” Scripts are extremely powerful and can do a lot more than Macros once you know your way around them. Not only can you manipulate data in your base, you can do things like pull information into your base from other places on the internet and use it contextually.
Your script could be high maintenance :nail_care:t5:
Just as you’ve run into an overbaked, overcomplicated formula, you can also encounter nonsensical scripts. A script’s success relies on its ability to handle changes and edge cases. The more complex the script, the more places where it can go wrong. So, test, test test! There are a lot of tips on best practices out there, though I’d love to know the community’s thoughts on this as well.
Creating your First Script
Creating a script is a fun process in experimentation! Scripting block comes with some great examples to start from, all you have to do is change some of the table and field names. Some examples do rely on basic principles that are good to know your way around. Below I’ll go through some things that made my journey a lot easier when creating scripts.
FWIW: I’ve never taught anyone how to script. These are just my observations that I thought would be helpful to share. Also I’ll try linking to other resources wherever possible. Many awesome people have created great tutorials and guides to help you. When you get lost, just Google.
AutoComplete & In-Block Documentation
The Scripting block has a lot of great features to guide you through building. A key one is Auto Complete. You can start writing and the editor will help you finish a statement or show what available options you have.
You can also access documentation from within the block if you’re unsure about what something means, by hovering over text, or searching in the bottom of the block to get more detailed guidance.
Understanding Await
From what I understand, in order to use information from your base in a script, you first have to call Airtable and wait for Airtable to deliver that information to your script. In that same vein, any time you want to push information into the base, you have to call Airtable and wait for Airtable to deliver that information to the base.
To call Airtable and ask it for something, you need to use Await before the asynchronous function that calls Airtable. You can tell when to use Await because these functions usually end with Async like table.selectRecordsAsync() and table.updateRecordAsync(). Remember, anytime you see Async in a function, put await before it.
Ternary Case
Ternary case allows you to use some shorthand for IF functions. It helps me read my script faster, and to keep things all on one line. Here’s a nice explainer.
Arrow Functions
Arrow functions are also shorthand with the goal of writing less stuff. You’ll see arrow functions in some examples. Mosh explains it here. Arrow functions come in handy with iterations like Array.Map which we’ll cover next.
Checking your work with console.log
Just like complex formulas, it’s rare to get a script right the first time. To figure out where something may not be quite right, you can use console.log() to check your work by outputting the results in the block, but not taking any actions on your base.
Array.Map
I use this in basically every script I use. To pull records into your script, usually you’ve got something like:
let table = base.getTable("Tasks");
let query = await table.selectRecordsAsync();
let records = query.records;
Now, that you’ve loaded the records into the script, you’ll want to get more information about them. In this example, records is actually an array of record objects. To get information from an individual record object (like a value from a specific field), you’ll need to use record.getcellvalue(‘YOUR FIELD NAME HERE’).
But first, you’ll need to isolate individual records.
This is where array.map comes in. To get the cell value from each individual record in records (which is an array of record objects), we’ll want to iterate through each element in records. Iterations are also called loops. Iterating through an array will allow you to do something multiple times on each different element in the array. (Again, people have explained this much better than I have)
Array.map takes the loop one step further, and allows you to iterate through each element in an array, and then create a new array that’s much more helpful and relevant to you. (Quick & dirty, More thorough explanation) The ability to iterate through each record AND create a new array is incredibly helpful, because to update/create multiple records in Airtable, you’ll need to pass an array of records.
Let’s look at Array.map in action
A script that makes use of Array.map is “Creating Tasks from a Template.” This script asks a user to select an event, and then uses a templated list of task records to create a new list of tasks for the selected event.
In the example, we’ve declared the list of templated task records as templateRecords. If we console.log(templateRecords) we can see it’s just an array of Record IDs and Record Names.
output of templateRecords
To get values from the record, we’ll need to iterate through the templateRecords array using Array.map and use record.getcellvalue(‘Field Name’) to extract those values. This is what it looks like in the script:
let createRecords = templateRecords.map( c => ({fields: {
'Name': c.getCellValue('Name'),
'Project': [selectedEvent],
'Hours': c.getCellValue('Hours'),
}});
Notice we’re using an arrow function here. In this function, c represents a single element from the templateRecordsMap array.
Using templateRecords.map(), I’m able to create a new array that is much more helpful to me, because for each record in templateRecords, I’ve created an object that can be used in createRecordsAsync() to update records in my table.
The below example shows the output of templateRecords as well as createRecords, which is the new array we’ve structured by using array.map
Okay, ran out of steam but I’ll definitely make tweaks later and I hope this helps! Have fun and happy scripting! Also, please lmk where I can make improvements.
