Skip to main content

var trigger

function myFunction() {

/**
* 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 = 2;

/* 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 trigger
*
*/
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();
}
}