0

I have a spreadsheet shared with a few users. I want to see who is making edits and when, in a convenient format. I noticed that if I right click into a specific cell, Google Sheets now offers the new feature "Show Edit History". (https://gsuiteupdates.googleblog.com/2019/06/edit-history-sheets.html)

How does Google store this data? Can I use Apps Script (or other tool) to report on these changes in a more digestible format?

I am envisioning a function which creates a new tab in the Sheet and has columns for...

  • @gmail of user that made the change
  • cell where the change was made
  • time stamp of change
  • previous entry
  • new entry

Do you know if this is possible?

Example export below:

email   cell    date    previous_entry  new_entry
user@gmail.com  C2  5/19/2020 1:11:00   Old string  Change
name@aol.com    C2  5/19/2020 21:52:00  Change  More changes
person@gmail.com    D15 5/20/2020 1:05:00       Data
Rubén
  • 34,714
  • 9
  • 70
  • 166
Evan292
  • 13
  • 7
  • 2
    Related https://stackoverflow.com/q/47607375/1595451, https://stackoverflow.com/q/14222090/1595451 – Rubén May 20 '20 at 20:33
  • Thanks @Rubén, helpful stuff - but that requires Python. I am trying to write this using Apps Script https://www.google.com/script/start/ – Evan292 May 20 '20 at 21:26
  • You could use the Drive Advanced Service to make use of the Google Drive API. Please checkout https://developers.google.com/apps-script/guides/services/advanced – Rubén May 20 '20 at 22:11
  • It is possible to get access to revisions,but not the edit history that you see in the cell. – TheMaster May 21 '20 at 03:08
  • @TheMaster do you mind clarifying? I wrote a script that is able to execute onEdit(e) and fills a new sheet with the New Value as well as the Cell where the change occurred. I am still unable to pull ActiveUser() as well as the Prior Value. Are you saying this isn't supported? Or is there a means? – Evan292 May 21 '20 at 16:03
  • 1
    You can get prior value using `e.oldValue` in most cases. But ActiveUser() is almost impossible to get, unless it's a gsuite customer within his domain. Simple triggers run without authorization/identification. – TheMaster May 21 '20 at 16:31

0 Answers0