Date stamp when editing rows in spreadsheet

Question

I have a spreadsheet which needs date stamp for each row as soon as there are changes being made in cells within that row. For example, I change something on cell B14 or E14 or G14 and today's date appears on K14. Obviously, I need to be able to choose range of rows and columns which are going to be affected.

However, the complicated bit in my case is that I need today's date to appear ONLY when I add or change information in the cell. If I just delete information from the cell I need the date to stay the same as before deleting info.

P.S. The file is an excel spreadsheet but it will be used on Google Drive.

Answer 1

This answer is if you go straight to a Google Sheet.

I think this will help in getting you going in the right direction. I'm unclear if you're saying that fields you'll be monitoring are sprinkled around the sheet, or if the non-adjacent cells you referenced were just examples inside a range. If you have cells sprinkled around, you will likely have to make multiple "watch range"s as I have noted in the code, and check that the cell being edited is inside at least one range, otherwise return out of the function.

I want to note that I didn't go to the extra length to make this work for supporting deleting all values from a multi-cell range.

Also, note that you will have to go into Tools -> Script Editor inside the Google Sheet, and then Resources -> Triggers (menu may be different depending what you've done in there before) and add an "onEdit()" trigger to the Sheet.

Then, your function will go something like this

function onEdit(e){
  var DateCol = "K";
  var DeletedColNote = "L";
  var curDate = Utilities.formatDate(new Date(), "GMT-5", "MM/dd/yyyy") 
  var editRange = e.range;
  var rowIndex = editRange.getRowIndex();
  var colIndex = editRange.getColumnIndex();

  // May need to set up multiple ranges like this and check all ranges if
  // checked fields are not in adjacent cells
  var watchRange = { // B2:G20
    top : 2,         // start row
    bottom : 20,     // end row
    left : 2,        // start col
    right : 7        // end col
  };
  // Exit if we're out of range
  if (rowIndex < watchRange.top || rowIndex > watchRange.bottom) return;
  if (colIndex < watchRange.left || colIndex > watchRange.right) return;

  var currentValue = editRange.getValue();
  currentValue = currentValue.replace(/^\s+|\s+$/g,""); //"trim" kludge

  if (currentValue.length == 0)
  {
    // Set a column to show when data was deleted
    SpreadsheetApp.getActiveSheet().getRange(DeletedColNote + rowIndex).setValue("Deleted: " + curDate);
  }
  else
  {
    // Set a column to show last edit date
    SpreadsheetApp.getActiveSheet().getRange(DateCol + rowIndex).setValue("Edited: " + curDate);
    SpreadsheetApp.getActiveSheet().getRange(DeletedColNote + rowIndex).setValue("");
  }
}

Answer 2

Unfortunately, VBA does not port to Google Sheets, but if the Google Sheets requirement is waived, it is quite simple to do with VBA.

Attach this code to the WorkSheet_Change event for the worksheet of interest ...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngToMark As Range
' define the range you want to track changes for
    Set RngToMark = ActiveSheet.Range("A1:G30")
' make sure the change occurred inside the range
    If Intersect(Target, RngToMark) Is Nothing Then Exit Sub
' ignore deleting the contents
    If Target.Value = "" Then Exit Sub
' mark the row as changed
    ActiveSheet.Range("K" & Target.Row).Value = Format(Now(), "MMM-DD-YYYY")

End Sub

To get to the correct location to insert this ...

1. In VBEditor, double click on the worksheet name under "Microsoft Excel Objects"
2. Then pick Worksheet from the left hand drop down
3. Then pick Change from the right hand drop down