Skip to content

Instantly share code, notes, and snippets.

@rubenrivera
Created Aug 23, 2018
Embed
What would you like to do?
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

This comment has been minimized.

Copy link

@hknerdr 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