1

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.

Improve this question
0

1 Answer 1

Reset to default
4

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.

Improve this answer
5
  • 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, 2014 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, 2014 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, 2014 at 10:58
  • Thanks Vidar, you have been really helpful, that does exactly what I need.
    – Ashleigh
    Mar 3, 2014 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, 2014 at 8:50

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