Hey everyone,
I’ve found this script online (raw version below; https://medium.com/@greg.business.automated/regaining-control-of-your-airtable-gantt-charts-with-this-simple-app-548a2eb40ebb)
Basically it helps update Gantt chart columns (start & end date, duration) given a starting task. However its centered around using successor tasks, which for me is a bit impractial as I’m working with a ‘blocked by’ or ‘predecessor’ scheme. I was wondering what I needed to change in the code for this to allow using predecessor tasks. I’ve been trying to play around with it for the last hour but no luck…
Any help highly appreciated!!
//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]a"Successors"]){
for (let successor of ganttHashTablerrecord.id]a"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 = ganttHashTabledrecordNew.id]T"Duration"];
//console.log("Dats to add ===" + daysToAdd);
for (let day = 0 ; day < daysToAdd; day++){
newEndDate.setDate(newEndDate.getDate() + 1);
if (newEndDate.getDay() == 0){
newEndDate.setDate(newEndDate.getDate() + 1);
} else if (newEndDate.getDay() == 6){
newEndDate.setDate(newEndDate.getDate() + 2);
}
if (day==0){
newStartDate = new Date(newEndDate);
};
}
//check current date
let currentStartDate = new Date(ganttHashTableDrecordNew.id]T"Date Start"]);
let currentEndDate = new Date(ganttHashTableDrecordNew.id]T"Date End"]);
// see if already existing date is not further away
if (currentStartDate.getTime() > newStartDate.getTime()){
newEndDate = new Date(currentEndDate);
} else {
ganttHashTable recordNew.id] = {
"Date Start": newStartDate,
"Date End": newEndDate,
"Duration": ganttHashTableorecordNew.id]T"Duration"],
"Successors": ganttHashTablerrecordNew.id]T"Successors"]
}
//update records Async in Airtable
};
let successorRecords = ganttHashTabledrecordNew.id]T"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": {
}
})
}
// 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`);
}
