SHARE
TWEET

Changelog

anayexm Dec 4th, 2016 277 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. // This script records changes to the spreadsheet on a "Changelog" sheet.
  2. // The changelog includes these columns:
  3. // "Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"
  4. // Users are logged by email address.
  5. // Source 1: https://productforums.google.com/d/topic/docs/az365_ypIV0/discussion
  6. // Source 2: https://productforums.google.com/forum/#!topic/docs/AI9OxbOtvWE
  7.  
  8. function onEdit(e) {  
  9.   var changelogSheetName = "Changelog";
  10.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  11.   var cell = SpreadsheetApp.getActiveRange();
  12.   var timestamp = new Date();
  13.   var currentSheet = ss.getActiveSheet();
  14.   var currentSheetName = currentSheet.getName();
  15.   var previousValue = e.oldValue;
  16.   var newValue = cell.getValue();
  17.   var typeChange = "Edit";
  18.  
  19.  
  20.   // if it is the changelog sheet that is being edited, do not record the change to avoid recursion
  21.   if (currentSheetName == changelogSheetName) return;
  22.   var changelogSheet = ss.getSheetByName(changelogSheetName);
  23.  
  24.   if (changelogSheet == null) {
  25.    
  26.     // no changelog sheet found, create it as the last sheet in the spreadsheet
  27.     changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets());
  28.    
  29.     Utilities.sleep(2000); // give time for the new sheet to render before going back
  30.    
  31.     ss.setActiveSheet(currentSheet);    
  32.     changelogSheet.getRange('A1:G1').setBackground('#E0E0E0');
  33.     changelogSheet.appendRow(["Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);
  34.     changelogSheet.deleteColumns(8,19);
  35.     changelogSheet.setFrozenRows(1);
  36.     changelogSheet.setColumnWidth(1, 170);
  37.     changelogSheet.setColumnWidth(7, 170);
  38.     changelogSheet.protect();
  39.   }
  40.  
  41.  
  42.   var user = Session.getEffectiveUser().getEmail();
  43.  
  44.   if (previousValue == null){
  45.     typeChange = "Add";
  46.   } else if (newValue == "") {
  47.     typeChange = "Remove";  
  48.   }
  49.  
  50.   changelogSheet.appendRow([timestamp, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);
  51. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top