Feature Request: Support accessing previous sheet revisions

[jkrauska]

I have a use case where it would be helpful to use gspread to compare changes before and after a revision change.

It looks like the Drive API supports retrieving previous revisions of a document.

Could you add this feature request to be able to load a sheet from a previous date.

[msuozzo]

Looking through the RESTful API (the one gspread uses) and the Apps Script API (JS-based Spreadsheet extensions) I couldn't find support for revision history. That's not to say support for this feature doesn't exist in a Google API but that it likely falls out of scope of this project.

One hacky solution would be to snapshot the spreadsheet with a cronjob or some such. You could store these snapshots indexed by date/time and access the revisions that way. Better yet, you could add a hook into your gspread startup that takes a snapshot before any modification can be made to the sheet (assuming you only make modifications through the API).

Wish there were a better answer I could give.

[msuozzo]

D'oh. Totally missed the Drive RESTful API section on revisions. It seems feasible but would require using the full Drive API instead of just the limited Spreadsheets API. As mentioned in #293, it's possible but represents a very large change in scope and would require some thought.

As of now, thanks for bringing this up and I'll mark it as a feature request :-)

[eranmaoz44]

Any updates on the topic? currently doing it with export Links , but it requires further authentication to retrieve the csv of a revision

[lavigne958]

Hi @eranmaoz44 I will look into it. The google drive API has now been integrated to Gspread (in order to create/share/delete spreadSheets). It might be possible now to access older revision of a spreadSheet.

[eranmaoz44]

Great news!

…

On Fri, May 14, 2021, 11:54 AM Lavigne958 ***@***.***> wrote: Hi @eranmaoz44 <https://github.com/eranmaoz44> I will look into it. The google drive API has now been integrated to Gspread (in order to create/share/delete spreadSheets). It might be possible now to access older revision of a spreadSheet. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#294 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ADALJXT3HMDKNQ5G3VWQC3DTNTQK3ANCNFSM4BPPRX6Q> .

[lavigne958]

Hi I found the documentation in GoogleAPI about revisions. Here but I can't quiet understand how to use it.
It offers to:

• list all revisions

• get a specific revision metadata

• delete a revision

• Update a revision

• List: I get it, one can list all revisions and check different things, good enough.

• Get: you can only get metadata of a revision, so I don't see the point, I think getting the actual spreadSheet at that revision would be nice, but only the metadata... can't understand how that is useful appart from check who made some changes 🤔 .

• Delete: deleting a revision, not the most useful operation but I understand the need.

• Update: it says: "Update a revision with patch semantics" and I don't understand why one would need to update a revision, they are the grand truth of history for a file, like a git commit no one should update an old commit in the past of a repository.

@eranmaoz44 does one of the above API suits what you are looking for ? Looking at it quickly make me think it needs some time to be designed. This returns a complex object and need to be organized but it can sure be done ! 🙂

If you understand the use of the API please help if you can think of some use cases.

[eranmaoz44]

Hey @lavigne958,

I think that use-case that was missing at the time / not working was the get_revision_content method. It required an extra authentication step (even though already authenticated via o2auth with the relevant resources) that required you to login again to your google account, or that it didn't work entirely. I can't fully remember since it was almost two years ago and now things may havve changed.

The authentication metod used was
oauth2client.service_account.ServiceAccountCredentials.from_json_keyfile_name(google_creds.json, ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'])

I think the emphasis should be on getting the content of a revision not only the metadata
Might be a bit more organized if a Revision object was returned which could then be requested for content/metadata/deleted etc.

Update seems completely unnecessary and harmful tbh, a revision should be immutable, if one wishes to update it should be done through the main spreadsheet

Furthermore, I think another nice method to have would be to calculate a 'delta' between two revisions. i.e. between revision id=x and revision id=y the field AA changed from 1 to 3 and the field BC was changed from "hello" to "hey"

Either that or have a method that that given two dates and a spreadsheet, returns records of the changes with dates, using the underlying diff between every two subsequent revisions

Hope that helps,
Thanks for efforts

[lavigne958]

Thank you very much for this.

I agree what is useful is: getting a spreadSheet at a certain point in time. Computing a delta will come once we do the previous action.

Sobfar it looks like something quiet complicated to do and other issues are waiting and PRs too.
I don't have much time to UT into it but if someone is willing to do so I'll be happy to review it and help design it.

Until then the issues stays open and any help is welcome 😊

Edit:
What we can do:

• list all revisions
• get a specific revisions Metadata

What wee need:

• get a spreadsheet content from a specific revision object.

[alijkl]

HI @jkrauska
I was in need for a similar feature and as well as making it simpler to process spreadsheet unattended.
We ended up writing a wrapper on top of gspread.
Check this link for an example.
We were unable to get the content without retrieving the file first,
as not all changes are recorded as revision by the googlesheet API.
hope this help.