1

I am trying to get the modified content after the given time from google sheets. Nowhere I can found the api to get the data. What i can see is getting modified date alone from the drive Api. How can I get the data using Drive or Sheets Api? Give me the suggestions if Possible

Theeran Pichapillai
  • 13
  • 6
  • Drive keeps track of file changes using a version history, https://developers.google.com/drive/api/v3/manage-revisions#list_and_download_individual_revisions could you utilise this using the `Revisions: get` method? https://developers.google.com/drive/api/v3/reference/revisions/get – Rafa Guillermo Jul 08 '19 at 13:58
  • I have already gone through the APIs. Those are not giving the Modified Data instead it's giving modifiedTime and other metadata. All the drive APIs are only taking about modifedTime nowhere it's giving updated sheet data. I can't able to go further without this data?? If you know any other way please suggest me – Theeran Pichapillai Jul 09 '19 at 14:11
  • What do you mean by 'modified content'? Other than version history Drive and Sheets don't keep a running track of content changes over time. – Rafa Guillermo Jul 09 '19 at 16:00
  • modified content-->Recently Edited values in the spreadsheet. From the version History drive API, I could not get the recently Edited Data. I m not sure how I can achieve this?? – Theeran Pichapillai Jul 10 '19 at 13:02
  • Have you tried turning on spreadsheet notifications? https://support.google.com/docs/answer/91588 It's not possible to get recently edited data like this as it's not stored, but you can get notifications every time someone edits a spreadsheet and also see what they've modified. – Rafa Guillermo Jul 10 '19 at 13:15
  • I have tried out. That is working fine I can get the notification. Is there any suggestion to get the data?? – Theeran Pichapillai Jul 10 '19 at 13:20
  • In the email notification there is a link to see changes to the sheet on the line that says `See the changes in the Google Document "Untitled spreadsheet": Click here ` – Rafa Guillermo Jul 10 '19 at 13:26
  • Where can I see the email notification?? – Theeran Pichapillai Jul 10 '19 at 14:03
  • In your inbox https://i.imgur.com/wOcUc1f.png – Rafa Guillermo Jul 10 '19 at 14:16
  • Do I get an API for this to collect this data?? – Theeran Pichapillai Jul 10 '19 at 14:46
  • 1
    how to get last updated rows , columns and data that has been updated in a sheet ?? – Theeran Pichapillai Jul 10 '19 at 15:10
  • Have a look at the revision documentation here https://developers.google.com/drive/api/v3/manage-revisions#list_and_download_individual_revisions, Revisions for Sheets can't be downloaded and the revision changes link you get as a notification doesn't allow you to programmatically get only the changes as the sheet itself as it only shows the changes as highlighted cells. – Rafa Guillermo Jul 10 '19 at 15:21
  • { "kind": "drive#revision", "id": "137", "mimeType": "application/vnd.googleapps.spreadsheet", "modifiedTime": "2019-07-10T15:07:24.255Z", "published": false, "lastModifyingUser": { "kind": "drive#user", "displayName": "Theeran Pichapillai", "photoLink": ".jpg", "me": true, "permissionId": "", "emailAddress": "" }, "exportLinks": {******* } } – Theeran Pichapillai Jul 10 '19 at 17:00
  • Above you can see the response of particular version response data. How can I get to know which cell value or row value or column values have changed.. No clue in cell level??? – Theeran Pichapillai Jul 10 '19 at 17:02
  • You can't see which cell or cells have been changed just from a revisions query. The Revisions resource documentation is here developers.google.com/drive/api/v3/reference/revisions which shows that a revisions resource only has information about the user that modified the file, and some other metadata such as the revision ID and MIME Type of the document. As a workaround, have you looked into doing it programatically by taking the last two revisions and comparing the data in the sheets individually? You can get a copy of old versions by fetching the URLs in `exportLinks`, maybe try CSV? – Rafa Guillermo Jul 11 '19 at 07:05

1 Answers1

3

Google Drive keeps a track of revision history of files that are contained on it. There is however, no way to obtain the revisions from a request alone.

Google allows for you to receive email notifications whenever a user makes an edit to your sheet, which you can set up by completing the following steps:

  1. In the Spreadsheet's web view, click Tools -> Notification rules...
  2. Under Notify me at myemail@address.ext when... select Any changes are made
  3. Under Notify me with... select Email - right away
  4. Click Save.

You should also be aware that you will not get a notification for edits made to the sheet by you - notifications are only received when another user edits the sheet. Whenever you get an email notification, you will receive a link to view the changes to the spreadsheet in the form of a read-only web view link.

You can work around this programatically, though there isn't one right way and it can be quite complicated. You can use the Revisions: list method of the Drive REST API to get the information about the user that made an edit, as well as a list of links which you can use to export that revision of the sheet to another MIME Type, as shown below in the request response.

Requesting:

GET https://www.googleapis.com/drive/v3/files/SPREADSHEET_ID/revisions

with revisions/exportLinks,revisions/lastModifyingUser/emailAddress as the fields field and replacing SPREADSHEET_ID with the ID of the spreadsheet will give you a 200 response:

{
 "revisions": [
  {
   "lastModifyingUser": {
    "emailAddress": "username@domain.ext"
   },
   "exportLinks": {
    "application/x-vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=revisionNumber&exportFormat=ods",
    "text/tab-separated-values": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=revisionNumber&exportFormat=tsv",
    "application/pdf": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=revisionNumber&exportFormat=pdf",
    "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=revisionNumber&exportFormat=xlsx",
    "text/csv": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=revisionNumber&exportFormat=csv",
    "application/zip": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=revisionNumber&exportFormat=zip",
    "application/vnd.oasis.opendocument.spreadsheet": "https://docs.google.com/spreadsheets/export?id=SPREADSHEET_ID&revision=1&exportFormat=ods"
   }
  }
 ]
}

With the links to individual changes, you can fetch and compare the different versions of the Sheet using Apps Script, and output A1 notation of the cells that have different values between versions. This, with the email address from the original Revisions: list request, is enough to compile a file or a log containing.

You can put this into a simple onEdit() trigger bound to the sheet will allow you to automatically get the changes each time a user edits the sheet.

Rafa Guillermo
  • 14,474
  • 3
  • 18
  • 54
  • one more doubt How do I get a sheet specific revision that is Inside the spreadsheet it will have many worksheets how can I find this is for which work sheet??? – Theeran Pichapillai Jul 11 '19 at 14:20
  • The Resource response you get from the `Revisions: list` request is in a JSON format and returns at the revisions in the request. You can get revisions since a certain date by including the `modifiedTime` field and filtering the results. Hope I've been able to help! – Rafa Guillermo Jul 11 '19 at 14:29
  • You can check the request response documentation here so you can get an idea for yourself too! https://developers.google.com/drive/api/v3/reference/revisions?authuser=1#resource – Rafa Guillermo Jul 11 '19 at 14:35
  • I have gone through. right now I am having 3 worksheets under my spreadSheet. Whenever I am doing changes in sheets whether it would be in sheet1 or sheet2 or sheet3. There is no reference which sheet has been modified Is it possible to get that ?? – Theeran Pichapillai Jul 11 '19 at 15:50
  • https://docs.google.com/spreadsheets/export?id=1Ks_08BgvDj7OsD6lhr5TkohVWa1Yoj3x8_xP3-igNpw&revision=143&exportFormat=csv ........ this is the URL to get the values but it is always giving sheet1 values ... How can i get it for sheet2?? – Theeran Pichapillai Jul 11 '19 at 15:56
  • @TheeranPichapillai There's nothing that specifically states which sheets have been modified, though each revision will always show all changed values for all sheets. You will have to loop through each sheet and compare the old version with the new version to see which cells have been changed. This being said, make sure to check out the export types - exporting to CSV from Sheets converts the first sheet only https://developers.google.com/drive/api/v3/manage-downloads#downloading_google_documents – Rafa Guillermo Jul 12 '19 at 07:44