2

I am trying to get the last modified date of a sheet from a GAS add on which I am developing.

My current idea is to get the Drive revision list and then take the last value. This seems a bit overkill for just getting the last modified, I am also worried that this will break if the number of revisions exceeds 1000 as per this link suggests.

https://developers.google.com/drive/api/v3/reference/revisions/list

Ideally I would like to know the range which has changed too, but I do not think this is possible.

I cannot use the onEdit event because I would like to track edits made by users who have not installed the add-on.

  var fileId = SpreadsheetApp.getActiveSpreadsheet().getId();
  var revisions = Drive.Revisions.list(fileId);
  var revisionLength = revisions.items.length;

  if(revisionLength > 0){
    var revision = revisions.items[revisionLength-1];
    var date = new Date(revision.modifiedDate);
    Logger.log(date.toString());
  }
Jesse Scherer
  • 1,492
  • 9
  • 26
user3284707
  • 3,033
  • 3
  • 35
  • 69
  • What add-on are you using? Are you referring to Apps Script as an add-on? – Jesse Scherer Mar 04 '19 at 16:22
  • That is correct – user3284707 Mar 04 '19 at 16:22
  • Are the edits only being made by people? I'm trying to work out why `onEdit` isn't working for you. – Jesse Scherer Mar 04 '19 at 16:24
  • onEdit does work, but only if the users have the add on installed, if the add on isn't installed then the onEdit doesn't fire. I need a solution that works for both users who have it installed and those that don't. I also need a solution that keeps all users in sync, whereas onEdit only fires on the user that made the change. – user3284707 Mar 04 '19 at 16:26
  • Are you developing an add-on, or writing a script that belongs to a specific Sheets document? To be clear, Apps Script is built into Sheets, and all users who interact with your sheet will be causing the `onEdit` simple trigger (https://developers.google.com/apps-script/guides/triggers/#Simple) to fire. – Jesse Scherer Mar 04 '19 at 16:30
  • I am developing an add on. I have played with simple triggers and these only fire on the user who has done the edit. So if two users are collaborating on the same sheet it doesn't fire on the other user - for me this is no good. I need to be aware of when anything changes, I could set some meta data on the sheet which would then be available to both users, however this will still not fire if the user doesn't have the add on installed and enabled. – user3284707 Mar 04 '19 at 16:34

2 Answers2

3

I believe you can do that as follow

var lastUpdated = DriveApp.getFileById(fileId).getLastUpdated();

See function reference

Fausto R.
  • 1,314
  • 3
  • 16
  • 29
  • 1
    This doesn't seem to update for me with every value changed.. it only seemed to update every minute or so? – user3284707 Mar 05 '19 at 14:44
  • 1
    if that's not enough, you might need to check the time frequency/accuracy on the revisions response I just tried the Drive.Revisions.list with pageSize=1 option, but it didn't return the last revision – Fausto R. Mar 06 '19 at 15:24
  • Yes annoyingly the revision response shows the last revision right at the end, so you need to page to the last one. This works for the time the modification was made, however seems to be returning the wrong username who made the edit in some cases when you have two users editing a document, very annoying! – user3284707 Mar 06 '19 at 16:43
1

Given that you need users of your add-on to have access to revision information from non-add-on users, the Drive revision list is precisely what you need. Happily, you can get the content of revisions, so if you wish you can compute diffs. I don't know what your data looks like, so that might be easy or nigh-impossible.

Aside: to your point about more than 1000 revisions, if there are more than 1000 (or whatever your page size is) revisions, you'll get a nextPageToken like so:

{
 "kind": "drive#revisionList",
 "nextPageToken": "BHNMJKHJKHKVJHyugaiohasdzT1JyUmlQWG10RUJ1emx1S2xNDg4EgQzMzY1GAI=",
 "revisions": [
    ...
 ]
}

If you see that you'll need to list revisions again, providing that token.

Anyway, when you list revisions, each revision will look something like this:

{
 "kind": "drive#revision",
 "etag": "\"som3-e-tAg\"",
 "id": "3365",
 "selfLink": "https://www.googleapis.com/drive/v2/files/dummydummydummy/revisions/3365",
 "mimeType": "application/vnd.google-apps.spreadsheet",
 "modifiedDate": "2018-10-19T19:05:41.762Z",
 "published": false,
 "exportLinks": {
  "application/x-vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=ods",
  "text/tab-separated-values": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=tsv",
  "application/pdf": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=pdf",
  "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=xlsx",
  "text/csv": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=csv",
  "application/zip": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=zip",
  "application/vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=ods"
 },
 "lastModifyingUserName": "Joe User",
 "lastModifyingUser": {
  "kind": "drive#user",
  "displayName": "Joe User",
  "picture": {
   "url": "https://lh3.googleusercontent.com/-asdfsadf/AAAAAAAAAAI/AAAAAAAAFOk/OIPUYOIUGO/s64/photo.jpg"
  },
  "isAuthenticatedUser": true,
  "permissionId": "123456789",
  "emailAddress": "user@gmail.com"
 }
}

Provided your data isn't insanely complex or large, you could fetch the target the text/csv export link for the revisions you wish to compare, and then do that comparison in Apps Script.

That might look something like:

var fileId = SpreadsheetApp.getActiveSpreadsheet().getId();
var revisions = Drive.Revisions.list(fileId);
var revisionLength = revisions.items.length;

if(revisionLength > 1) {  // something to compare!
  var revision = revisions.items[revisionLength-1];
  var newContent = UrlFetchApp.fetch(revision.exportLinks["text/csv"]).getContent();
  newContent = Utilities.parseCsv(newContent);

  var oldRevision = revisions.items[revisionLength-2];
  var oldContent = UrlFetchApp.fetch(oldRevision.exportLinks["text/csv"]).getContent();
  oldContent = Utilities.parseCsv(oldContent);

  # TODO check they're the same size!

  # where do they differ?
  for (var row = 0; row < newContent.length; row++) {
    for (var col = 0; col < newContent[0].length; col++) {
      if (newContent[row][col] != oldContent[row][col]) {
        Logger.log('Change on row ' + (row + 1) + ' column ' + (col + 1));
      }
  }

  # when did it change?
  var date = new Date(revision.modifiedDate);
  Logger.log(date.toString());
}
Jesse Scherer
  • 1,492
  • 9
  • 26
  • Some disclaimers: I haven't tried using UrlFetch in an add-on, and that might be prohibited. The CSV export URL only gives you a single sheet, so if your Sheets document has multiple sheets this gets more complex. – Jesse Scherer Mar 04 '19 at 17:27
  • Amazing thanks so much for your reply.. I will test this out tomorrow and get back to you! – user3284707 Mar 04 '19 at 19:02
  • Unfortunately this has not worked, the url https://docs.google.com/spreadsheets/export?id=dummydummydummy&revision=3365&exportFormat=csv as what is given in the export isn't actually a file, its just a link to a page which then downloads the file. Therefore the data I get back is actually of that page rather than the actual file, do you know of any way to get round this? – user3284707 Mar 05 '19 at 09:53
  • Try a URL like https://docs.google.com/spreadsheet/ccc?key=dummydummydummy&output=csv&gid=1959012225&revision=3365 (the `gid=...` part is the tab ID and is probably redundant for a single-tab document) -- if that works I'll simplify my answer since we don't have to do so much with the revision object – Jesse Scherer Mar 06 '19 at 16:36
  • Sorry i'm a little unsure what ccc means in your url? I have tried this and it throws an error page with Google. Where did you get this url from sorry? We do need to support multiple sheets so we may been the tab ID – user3284707 Mar 06 '19 at 16:47
  • I got that pattern from a "publish to the web" url because for me it returns clean CSV without redirects. – Jesse Scherer Mar 06 '19 at 20:31
  • Ok, where did you get the publish to the web url from? What does the ccc stand for on the url, sorry for all the questions! – user3284707 Mar 06 '19 at 20:32
  • Actually doing a google gave me this... https://stackoverflow.com/a/33727897/3284707 which is really helpful! Thank you I will give this a go tomorrow when back in the office – user3284707 Mar 06 '19 at 20:37