On my day job, I always make trackers on Google Spreadsheets to monitor the progress of each feature we committed per iteration.

One of the issues I always face in tracking everything is traceability, or the ability to look back at the past activities of any item under supervision.

Traceability is important in investigating the root cause of any other problems. I simply cannot trust my faulty memory in recollecting everything I did.

If you frequently ask yourself similar questions from the following, this issue is most probably familiar to you.

1. What items were accomplished yesterday? last week?
2. When did I last update this item?

To solve this problem, I tried playing around with Google Apps Scripts. Good thing my basic JS background somehow got me through with this.

Changelog

I made a simple script to monitor all the changes incurred in one of my trackers made in Google spreadsheet. Resources used are indicated in the comments.

Pastebin - Changelog

Let me discuss the script in detail:

I wanted to track the following fields in my Changelog:

• Timestamp when the change occurred
• Sheet - My tracker has multiple sheets
• Cell
• Type - Add, Remove, or Edit value
• Old Value
• New Value
• User who modified the tracker

I set the essential variables here, including the old value as previousValue and the new value as newValue.

function onEdit(e) {  
  var changelogSheetName = "Changelog";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = SpreadsheetApp.getActiveRange();
  var timestamp = new Date();
  var currentSheet = ss.getActiveSheet();
  var currentSheetName = currentSheet.getName();
  var previousValue = e.oldValue;
  var newValue = cell.getValue(); 
  var typeChange = "Edit";
...

The script only tracks changes in all sheets except the Changelog sheet. This line of code is essential to avoid recursion.

  if (currentSheetName == changelogSheetName) return;

This simply creates the Changelog sheet if it doesn't exist yet.

 var changelogSheet = ss.getSheetByName(changelogSheetName);

  if (changelogSheet == null) {
    changelogSheet = ss.insertSheet(changelogSheetName, ss.getNumSheets()); 
    ...
 }

Setting up the headers! Setting the background color to gray and appending the fields I want to track, as explained previously.

changelogSheet.getRange('A1:G1').setBackground('#E0E0E0');  
changelogSheet.appendRow(["Timestamp", "Sheet", "Cell", "Type", "Old Value", "New Value", "User"]);  

Did some cleaning up of the Changelog by deleting the extra columns, setting the header row as frozen, customizing the widths of some columns, and protecting the sheet.

    changelogSheet.deleteColumns(8,19);
    changelogSheet.setFrozenRows(1);
    changelogSheet.setColumnWidth(1, 170);
    changelogSheet.setColumnWidth(7, 170);
    changelogSheet.protect();

Setting up the variable for user. Encountered a major issue here ¹

  var user = Session.getEffectiveUser().getEmail();

I also wanted to track all the additions and deletions made in the tracker. Here's the implementation.

  if (previousValue == null){
    typeChange = "Add";
  } else if (newValue == "") {
    typeChange = "Remove";  
  }

And lastly, this function appends a log whenever change is incurred:

changelogSheet.appendRow([timestamp, currentSheetName, cell.getA1Notation(), typeChange, previousValue, newValue, user]);  

Here's the result of the Changelog script¹:

Export to CSV

In addition, I also enable exporting the Changelog to CSV for documentation purposes. The source is indicated in the comments. Pastebin - Export to CSV

I just modified this script to customized the file name and save the file in a specific folder.

function saveAsCSV() {  
...
  var folder = DriveApp.getFolderById(<insert Folder ID here>);
...
  var currentDate = Utilities.formatDate(new Date(), "GMT+8", "MM-dd-yy hh:mm aaa");

  fileName = ss.getName() + "_Changelog_" + currentDate + ".csv";

  var csvFile = convertRangeToCsvFile_(fileName, changelogSheet);

  folder.createFile(fileName, csvFile);

  Browser.msgBox('Uploading file in ' + folder.getName());
}

Issues

Session.getActiveUser().getEmail() and Session.getEffectiveUser().getEmail() only works when the user is the author of the script.

The function returns nothing whenever my coworker updates the tracker. Apparently, no one in the Google Apps community has completely resolved this bug.

So there you go, a simple Google Apps script to track all changes made in a google spreadsheet.

I'm looking forward to resolve the issue and to dabble with more code. :)