Help

Script Coding - Project/Task Creation Script Example

Topic Labels: Scripting extentions
1347 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Yonatan_Langer
5 - Automation Enthusiast
5 - Automation Enthusiast

Hi everyone, I found this very good script template, and need help with some adjustements please.

“Collaborators” used to be a dependency field, called “Followed By”, but I would like to use this field rather to assign collaborator names, that are stored in a separate sheet called “Collaborators”.

The purpose of the Base is to automatically assign tasks and collaborator names to classes.

Here is the code:

// when a new asset is created,
// there are a certain number of tasks that must be completed
// these tasks are dependent on the type of asset
// Given a list of project and task templates,
// when a new asset is created and has a project template assigned
// we can then create corresponding set of tasks for that asset

// define some of our initial variables
// these are the basic table, field and view names to define to create the script
const project_table_name = ‘Classes’;
const new_project_view_name = ‘New Projects’;
const project_template_link_field_name = ‘Project Template’;

const task_table_name = ‘Tasks’;
const task_project_link_field_name = ‘Classes’;
const task_dependency_field_name = ‘Collaborators’;
const task_primary_field_name = ‘Task Name’;

const task_to_template_link_field_name = ‘_task_template_id’; // this is used to assist with creating the links between tasks after they are created. We don’t need to maintain a true linked record relationship, but temporarily storing the template’s ID is helpful

const project_template_table_name = ‘Project Templates’;
const task_template_table_name = ‘Task Templates’;
const proj_temp_task_temp_link_field_name = ‘Tasks’;
const task_temp_dependency_field_name = ‘Collaborators’;
const task_temp_primary_field_name = ‘Task Name’;

/********************************************************/
output.markdown(‘# Creating tasks for new assets and assigning dependencies’);

// create our table objects
const project_table = base.getTable(project_table_name);
const task_table = base.getTable(task_table_name);
const project_temp_table = base.getTable(project_template_table_name);
const task_temp_table = base.getTable(task_template_table_name);

// get all new projects that have been assigned a template
// but do not yet have tasks
const new_project_view = project_table.getView(new_project_view_name);
const new_project_results = await new_project_view.selectRecordsAsync();

// pull all of our project templates and all task templates
const project_temp_results = await project_temp_table.selectRecordsAsync()
const task_temp_results = await task_temp_table.selectRecordsAsync();

// build a map of projects to tasks
output.markdown(‘### Setting up’);
output.markdown(‘Building map of project templates and task templates’);
var project_task_temp_map = {};
for (let r of project_temp_results.records) {
let temp_tasks = r.getCellValue(proj_temp_task_temp_link_field_name);

project_task_temp_map[r.id] = temp_tasks.map((t)=>{
    return t.id;
}) 

}
output.inspect(project_task_temp_map);

// also build a map of task template to task template so we can resolve dependencies
output.markdown(‘Creating map of templated tasks to dependent tasks’);
var task_temp_dependency_map = {};
for (let r of task_temp_results.records){
let next_task = r.getCellValue(task_temp_dependency_field_name);
if(next_task !== null) {
task_temp_dependency_map[r.id] = next_task[0].id;
} else {
task_temp_dependency_map[r.id] = null;
}
}
output.inspect(task_temp_dependency_map);

// for each new project
// get the set of tasks and create the creation payloads
// we will need to do a second pass of all of these records to then update
// the tasks with the corresponding dependencies
//
// THIS IS THE PART OF THE SCRIPT WHERE YOU ASSIGN WHAT DATA YOU WANT IN YOUR NEWLY CREATED TASKS
//
output.markdown(‘### Creating new tasks’)
output.markdown(Found **${new_project_results.records.length}** projects which need task assignment)
var payloads = ;
for(let r of new_project_results.records){
// there should only ever be one project template linked
// so just take the first one
let p_id = r.getCellValue(project_template_link_field_name)[0].id;

let task_temp_ids = project_task_temp_map[p_id];
let task_temps = task_temp_ids.map((i)=>{
    return task_temp_results.getRecord(i);
});
for(let t of task_temps) {

    payloads.push({
        fields: {
            [task_project_link_field_name]: [{id: r.id}],
            [task_primary_field_name]: t.getCellValueAsString(task_temp_primary_field_name),
            
            [task_to_template_link_field_name]: t.id
        }
    });
}

}

// create all of the new tasks
// we should hold on to the created IDs
output.markdown(Creating **${payloads.length}** tasks across these projects);
var new_tasks = ;
while(payloads.length > 0){
let n = await task_table.createRecordsAsync(payloads.slice(0,50));
new_tasks = […new_tasks, …n];
payloads = payloads.slice(50);
}

output.markdown(‘### Creating dependencies between new tasks’);
output.markdown(‘Pulling newly created tasks’)
// refetch these tasks so that we can update the dependencies
const task_results = await task_table.selectRecordsAsync({
fields: [
task_primary_field_name,
task_project_link_field_name,
task_to_template_link_field_name,
task_dependency_field_name
]
});

// pull out only the newly created tasks
const tasks_to_update = new_tasks.map((t)=>{
return task_results.getRecord(t);
});

output.markdown(‘Creating map of new tasks to templated task ids to resolve dependencies’)
// create a map of new task to templated task ids
// and templated tasks to new task ids
// group them by project since any given project may be using the same set of templated tasks
var new_task_to_template_map = {};
for(var r of tasks_to_update){
let p = r.getCellValue(task_project_link_field_name)[0].id;
let temp_t = r.getCellValue(task_to_template_link_field_name);
if(new_task_to_template_map[p] === undefined){
new_task_to_template_map[p] = {
task_to_template: {},
template_to_task: {}
};
}
new_task_to_template_map[p].task_to_template[r.id] = temp_t;
new_task_to_template_map[p].template_to_task[temp_t] = r.id;
}
output.inspect(new_task_to_template_map);

// now go back through the tasks one more time and actually build out the payloads to establish links
// not all tasks will have a dependent task
// we can filter these out afterwards
payloads = tasks_to_update.map((r)=>{
let p = r.getCellValue(task_project_link_field_name)[0].id;
let temp_task = r.getCellValue(task_to_template_link_field_name);
let temp_task_dependency = task_temp_dependency_map[temp_task];
let dependent_task_id = new_task_to_template_map[p].template_to_task[temp_task_dependency];
if(dependent_task_id === undefined) {
return undefined;
}

return {
    id: r.id,
    fields: {
        [task_dependency_field_name]: [{id: dependent_task_id}]
    }
}

}).filter((r)=>{
return r !== undefined;
});
output.inspect(payloads);

output.markdown(Updating **${payloads.length}** tasks with required dependencies);
while(payloads.length > 0) {
await task_table.updateRecordsAsync(payloads.slice(0,50));
payloads = payloads.slice(50);
}
output.markdown(### Done);

2 Replies 2
Yonatan_Langer
5 - Automation Enthusiast
5 - Automation Enthusiast

Here is a link to the Base, that also has the code and the new sheets that I want to use. I would appreciate if someone could have a look, and try to fix that the Collaborator field also gets field when tasks are being created; Sign up - Airtable

Yonatan_Langer
5 - Automation Enthusiast
5 - Automation Enthusiast

@Giovanni_Briggs any chance you could help?