What is a Spreadsheet: Introduction to Programming with Google Sheets
https://www.youtube.com/watch?v=wpUiF52HWgo&ab_channel=TimothyJames
What Is a Gantt Chart, & How Does It Work? | Coffee with Brett
https://youtu.be/h2fA-DtQMj8
Freeze Row 1
Center A1- I12 (or number of rows)
Center/Bold/Middle Align Text in Row 1
Use Dates in the Magenta Area
Add Checkboxes in Cells A1-A12 (or number of rows)
Change Colors of Rows Based on Stage
=(ABS(D2 - C2)+1)
Here is the code . . .
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();
Once you add the code, save it, and run it. Only the cells in Row 2-13 in columns F-I will change color, PROGRESS will be set to '0%' and STAUS will change to 'UNFINISHED'
Now test the code. If you click the checkbox, the PROGRESS with change to '100%' and the STATUS with change to 'FINISHED'. Like this . . .