Last active
May 24, 2018 13:36
-
-
Save lolandese/bf05cceba2271552b1f8 to your computer and use it in GitHub Desktop.
Google Sheets script to keep a 'Modified' timestamp and a counter (serial) column.
This file contains hidden or 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
| function onEdit() { | |
| // Set some 'global' variables. | |
| var sheet = SpreadsheetApp.getActiveSheet(); | |
| var cell = sheet.getActiveCell(); | |
| var row = cell.getRow(); | |
| // Create a timestamp in column 'C' with user ID (first 6 characters of mail address) when a row gets modified. | |
| if( cell.getRow() != 1 ) { // Don't process the header row. | |
| var user = Session.getActiveUser().getEmail(); | |
| var date = Utilities.formatDate(new Date(), "GMT+2", "yyyy.MM.dd"); | |
| var time = Utilities.formatDate(new Date(), "GMT+2", "HH:mm:ss"); | |
| SpreadsheetApp.getActiveSheet().getRange('C' + row.toString()).setValue(date + '\n' + time + '\n' + user.substring(0, 6)); | |
| }; | |
| // Keep a counter in cell M1 (just put it somewhere in the header with an initial value of '0'), increment it and use it in | |
| // the first column (if empty) when a row gets modified. | |
| var counter = sheet.getRange("M1").getValue(); | |
| counter++; | |
| if (sheet.getRange(row, 1).getValue() == "") { | |
| sheet.getRange(row,1).setValue(counter); | |
| sheet.getRange("M1").setValue(counter); | |
| } | |
| }; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment