0

I've been trying to implement a conditional on my spreadsheet, basically a check-sheet with three conditional cells with "Yes" or "No" in them. All I want to achieve (using onEdit) is one all three cells contain "Yes", enter the next column with the date the final Yes was entered. I've managed to create other scripts which work fine, but this one has me stumped.

Thanks

Hyperjase
  • 127
  • 2
  • 21
  • What do you have so far? You haven't provided any information about what has you stumped with this question. – Mogsdad Apr 18 '13 at 16:05

1 Answers1

1

Since the cells can be edited individually, your onEdit will always need to check all of your conditional cells' values, and write the timestamp only when all are "Yes".

function onEdit(event) {
  var conditionalCells = [ "B1", "B2", "B3" ];  // Array of monitored conditionals
  var inList = false;                           // assume edit was outside of the conditionals
  var allYes = true;                            // and that all values are "Yes".
  var sheet = event.source;  // Sheet that was edited
  var cell = event.range.getA1Notation();  // get range description
  // Loop through all conditionals checking their contents.
  // Verify that the edit that triggered onEdit() was in one
  // of our conditional cells, setting inList true if it was.
  for (var i = 0; i < conditionalCells.length && allYes; i++) {
    if (cell == conditionalCells[i]) inList = true;
    allYes = (sheet.getRange(conditionalCells[i]).getValue() == "Yes");
  };
  // If this was our final Yes, record the date.
  // By validating inList, we ensure we record only the first time
  // all conditionals are "Yes".
  if (inList && allYes) sheet.getRange("C1").setValue(new Date());
}
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Apologies for not having provided any script, everything I had tried had failed miserably, and was nowhere near what you gave above, many thanks. Just on that, it appears to cause an error on this line? var sheet = event.source; // Sheet that was edited Error message : TypeError: Cannot read property "source" from undefined. (line 5, file "Code") - Sorry I can't seem to format this correctly as code! – Hyperjase Apr 19 '13 at 07:53
  • The 'event' parameter gets provided when the function is triggered by an edit; if you are running it in the debugger, you have no 'event'. See [this question](http://stackoverflow.com/questions/16089041/how-can-i-test-a-trigger-function-in-gas). – Mogsdad Apr 19 '13 at 09:41
  • Ah yes, thanks, I spotted something too. You assign B1, B2 and B3 in the array, I found a small issue, the columns I need to check are D, E and F, then put the date in the G column, however, the row number is specified - how would I make it work on all rows? Many thanks. – Hyperjase Apr 19 '13 at 10:13
  • You never did go back and add enough information to your question to make it clear what you're looking for - and your comment above is open to interpretation, so start there. You said "three cells", that's three cells. If you meant "Cells in three columns of the same row", or better yet "Cells in columns D,E,F of the same row", the answer would be different. You need to adapt the code; the `event.range` contains the row information, use that to pick D:F of the correct row, and to set the range for `setValue`. The `getRange` method has a version that takes numbers specifying row & column. – Mogsdad Apr 19 '13 at 12:32
  • My apologies for the delay in responding, more pressing matters diverted my attention from this. What I have is a table which contains three Yes/No drop down boxes, which are column D, E & F. There are multiple rows for this as I add data regularly. The code you gave me above worked perfectly for row 2 (row 1 being the header). I'm just trying to achieve it so that when I click on Yes on all of the three columns (D, E & F) column G auto populates the time and date. Does this clarify my requirements better, sorry sometimes I forget to include the more important details! – Hyperjase May 22 '13 at 07:59