Skip to content

Instantly share code, notes, and snippets.

@rubenrivera
Created August 23, 2018 17:08
Show Gist options
  • Save rubenrivera/00451dedf30e4cadb391d96b055b07cb to your computer and use it in GitHub Desktop.
Save rubenrivera/00451dedf30e4cadb391d96b055b07cb to your computer and use it in GitHub Desktop.
Google Apps Script bounded to spreadsheet project that logs the timestamp and user email next to the edited cell for an specified range. It assumens that the spreadsheet has only one sheet.
/**
* 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();
}
@hknerdr
Copy link

hknerdr commented Feb 2, 2019

Thanks for the code.

How can we add hour and minute and also arrange the target cell to write the date and time?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment