0

We have hundreds of Google Sheets that are either modified by a human (through the UI) or by automation (that is run under a human ID). The modifications can be anything from changing cell values to adding a new sheet.

I am looking to programatically get the last time each file was modified by a human through the UI and not by the automation.

We cannot, and do not, want to add onEdit code to each Sheet for various reasons.

I have checked both Drive.Revisions.list(fileID) and Drive.Files.get(fileID) but it does not show a distinction between how the edit was made -- either by the UI or by automation.

Is it possible to get the last modified date of a file but only for modifications made by a human using the web UI and not by automation?

Update:

I wanted to explain what I mean by "modifications made by a human using the web UI and not by automation".

There are two ways a Google Sheet could be edited.

  • a human using the web UI = a human user opens his/her web browser, opens the Google Sheet document and starts making changes
  • automation = a script/program programatically opens a Google Sheet document (for example, SpreadSheetApp.openById(...)) and then makes changes

The challenge is that if a Google Apps Script is executed under the context of a user (like me) then the normal activity logs of the file (like the last changed by user) will show that user even though they didn't make the change using the web UI, they made it using automation.

Update 2:

More details on our use-case:

  • Our team uses Google Sheets for tracking details of our projects
  • Each project has its own sheet
  • As our team gets a new project, a new sheet is created from a template sheet
  • The human users in our team open a sheet and make whatever updates they need
  • We also have a Google Apps Script that, once a day, opens each sheet and does its own updates to the sheet
  • The Google Apps Script is run on a daily trigger that I own
  • I am also on the team so I also open sheets individually to make edits

So:

  • Sheets are edited by the human members of our team, including me
  • Or sheets are edited by a Google Apps Script that runs under my user

Now my need is that every day I need to collect the last time each sheet was edited by a human.

  • I can't just check for last modified because it is possible the script made an edit and we do not want those
  • I cannot exclude all edits made by me because I could have made changes to a sheet manually

I don't want to use onEdit for each sheet because of the overhead. We don't want to end up with hundreds or thousands of Sheets each with their own script project tied to them. If there is ever a bug discovered in the code we would have to go and edit it for all the sheets -- not practical. And we already have a couple hundred sheets created that do not have any code in them so if we went the onEdit route I would have to manually add them to every existing sheet.

IMTheNachoMan
  • 5,343
  • 5
  • 40
  • 89
  • 1
    I apologize for my poor English skill. I cannot understand about `modifications made by a human using the web UI and not by automation`. Can I ask you about the detail information of it? – Tanaike Jul 25 '19 at 22:22
  • If I understand you, you want to capture the date (and presumably the details) when/if a human made a change to a sheet but you don't want to use onEdit. As a matter of interest, have you explored [G Suite Admin SDK](https://developers.google.com/admin-sdk/). I would have thought some kind of "audit" report would be required; perhaps there is a add-on, have you explored that option? – Tedinoz Jul 26 '19 at 01:42
  • @Tanaike I updated the question. I hope that clarifies it for you. – IMTheNachoMan Jul 26 '19 at 12:49
  • @Tedinoz Looking into it now. Hadn't thought of that... – IMTheNachoMan Jul 26 '19 at 12:50
  • 1
    @IMTheNachoMan You want to know whether the Spreadsheet is edited by the user's browser or the script. I could understand like this. Is my understanding correct? If my understanding is correct, in this case, who is the script run? But, if your issue has already been resolved, also please tell me. At that time, I would like to stop thinking of the workaround. – Tanaike Jul 26 '19 at 23:35
  • 1
    I recognise your problem more clearly now, though I do not understand it. Hundreds of sheets yet you want the datestamp of the file not any given sheet. Would you please edit your question to describe the number of users who work on your spreadsheet at any given time, and whether they do data input, or can add/delete columns/rows/sheets/etc? Would you also please explain the reasons why you "cannot, and do not, want to add onEdit code to each Sheet"? I ask because i) `onEdit(e)` is a possible solution and ii) regardless of the number of sheets, you do not need (nor want) an `onEdit` per sheet. – Tedinoz Jul 27 '19 at 01:52
  • @Tedinoz I added more details. – IMTheNachoMan Jul 27 '19 at 02:08
  • WE should chat - if I could figure out how. argh – Tedinoz Jul 27 '19 at 02:17
  • @IMTheNachoMan Can I ask you about your current situation? – Tanaike Jul 28 '19 at 02:52
  • @Tedinoz I thought SO has a chat system... – IMTheNachoMan Jul 28 '19 at 03:54
  • @Tanaike I had updated the question with more details about my current use case. – IMTheNachoMan Jul 28 '19 at 03:54
  • "don't want hundreds of Sheets each with their own script." You misunderstand 'onEdit(e)'-you do NOT need a script for each sheet; one script might work for the entire spreadsheet. The "(e)" element are [Event Objects](https://developers.google.com/apps-script/guides/triggers/events#edit) which enable the script to identify the edited cell and sheet (among other things); so you could update the timestamp in that specific sheet-[this answer shows how](https://stackoverflow.com/a/57193102/1330560). If you have a lot of users, `onEdit` might not suit, but it does have its advantages. – Tedinoz Jul 28 '19 at 07:39
  • @IMTheNachoMan How many users in your team? – Tedinoz Jul 28 '19 at 07:40
  • Regarding concurrent access/editing, interesting answer [here](https://webapps.stackexchange.com/a/92150/196152) suggesting that users make their edits via Google Forms. – Tedinoz Jul 28 '19 at 07:43
  • Thank you for replying. It seems that the discussions are progressed. So I think that it will resolve your issue. – Tanaike Jul 28 '19 at 07:59

1 Answers1

0

You can access the version history of a document with Apps Script and retrieve the modification dates and modifying users.

If you set-up the script to be run by a service account, the changes made to a document through Apps Script will appear as made by the service account and not the user. This allows you to distinguish between manual and programmatic change.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • That is the issue -- changes made programatically still show up made by the user who runs the script. If I make a change manually, and then a script (running as me) makes a change, I want to know the date/time of the change I made -- not the script. – IMTheNachoMan Jul 26 '19 at 12:51
  • You can set up the script to be run through a service account. In this case, the changes made by the script will appear in the version history as made by the service account and not the user. – ziganotschka Jul 27 '19 at 13:51
  • If you don´t want to use either the onEdit trigger, nor a service account / user impersonation - I am not aware of other options you might have. – ziganotschka Jul 27 '19 at 14:02