5

I have a Google Spreadsheets that is used by different people to add tasks for my team. Here's an example. People can add tasks to column B and I will assign them to the right people in the team, filling in the other columns. The first field I'm capturing is the date that a new task is logged. I'd like to automate this so that when people add a new task starting in column B, column A gets updated with the date that the new line is created.

I've found scripts that will update the timestamp in column A based on changed in column B, but I only want to log the creation date. In other words, when there is already an entry in column B and it gets updated, the date in column A should not change. Only when column B changes from an empty cell to a new value should column A on that row be updated with the date.

In the above example, if I change the task on line 2, the date in column A should not change. If I add a new task in cell B3, it should add the current date in cell A3.

Improve this question
4

You could add a trigger that runs when new rows are added to the spreadsheet:

function onModification(event) {
  var sheet = event.source.getActiveSheet();
  if (event.changeType == "EDIT") { // A cell is edited. Other values include "INSERT_ROW" (when a new row is inserted)
    fillEmptyRows(sheet);
  }
}

function fillEmptyRows(sheet) {
    var range = sheet.getRange(1, 1, sheet.getMaxRows(), 1);
    var firstColumnValues = range.getValues();
    for (var i = 0; i < firstColumnValues.length; i++) {
      if (firstColumnValues[i][0] == "") { // Column 0 is A
         firstColumnValues[i][0] = new Date();
      }
    }
    range.setValues(firstColumnValues);
}

You need to register this trigger manually: Click Resources -> Triggers -> Add new trigger, select the onModification method and select on modification from the right-most dropdown menu.

When a cell is edited, the script will fill all blank cells in column A with the current timestamp.

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

Improve this answer
4
  • Thank you for the suggestion. This could work if I can get my users to create one line at a time, which might be a challenge. The "Add 20 more rows at the bottom" option that Google adds to a spreadsheet at the end is very enticing to most of them. I am still interested in hearing other solutions as well, should anyone think of alternatives. – Koen Nov 6 '13 at 14:53
  • 2
    The script looks through the entire sheet, looking for rows that have a blank first column. So the script should work even when 20 lines are added at once. I just tested it, and it works as expected. It might take a couple of seconds, though, before you see the results. – Vidar S. Ramdal Nov 6 '13 at 15:16
  • While it does work, it adds the date when the lines were added. The problem is that I'm less interested in when the line was created and more in when a new entry was made. Going back to the example sheet, I'd prefer to capture the date when column B receives a new entry over the date when the line was inserted. – Koen Nov 16 '13 at 20:20
  • 2
    OK, if so, INSERT_ROW can be replaced with EDIT. I have updated the answer and the example spreadsheet to reflect this. – Vidar S. Ramdal Dec 19 '13 at 11:59
1

I would consider adding a form to the spreadsheet, and having your users enter new requests via the form. This will timestamp each entry and then you do the assignment. No code needed and very easy to set up.

More information on how to set up a form here.

Improve this answer
1
  • 1
    Thanks. I had considered using a form, but would prefer not to go down that route. Most of our users need to log multiple lines and find forms to be a cumbersome way of doing this. They prefer getting access to the spreadsheet for quick logging. – Koen Nov 16 '13 at 20:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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