Gantt chart might be one of the simplest project management tools out there. If you have attempted to create Gantt charts in Excel certainly using the Airtable Gantt app will be a huge improvement. It presents visually the diagram based on the tasks from the selected Airtable view. It also allows adjusting task length and placement by dragging the elements on the canvas. The most crucial function is the ability to show dependency between tasks and create a critical path to show you if one task starts before another.
With big-scale projects containing multiple tasks, adjusting things by hand is not the best option neither is manually changing start and end dates. Airtable does not allow for circular references so that won’t help either. This problem can be easily solved with a bit of code in the Airtable “Scripting” block. Relax, we are no-code friendly so we have written this code for you.
To start the script in-line you need to have the first starting date for the first task of the project and the duration of all tasks. Starting from there, the script will set the end date for the first task and follow on to each successor task, and set their starting and ending dates.
The script excludes Saturdays and Sundays automatically. If the duration of your task is 5 days but the starting date falls on Thursday, the duration of the task will be extended by 2 days to adjust for the weekend. If you prefer to work on weekends — do reach out to us and we will happily adjust the script for you!
Script Code
//Copyright © 2020 by Business Automated
//Update dates of tasks in Gantt chart in Airtable. To be used together with the Airtable Gantt and Scripting block.
// Select the To Do table and records
let table = base.getTable("Table 1"); // <== change here the Table name
let toDoRecords = await table.selectRecordsAsync();
let nameOfStartDate = "Start Date"; // <== change here the to the column name with start date
let nameOfEndDate = "End Date"; // <== change here the to the column name with end date
let nameOfDuration = "Duration" ; // <== change here the to the column name with duration
let nameOfSuccessor = "Successor Task" ; // <== change here the to the column name with successor task
// Prompt the user to pick a record
// If this script is run from a button field, this will use the button's record instead.
let record = await input.recordAsync('Select the start record', table);
//check for no empty record
if (record) {
// Customize this section to handle the selected record
// You can use record.getCellValue("Field name") to access
// cell values from the record
output.text(`You selected this record: ${record.name}`);
} else {
output.text('No record was selected');
return;
}
// pick up data from the starting record
let startDate = record.getCellValue(nameOfStartDate);
let endDate = new Date(startDate);
endDate.setDate(endDate.getDate() - 1);
// exit on start date
if (startDate == null){
output.text("No start Date - exiting the script");
return;
}
//creat Hash table for faster updates vs updates in the database
let ganttHashTable = {};
/**
* @param {DetailedToDoTable_Record} record
*/
function createGanttHashTable(record){
ganttHashTable[record.id] = {
"Date Start": record.getCellValue(nameOfStartDate),
"Date End": record.getCellValue(nameOfEndDate),
"Duration": record.getCellValue(nameOfDuration),
"Successors": record.getCellValue(nameOfSuccessor)
}
if(ganttHashTable[record.id]["Successors"]){
for (let successor of ganttHashTable[record.id]["Successors"]){
let newRecord = toDoRecords.getRecord(successor.id);
createGanttHashTable(newRecord);
//console.log(record);
}
}
}
createGanttHashTable(record);
//console.log(Object.keys(ganttHashTable).length);
//declare main update fuction which will be use recurisviely
/**
* @param {string | number | Date} endDate
* @param {Record} record
* @param {string} level
*/
async function updateDates (endDate, record, level){
//refresh
let recordNew = toDoRecords.getRecord(record.id);
level = level + "=";
//convert end date to start
let newStartDate = new Date();
let newEndDate = new Date(endDate);
let daysToAdd = ganttHashTable[recordNew.id]["Duration"];
//console.log("Dats to add ===" + daysToAdd);
for (let day = 0 ; day newStartDate.getTime()){
newEndDate = new Date(currentEndDate);
} else {
ganttHashTable[recordNew.id] = {
"Date Start": newStartDate,
"Date End": newEndDate,
"Duration": ganttHashTable[recordNew.id]["Duration"],
"Successors": ganttHashTable[recordNew.id]["Successors"]
}
//update records Async in Airtable
};
let successorRecords = ganttHashTable[recordNew.id]["Successors"];
//if successor Record exist iterate over the successors
if (successorRecords){
for (let successorRecord of successorRecords){
//console.log(record);
//adding fuctions and properties to the object
let newRecord = toDoRecords.getRecord(successorRecord.id);
//console.log(record);
//output.text(level+" Successor task: " + record.getCellValue("Name"));
//pass current event end date and successor record
await updateDates(newEndDate, newRecord, level);
}
}
};
//adjust start for the starting record to be similar in format(-1) to "end date" of predecessor
let level = "";
await updateDates(endDate,record, level);
let updateArray = []
for (let [key,value] of Object.entries(ganttHashTable)){
updateArray.push({
"id": key,
"fields": {
[nameOfStartDate]: value["Date Start"],
[nameOfEndDate] : value["Date End"]
}
})
}
// batch update of exisitng records
let updateRecordsLenght = updateArray.length
while (updateArray.length > 0) {
await table.updateRecordsAsync(updateArray.slice(0, 50));
updateArray = updateArray.slice(50);
output.text(`Updated time entries for ${updateRecordsLenght-updateArray.length}/${updateRecordsLenght} task`);
}