CODEWITHSUNDEEP
javagoogle-drive-apigoogle-sheets-api
Theeran Pichapillai
Theeran Pichapillai

Reputation: 13

How to get the modified content from google sheets?

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

Upvotes: 1

Views: 2674

Answers (1)

Rafa Guillermo
Rafa Guillermo

Reputation: 15377

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 [email protected] 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": "[email protected]"
   },
   "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.

Upvotes: 3

Related Questions