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.

pnuts
  • 17,883
  • 5
  • 55
  • 103
Koen
  • 61
  • 1
  • 1
  • 3

2 Answers2

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.

Vidar S. Ramdal
  • 17,409
  • 11
  • 71
  • 82
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.

guitarthrower
  • 129
  • 3