Gantt Chart

function onEdit(e) {

  if (!e || !e.source || !e.range) {

    Logger.log("Event object is missing required properties.");

    return;

  }

  var sheet = e.source.getActiveSheet();

  var range = e.range;

  // Check if the edited cell is in column A and not in row 1

  if (range.getColumn() == 1 && range.getRow() > 1) {

    var row = range.getRow();

    var checkboxValue = range.getValue();

    // Get the corresponding cells in columns F, G, H, and I

    var cellF = sheet.getRange(row, 6);

    var cellG = sheet.getRange(row, 7);

    var cellH = sheet.getRange(row, 8);

    var cellI = sheet.getRange(row, 9);

    updateCells(checkboxValue, cellF, cellG, cellH, cellI);

  }

}

function runScript() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var lastRow = sheet.getLastRow();

  for (var row = 2; row <= lastRow; row++) { // Start from row 2

    var checkboxValue = sheet.getRange(row, 1).getValue();

    // Get the corresponding cells in columns F, G, H, and I

    var cellF = sheet.getRange(row, 6);

    var cellG = sheet.getRange(row, 7);

    var cellH = sheet.getRange(row, 8);

    var cellI = sheet.getRange(row, 9);

    updateCells(checkboxValue, cellF, cellG, cellH, cellI);

  }

}

function updateCells(checkboxValue, cellF, cellG, cellH, cellI) {

  if (checkboxValue === true) {

    setCellFormatting(cellF, "100%", "green", "white", "bold");

    setCellFormatting(cellG, "", "green", "white", "bold");

    setCellFormatting(cellH, "", "green", "white", "bold");

    setCellFormatting(cellI, "FINISHED", "green", "white", "bold");

    var options = ['100%'];

    var rule = SpreadsheetApp.newDataValidation().requireValueInList(options, true).build();

    cellF.setDataValidation(rule);

    cellF.setValue('100%'); // Set default value

  } else {

    setCellFormatting(cellF, "0%", "red", "white", "bold");

    setCellFormatting(cellG, "", "red", "white", "bold");

    setCellFormatting(cellH, "", "red", "white", "bold");

    setCellFormatting(cellI, "UNFINISHED", "red", "white", "bold");

    var options = ['0%', '25%', '50%', '75%'];

    var rule = SpreadsheetApp.newDataValidation().requireValueInList(options, true).build();

    cellF.setDataValidation(rule);

    cellF.setValue('0%'); // Set default value

  }

}

function setCellFormatting(cell, value, backgroundColor, fontColor, fontWeight) {

  cell.setValue(value);

  cell.setBackground(backgroundColor);

  cell.setFontColor(fontColor);

  cell.setFontWeight(fontWeight);

}

// Run the script initially to check all checkboxes

runScript();