This question already has an answer here:

We have a barcode scanner that we use to track student assignments. I am trying to create a Google Spreadsheet that tracks the assignments for each subject.

I have been using the following script:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  if( s.getName() == "Sheet1" ) { //checks that we're on the correct sheet
    var r = s.getActiveCell();
    if( r.getColumn() == 13 ) { //checks the column
      var nextCell = r.offset(0, 1);
      if( nextCell.getValue() === '' ) //is empty?
        var time = new Date();
        time = Utilities.formatDate(time, "GMT", "HH:mm:ss");
        nextCell.setValue(time);
      }
    }
  }
}

However I do not know how to modify the script so that it works in multiple columns on the same spreadsheet. For example a subject may have 2 assignments: Assignment 1 and assignment 2

For assignment 1 I need to scan into column A and have the date stamp added to column B.

For assignment 2 I would scan into column D on the same spreadsheet and have the date stamp added to column E.

marked as duplicate by ale, jonsca Jan 27 '16 at 0:04

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

You're on the right track. You're missing a { on line 7. When that's fixed, you simply need to add a check for column D also:

function onEdit(e) {
  if(e.source.getActiveSheet().getName() == "Sheet1" ) {
    var col = e.source.getActiveCell().getColumn();
    if(col == 1 || col == 4) {
      var nCell = e.range.offset(0,1);   
      if(nCell.getValue() == "" && e.value != "") {
        var time = Utilities.formatDate(new Date(), "GMT+1", "HH:mm:ss");
        nCell.setValue(time);
      } else if(nCell.getValue() != "" && e.value == "") { 
        nCell.setValue("");
      }
    }
  }    
}

I have set up an example spreadsheet to demonstrate the code, feel free to play with it.

  • Thanks for that. That does work! The next issue I have, is this is actually meant to be a time and date record for when the assignment is received by us. The next issue I have, is because this is tied to an editing function, if I wish to delete the time stamp or clear the cells contents, the time stamp re-appears. Do you know if there is a way of over-riding this? – Ashleigh Feb 28 '14 at 4:38
  • So you don't want the date to update when the active cell is edited again? And you want the date cell to be cleared when the active cell is cleared? Sure. I have updated the code and the spreadsheet to do that. Strictly speaking, maybe you should edit your question to include those two requirements. – Vidar S. Ramdal Feb 28 '14 at 7:50
  • 1
    I've edited my answer to include a much prettier version of the code. It still works the same as the previous version. Note that I'm changing the spreadsheet permissions, so to play with it, you must save a copy of it to your own Google Drive. – Vidar S. Ramdal Feb 28 '14 at 10:58
  • Thanks Vidar, you have been really helpful, that does exactly what I need. – Ashleigh Mar 3 '14 at 5:58
  • Great! If you feel this answers your question, you could Accept the answer by clicking on the check mark next to the vote number. – Vidar S. Ramdal Mar 3 '14 at 8:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.