Created
August 23, 2018 17:08
-
-
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* 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(); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Thanks for the code.
How can we add hour and minute and also arrange the target cell to write the date and time?