2

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
}, 
...
]
4

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();
}
-1

This is now pretty straight forward with the event object. I wrote this article with script example to show how it is done.

http://blog.prolecto.com/2016/10/30/how-to-google-sheet-script-to-edit-cell-contents/

  • 3
    We like answers to be self-contained. Links are great for supplemental information, but if a reader has to follow the link in order to get to any useful information it's not considered a good answer. Further, when a new user shows up and posts a content-light answer with a link elsewhere it looks spammy. – ale Oct 31 '16 at 12:55
  • The linked article doesn't answer the question. – Rubén Oct 4 '17 at 21:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.