jordan rhea header

Instant Feedback

September 15th, 2017

Providing feedback is an important part of teaching. However, it can be hard to manage when you have a classroom full of students requiring your attention.

Teachers are constantly finding ways to manage the attention of students.

It is great when you can use technology to alleviate some of those problems.

function onEdit(e) {
  var range = e.range;

  if (
    range.getColumn() >= 2 &&
    range.getColumn() <= 5 &&
    range.getRow() >= 2 &&
    range.getRow() <= 21 &&
    range.getA1Notation() !== 'B2'
  ) {
    //test to see if any of the cells should be formulas
    if (range.getFormula() === '' && range.getValue() !== '' && range.getColumn() !== 3) {
      range.setNote('Not a formula').setBackground('red');
    } else {
      range.clearNote().setBackground(null);
      isStepComplete(range);
    }
  } else {
    isStepComplete(range);
  }
}

function isStepComplete(range) {
  var steps = [
    { stepCell: 'G1', targetCell: 'B2' },
    { stepCell: 'G2', targetCell: 'D2' },
    { stepCell: 'G3', targetCell: 'E2' },
    { stepCell: 'G4', targetCell: 'B3' },
    { stepCell: 'G5', targetCell: 'E21' },
  ];

  for (var i = 0; i < steps.length; i++) {
    var cell = SpreadsheetApp.getActiveSpreadsheet().getRange(steps[i].targetCell);

    if (cell.getValue() !== '') {
      markComplete(steps[i].stepCell);
    } else {
      markIncomplete(steps[i].stepCell);
    }
  }
}

function markComplete(cell) {
  SpreadsheetApp.getActiveSheet()
    .getRange(cell)
    .setBackground('lightgreen')
    .setFontLine('line-through');
}

function markIncomplete(cell) {
  SpreadsheetApp.getActiveSheet()
    .getRange(cell)
    .setBackground(null)
    .setFontLine(null);
}

jordan rhea wearing a hoodie

Written by jordan rhea a developer focused on building solutions in the cloud

You should follow me on Twitter, or Github, or even on LinkedIn

© 2022 jordan rhea