Google-sheets – Cell last modified date & user stamp Google Sheets

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-sheets-timestamp

I want the cells of two columns in my Spreadsheet to auto-update with the date (m/d/y) and name of user when cells in a certain range are edited. For instance, Catherine goes to sheet on 3/6/2017 (today) and edits information in either column A or B. Ideally, the current date would update in column C and the user's name in column D of the same row as the edits. I have included an image below detailing this example and highlighted the cells in yellow that should auto-update:

enter image description here

Best Answer

The more robust way to automatically record the user that edited a cell is by using a Google Apps Script on edit installable trigger for the spreadsheet created by each editor.

To help your spreasheet editors to create the instalable trigger you could add an user interface element like a button, menu, dialog or sidebar.

To avoid collisions between triggers, the script should include a condition to leave in case that the edit was made by another user rather than the user who created the trigger. In order to do this, the script could keep the trigger id as a user property.

From gist published by me few moments ago,

NOTE: In order to fit the case mentioned by the OP adopters will have to replace var monColumn = 3; by var monColumn = 2;. Other changes could be necessary to addapt it for other specific cases.

/**
 * Log the timestamp and user email next to the edited cell
 * @param {Event} e Edit event object
 *
 */
function logWhenWho(e){

  // Get timestamp ASAP for the best precision
  var timestamp = new Date();

  // Validate trigger
  var triggerId = PropertiesService.getUserProperties()
                    .getProperty('timestamp_trigger');
  SpreadsheetApp.getActive().toast('triggerUid: ' + e.triggerUid);
  if(e.triggerUid !== triggerId) {
    //console.log('Terminated: Invalid trigger');
    return;
  }

  // Validate edited range

  /* Column of monitored range */
  var monColumn = 3;

  /* Starting row of monitored range */
  var startRow = 2;

  var column = e.range.getColumn();
  var row = e.range.getRow();
  if(column !== monColumn || row < startRow) {
    //console.log('Terminated: invalid range');
    return;
  }
  // Main
  var user = Session.getEffectiveUser();
  var whenWho = [[
    /* When */
    timestamp, 
    /* Who */
     user.getEmail()
  ]];
  e.range.offset(0,1,1,2).setValues(whenWho); 
  //console.log('Terminated: whenWho added');
}

/**
 * Create spreadsheet on edit strigger
 *
 */
function createTrigger() {
  var trigger = ScriptApp.newTrigger('logWhenWho')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onEdit()
    .create();
  var id = trigger.getUniqueId();
  PropertiesService.getUserProperties()
    .setProperty('timestamp_trigger', id);
}
/**
 * Add a menu to manage triggers
 *
 */
function onOpen(e){
  SpreadsheetApp.getUi()
    .createMenu('Log When-Who')
    .addItem('Add trigger', 'createTrigger')
    .addToUi();
}
Related Topic