Airscript Learn to use Airtable scripts

Generating tasks for projects

If you work on projects, there’s a strong likelihood that you will do similar projects again and again. For example, a software development project will often follow the cycle of:

  • Gather requirements
  • Design
  • Development
  • Test
  • Deploy

And each of these high-level tasks will probably have a consistent set of lower-level tasks.

In this post we’re going to create an Airtable script that creates a set of tasks for a project from a predefined template. Here’s our base set-up:

We’ve got 3 tables:

  • Projects - a place for all of our projects (this table might also contain client details, a project manager, project start and end dates and so on)
  • Tasks - a set of tasks linked to a project (this table might also contain task due dates, people assigned to and other notes)
  • Task Template - A template of tasks that represent what needs to be done on any project

We want the tasks table to end up like this:

with each task from our template linked to a project.

In our script, let’s start by defining all 3 tables:


let projectsT = base.getTable('Projects');
let tasksT = base.getTable('Tasks');
let taskTemplateT = base.getTable('Task template');

We’re going to make use of Airtable’s “button” field type later on, so our next step is to get some input from the user to choose a project to have tasks created against:


let project = await input.recordAsync('Pick a project', projectsT);

It is good practice to check that a project has actually been selected by the user before continuing with other script operations, so let’s wrap the rest of the code in a conditional statement that checks that we do have a project record:


if (project) {
  // we have a project, so do something here
}

The first thing we want to do is give some output to the user, so that they know that the script is doing something:


output.text(`You picked ${project.getCellValueAsString('Name')}`);
output.text(`Creating tasks for ${project.getCellValueAsString('Name')} ... please wait`);

We then want to get the standard tasks from the task template table:


let taskTemplateQ = await taskTemplateT.selectRecordsAsync();

We’re going to use the createRecordsAsync method later on to add the tasks to the Tasks table - see here for a refresher on this. Our next task is to create an empty array to hold the data we’re going to push into the tasks table, so let’s declare that now:


let projectTasks = [];

createRecordsAsync takes an array of objects so we’ll push data into the projectTasks array in the correct format as we loop over the task template records:


for (let task of taskTemplateQ.records) {
    projectTasks.push(
        {
            fields: {
                'Task': task.name,
                'Project': [ {id: project.id} ]
            }
        }
    )
}

Two things to note here:

  • for each task in the task templates table, we’re setting the Task attribute to be the task.name - the value of the primary field in Airtable.
  • Project is a link field, so we need to set this in specific way - linked fields take an array (square brackets) of objects (curly braces) and each object has the attribute id and the value is the record ID of the project selected by the user. As ‘an array of objects’ suggests, we could have multiple objects of type {id: rec123567890}, specifically when the linked field in Airtable is set to link to multiple records in the linked table. But, in our case, we will only link a task to one project, so there will only be one object in the array.

Now that we have built the array, we can pass this to the createRecordsAsync method like this:


await tasksT.createRecordsAsync(projectTasks);

And, finally, let’s tell the user that the script has completed:


output.text('Tasks created!')

Here’s the final script:


let projectsT = base.getTable('Projects');
let tasksT = base.getTable('Tasks');
let taskTemplateT = base.getTable('Task template');

let project = await input.recordAsync('Pick a project', projectsT);
if (project) {
    output.text(`You picked ${project.getCellValueAsString('Name')}`);
    output.text(`Creating tasks for ${project.getCellValueAsString('Name')} ... please wait`);

    let taskTemplateQ = await taskTemplateT.selectRecordsAsync();

    let projectTasks = [];

    for (let task of taskTemplateQ.records) {
        projectTasks.push(
            {
                fields: {
                    'Task': task.name,
                    'Project': [ {id: project.id} ]
                }
            }
        )
    }

    await tasksT.createRecordsAsync(projectTasks);

    output.text('Tasks created!')
}

To make the process a little more robust, we can check if tasks already exist against a project record (as we don’t want to create them twice) with a conditional statement:


if (!project.getCellValue('Tasks')) {
    output.text(`Creating tasks for ${project.getCellValueAsString('Name')} ... please wait`);

    let taskTemplateQ = await taskTemplateT.selectRecordsAsync();

    let projectTasks = [];

    for (let task of taskTemplateQ.records) {
        projectTasks.push(
            {
                fields: {
                    'Task': task.name,
                    'Project': [ {id: project.id} ]
                }
            }
        )
    }

    await tasksT.createRecordsAsync(projectTasks);

    output.text('Tasks created!')        
    
} else {
    output.text('Tasks already exist!!');
}

With the script now working and tested, we can assign it to a button the Projects table so that users can execute the script and select the project to run it against in one go: