20

How can I automatically set "last updated" cell in row Google Docs Spreadsheets ?

I want to create a column where the cells' value will be automatically set to when that row was last amended. Is this possible? How do I do this / what option do I use to set this?

I see a workaround for MS Excel but I think Google Docs should have something similar, right? (fingers crossed)

We have multiple people editing a large sheet. We can see revision history, but it would be nicer if we had a column of "last updated" dates as well.

19

You can try adding a Google Apps Script to capture when a cell is edited and add a timestamp to a different cell. Here's a previous answer that is similar: Google Spreadsheet Timestamp?

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);
   };
 };
}
13

=iferror(A1+B1+C1+D1+J1+K1+L1+"x",today()) whenever the cells in the row changes the formula re-calculates. with the "x" in the equation it guarantees an error. which in return, returns the date.

  • 4
    That's an interesting kluge. – ale Oct 14 '16 at 21:55
  • 1
    The most beautiful solution I've ever seen :) – mafonya Apr 6 '17 at 19:58
  • 2
    As far as I can tell, this doesn't actually work. To be clear, it works, but it's not updated for specific rows. When I put this in and drag it through multiple rows, then edit anything anywhere on the sheet, all of the rows are recalculated and updated. – nhgrif May 18 '17 at 23:23
  • 1
    Wow! It worked for me. Here is a shortened version: =iferror(SUM(A2:O2)+"x",today()) and dragging it automatically updated row numbers. – Ilya Evdokimov Oct 5 '17 at 5:58
1

This is my script, put in the last column this information:

  1. Insert into the Cell (in the last cell = lastcolumn for the row that you modify) - Date in GMT -3, if you want to put the same in other TZ just modify the -3 for you concurrent timezone. If you want to put the date automatically in your default TZ, just modify this:

    var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm");
    to
    var date = Utilities.formatDate(new Date());
    
  2. Insert a Comment with the Cell+User who modify it.

  3. Appear a popup when you update something. Only appear to the ActiveUser.

    function onEdit(event)
    {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var actSht = event.source.getActiveSheet();
        var actRng = event.source.getActiveRange();
    
        var index = actRng.getRowIndex();
        var dateCol = actSht.getLastColumn();
        var lastCell = actSht.getRange(index,dateCol);
        var date = Utilities.formatDate(new Date(), "GMT-3", "dd/MM/yyyy HH:mm");
    
        lastCell.setValue(date);
        lastCell.setComment("Ultima Modificacion: " + actRng.getA1Notation() +' por '+Session.getActiveUser());
        ss.toast('Ultima Modificacion = '+ actRng.getA1Notation()+' por '+Session.getActiveUser());
    }
    
1

I've created a function that write a new Date when the referenced cell is modified. In this case the cell value is TRUE or FALSE but you can edit it to any value.

function WhenUpdated(cell1) {
  if(cell1){
    return new Date();
  }
  else return null;
}
1

Here's a version that finds the column using the header, rather than hardcoding the column number, and uses a custom time zone:

function updateModified(options) {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (options.sheetName && sheet.getName() != options.sheetName) {
    return;
  }

  var modifiedColumnName = options.modifiedColumnName || 'Modified';
  var header = sheet.getDataRange().offset(0, 0, 1).getValues()[0];
  var modifiedColumn;
  for (var i=0; i < header.length; i++) {
    if (header[i].trim() == modifiedColumnName) {
      modifiedColumn = i + 1;
      break;
    }
  }
  if (typeof modifiedColumn != 'number') {
    throw new Error("Can't find column with name: " + modifiedColumnName);
  }

  var activeCell = sheet.getActiveCell();
  if (activeCell.getColumn() == modifiedColumn) {
    return;
  }

  var modifiedCell = sheet.getRange(activeCell.getRow(), modifiedColumn);
  var formattedDate = Utilities.formatDate(new Date(), "America/Los_Angeles", "M/dd/yy HH:mm");
  modifiedCell.setValue(formattedDate);
}

function onEdit() {
  updateModified({sheetName: 'Jobs'});
}

To use UTC, just change the time zone to UTC.

-4

Simply type "Last updated:" in one cell and type "=now()" in the cell next to it! That should do the job!

  • 1
    This will only work per-cell, not per row as stated in the question – 1owk3y Apr 26 '16 at 0:42

protected by Community Dec 18 '18 at 22:57

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

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