Google-sheets – Access Google Spreadsheet revision history through API

google sheetsgoogle-apps-script

Is there any way I could access Google Spreadsheet revision history through the API?

The reason for that is I have a lot of sheets and simply relying on build-in revision history does not suffice to point me to the sheet where changes were made. I need to check all the sheets to figure out what change was made – highly inefficient approach.

The output I would expect is:

Changes made at 01 Jan 2010 by SomeUser 
Sheet name: Sheet1
Updates:
[{
address: C1,
oldValue: OldValue,
newValue: NewValue
},
{
address: B2,
oldValue: OldValue,
newValue: NewValue
}, 
...
]

Best Answer

I ended up using logging from Google App Script to file.

function onEdit(event) {

  var observableSheets = ["Sheet1", "Sheet2", "Sheet3"];
  var ignoredUsers = ["ignored_user@gmail.com", "ignored_user2@gmail.com"];
  var current = new Date();

  Logger.log("%s - %s", "Change Logger", new Date());

  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  var val = r.getValue();
  var activeCellAddress = r.getA1Notation();
  var sheetName = s.getName();

  if (observableSheets.indexOf(sheetName) > -1 && r.getRow() > 1) {
    Logger.log("Change in %s sheet detected", sheetName);
    Logger.log("Cell change at: %s", activeCellAddress);
    Logger.log("value of active cell: %s", val);
  }

  var sessionEmail = Session.getActiveUser().getEmail();

  if (ignoredUsers.indexOf(sessionEmail) > -1) {
    return;
  }
  SaveLogToFile();
}
Related Topic