Google Drive Files/Spreadsheets REST API: How to avoid delay when tracking file changes?

Question

I have an application where I need to keep local copies of some of my user's Google Spreadsheets, and this copy should be in sync with the Google Drive version. I've been testing two methods of tracking changes to a Google Spreadsheet: (1) the file version polling method and (2) the files.watch method.

1) The file version polling method

In this method, whenever I need the most recent version of a Spreadsheet (for instance, when the user wants to download the file from my application), I retrieve the file version from Google using:

POST https://www.googleapis.com/drive/v3/files/FILE_ID?fields=version

If the version is greater than the version I have stored on my end, I know that changes have been made and the file on my end is outdated. So I download the file and update my copy.

The problem is that it takes a while for the file version number to be updated on Google's end. Ideally, after editing a Google Spreadsheet cell, my application should be able to detect this change within less than 10 seconds. However, after editing a cell and seeing the Saved to Drive confirmation at the top, sometimes it takes seconds, other times it takes minutes before the version number gets updated, so it is very inconsistent.

Aside from the version number, I've also tried polling the modifiedTime value to see if it changed sooner, but it didn't. So I tried another method.

2) The files.watch method

In this method, I keep track of the file changes by registering a webhook to receive change notifications from Google:

POST https://www.googleapis.com/drive/v3/files/FILE_ID/watch

Whenever I receive a change notification, I know that I need to update my local copy.

Unfortunately, the change notifications also don't happen as quick as I would like. It also has very inconsistent delays: sometimes taking a few seconds, sometimes taking more than a minute.

UPDATE (3) The 'always export' method? Never cache method?

To complicate matters, it seems that even if I ignore my local copy and always try to download the latest version of the file directly from Google, the downloaded file will not necessarily be the absolute latest version that the user sees on the Spreadsheets editor. I tried that using

GET https://www.googleapis.com/drive/v3/files/FILE_ID/export?mimeType=application/vnd.openxmlformats-officedocument.spreadsheetml.sheet

but it would often return the outdated version, and sometimes would only return the latest version after a few minutes.

Is there something else that I can try? The above methods use the Google Drive Files API, but if there is a way to detect changes sooner using the Google Spreadsheets API, I would like to know.

Answer 1

1. How to detect file changes as soon as possible?

After a file change (in my case, a change in a Google Spreadsheet), the version does not get updated immediately, and when you watch for file changes with the files.watch API you will also not get a notification immediately.

What does get updated immediately is the list of revisions of the file, which can be retrieved with the revisions.list API:

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

This returns a list of all revisions of the file FILE_ID. The last item in the list is the most recent revision (the "head" revision). In order to know if a file has changed, I retrieve this list. If the id of head revision is different from the id stored in my end, it means that my local copy is outdated, so I have to update the file and its revision id.

However, if you call files.export, the file version returned will not necessarily be the absolute most recent version (e.g., the Google Sheet you are seeing in your browser). And in the case of Google editor documents, it is not possible to retrieve the most recent revision using the revisions.get API. What can you do then?

2. How to retrieve the most recent revision of a Google Sheet?

(I bet it works for other Google editor documents as well).

Before calling files.export, you have to "touch" the file using the files.update API, updating its modifiedTime:

PATCH https://www.googleapis.com/drive/v3/files/fileId
  {
    "modifiedTime": "TIMESTAMP"
  }

Where TIMESTAMP is a date with the format 2022-04-16T22:00:00Z.

For some reason, touching the file like this "forces" Google to return the head revision of the file the next time you call files.export:

GET https://www.googleapis.com/drive/v3/files/FILE_ID/export?mimeType=MIMETYPE

In my case, MIMETYPE is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet.

That's it. So far, this has been working for me.