Restoring specific version of spreadsheet using the Google API

3
votes

Just like the title says, I'm trying to find a way to restore a spreadsheet to a specific version using the Google Sheets API v4 (or Google Drive API v3).

This is needed because of unit testing - I need one test to perform some actions (let's say delete a row) on a spreadsheet, assert the results and then reset the spreadsheet to its initial state so the next test can execute on the original spreadsheet.

So far I noticed that there isn't an option to do something similar in the Google Sheets API. As far as using the Google Drive API goes, I tried using revisions (getting a list of all revisions and deleting all revisions after the desired one), but to no avail.

I also tried capturing the request after clicking on the Restore this version button and found out there is a revisions/revert endpoint. 

> Example:
> https://docs.google.com/spreadsheets/d/**spreadsheetId**/revisions/revert?includes_info_params=false

I actually managed to revert the version this way, but using Postman and using hardcoded values - which is bad.

Any advice on how to approach this?

1
c#google-drive-apigoogle-sheets-api

1 Answers

4
votes

The usual way to restore an older version from a document with Drive API is by deleting all later versions with Revisions: delete. But, as the referenced documentation indicates, "you can only delete revisions for files with binary content in Google Drive, like images or videos. Revisions for other files, like Google Docs or Sheets, and the last remaining file version can't be deleted". So there is no direct way to revert to a previous version with Drive API (Sheets API is not useful for this).

Workaround:

A possible workaround would be to do the following:

  • Get the revision ID you want to restore via Revisions.list.
  • Export the version you want to restore via Revisions.get and exportLinks, a property of the Revisions resource. You will have to choose the MIME type in which to export this version (see list of supported export MIME types). Update: You have to specify that you want to retrieve the field exportLinks by setting the parameter fields to exportLinks.
  • Get the blob of this exported version.
  • Using this blob, update the spreadsheet with Files.update.

You can see the code to do this in Google Apps Script in this answer. You would have to use the API directly and adapt this to C#, but the workflow is basically the same.

Reference: