Google Sheets Timestamp – Automatically Set ‘Last Updated’ Cell

google sheetsgoogle-apps-scriptgoogle-sheets-timestamp

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.

Best Answer

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

Related Solutions

Google Sheets – How to Set Conditional Formatting Based on Another Cell’s Value

Complex conditional formatting can be achieved in Google Spreadsheets using Google Apps Script. For example, you could write a function that changes the background colour of an entire row based on the value in one of its cells, something that I do not think is possible with the "Change color with rules" menu. You would probably want to set triggers for this function such as "On Edit", "On Open" and "On Form Submit".

Documentation for setBackgroundRGB() function

UPDATE: Here is a Google Apps Script example of changing the background color of an entire row based on the value in column A. If the value is positive, use green. If empty, white. Otherwise, red. See the results in this public Google Spreadsheet. (You will have to be signed in for the script to run, but without signing in you can still see results).

function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var dataRange = sheet.getRange(r, 1, 1, 3);

  var data = dataRange.getValues();
  var row = data[0];

  if(row[0] === ""){
    dataRange.setBackgroundRGB(255, 255, 255);
  }else if(row[0] > 0){
    dataRange.setBackgroundRGB(192, 255, 192);
  }else{
    dataRange.setBackgroundRGB(255, 192, 192);
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 2) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}

Google Sheets Shortcuts – Keyboard Shortcut for Dropdown Selection

The new version of Google Sheets makes it very simple: hit the Enter key while you're in the cell. Boom. Dropdown list.

Best Answer

Related Solutions

Google Sheets – How to Set Conditional Formatting Based on Another Cell’s Value

Google Sheets Shortcuts – Keyboard Shortcut for Dropdown Selection

Related Topic