21

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.

20

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

=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.

  • 5
    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
  • 5
    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

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