Google-sheets – How to update row in Spreadsheet based on respective cell value

google sheetsgoogle-apps-script

I'm about 10 days new to coding in GScripts/Spreadsheets and I've been struggling to write a Spreadsheet function that will change the values of certain cells in certain rows depending on the value of the cell in the last column of it's respective row as it receives updates through a linked form.

In other words, if cell A5 of the incoming row reads "Recovering", I want it to clear cells A2 and A3 and change cell A5 (itself) to "RECOVERED!".

The code I've written so far looks like this:

function statCheck() {
  var ss = SpreadsheetApp.getActive();
  var range = ss.getActiveRange()

  var rowEnd = range.getLastRow();
  var columnEnd = range.getLastColumn();

  for(var i = 2; i <= rowEnd; i++) { //skip 1, ignore column titles

    var time = range.getCell(i, 1);
    var firstname = range.getCell(i, 2);
    var lastname = range.getCell(i, 3);
    var tablet = range.getCell(i, 4);
    var status = range.getCell(i, 5);

    var value = status.getValue();

        if (status.getValue() === "Recovering") {
          firstname.clear();
          lastname.clear();
          status.setValue("RECOVERED!");
        }
  }
}

Which should change the value inside A5 to "RECOVERED!", but for some reason nothing happens after the code runs (no errors either).

If there is a way to do this entirely without Spreadsheet formulas, that's the way I'd like to get this done. Otherwise, any and all help is much appreciated!

Best Answer

ss.getActiveRange() selects the currently selected or edited range. From reading your script I see you meant the entire range of data, which would be ss.getDataRange(). Other than that, the logic is okay.

Other suggestions:

  • Don't use methods when unnecessary, you get time and tablet that are never used.
  • Consider getting all relevant values in one getValues call, and then loop over the resulting JavaScript array, referring to individual cells with range.getCell(..).clear only when actually needed.
  • Consider running the script by a trigger on form submission, making use of the event object. This simplifies things a lot: see below.

function statCheck(e) {
  if (e.values[4] === 'Recovering) {  
    e.range.offset(0, 1, 1, 2).clear();
    e.range.offset(0, 4, 1, 1).setValue('RECOVERED!');
  }
}

The index is 4 instead of 5 because JavaScript index is 0-based.

Related Topic