Google Sheets Script: IF Column A is filled, THEN write in Column E

Question

I have a webform that's plugging entries into Google Sheets, so I can't place a formula within the Sheet's cells, otherwise the form will skip the row. My current script

function fillInTheBlank(e) 
{
  var row=e.range.getRow();  
  e.range.getSheet().getRange(row,5).setFormula('=IF(ISBLANK(A'+ row 
+',1)),"","ready")');
}

IF column A is blank ignore, ELSE fill with "ready".

Note that you can simply .offset from a Range, avoiding the need to backpedal to the sheet and call getRange again. — tehhowch
@I'-'I not more than calling getSheets and then getRange does — tehhowch

tehhowch tehhowch · Accepted Answer · 2018-03-23T02:14:07

You are pretty close, and just need to check the value in code before setting it:

function onEdit(e) {
  var col = e.range.getColumn();
  // check Ax for any truthy value
  if (e.range.offset(0, 1 - col).getValue()) {
    e.range.offset(0, 5 - col, 1, 1).setValue("ready");
  }
}

Note that the above requires no trigger, as it meets the qualification for the simple trigger, onEdit.

If your intent is to bind to the on form submit trigger, you'll need to name it something else (e.g. function addStatus(e)), and install the trigger for it. Note that in general, column A has the timestamp of the submission, and thus you do not need to check it for a value. Rather, if your goal is to simply add "ready" to the column next to the just-added form:

function addReady(e) {
  var numQs = e.range.getNumColumns();
  e.range.offset(0, numQs, 1, 1).setValue("ready");
}

Range offsets are always relative to the upper left cell, so offseting the number of questions and reducing the selection to a single row and column gives us the cell in the same row, adjacent to the questions.

Google Sheets Script: IF Column A is filled, THEN write in Column E

1 Answers