get last modified date/time by a human (excluding changes by automation) for a Google Sheet using Google Apps Script

0
votes

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.

1
google-apps-scriptgoogle-sheets
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
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. I would have thought some kind of "audit" report would be required; perhaps there is a add-on, have you explored that option? – Tedinoz
@Tanaike I updated the question. I hope that clarifies it for you. – IMTheNachoMan
@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
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

1 Answers

0
votes

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.