google-apps-script google-sheets google-drive-api version revision-history

Revert to specific version of Google sheets with respect to specific date using Google Apps Script

I have a Google sheet and I am looking to get the previous version which was on a specific date. Let's say, I want to get the version of my Google sheet dated back to 31st December 2022. This version should be created in a new spreadsheet and does not overwrite the existing one. I found a StackOverflow snippet proposed by @Tanaike that seems similar to my requirement:

function myFunction() {
 var revisionId = "1";  // Please set the revision ID you want to revert.
 var googleDocsFileId = "###";  // Please set the Google Docs file ID.

 var endpoints = Drive.Revisions.get(googleDocsFileId, revisionId).exportLinks;
 var keys = Object.keys(endpoints);
 for (var i = 0; i < keys.length; i++) {
   if (keys[i].indexOf("application/vnd.openxmlformats-officedocument") > -1) {
     var endpoint = endpoints[keys[i]] + "&access_token=" + ScriptApp.getOAuthToken();
     var mediaData = UrlFetchApp.fetch(endpoint).getBlob();
     
     Logger.log(mediaData.getBytes().length)
     Drive.Files.update({}, googleDocsFileId, mediaData);
     break;
   }
 }
}

But this script does not address the specific date version scenario and it overwrites the existing file. I was not able to modify it due to my basic knowledge. Any guidance would be much appreciated.

Solution

I believe your goal is as follows.

You want to restore the existing Google Spreadsheet by selecting the date as a new Google Spreadsheet.
From any version of 31st December 2022 will be fine, you want to restore one of the versions on 31st December 2022.
In this case, you are not required to include the container-bound script.
You want to achieve this using Google Apps Script.

In this case, how about the following answer? Unfortunately, I think that in order to achieve your goal, it is required to do the following flow.

Retrieve versions using the inputted date.
Retrieve the endpoint of XLSX using the retrieved version.
Convert XLSX data to Google Spreadsheet as a new file.

When this flow is reflected in a sample script, I think that your showing script cannot be directly used. In this case, how about the following sample script?

Sample script:

Before you use this script, please enable Drive API at Advanced Google services. And, please set date. In this case, please use the format of yyyy-MM-dd as a string value.

This sample script is for Drive API v2.

function myFunction() {
  const date = "2022-12-31"; // This is from "I want to get the version of my Google sheet dated back to 31st December 2022."
  const spreadsheetId = "###"; // Please set your Spreadsheet ID. Or, please use SpreadsheetApp.getActive().getId() if your script is container-bound script.

  let list = [];
  let pageToken;
  do {
    const obj = Drive.Revisions.list(spreadsheetId, { maxResults: 1000, fields: "nextPageToken,items(id,mimeType,modifiedDate)", pageToken });
    const items = obj.items.filter(({ modifiedDate }) => modifiedDate.split("T")[0] == date);
    if (items.length > 0) {
      list = [...list, ...items];
    }
    pageToken = obj.nextPageToken;
  } while (pageToken);
  if (list.length > 0) {
    const revision = list.pop(); // This is the last version on the date of `date`. If you want to first version on the date, please use list[0] instead of list.pop()
    if (revision.mimeType == MimeType.GOOGLE_SHEETS) {
      const endpoint = Object.entries(Drive.Revisions.get(spreadsheetId, revision.id).exportLinks).find(([k]) => k == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
      if (endpoint) {
        const blob = UrlFetchApp.fetch(endpoint[1], { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
        const file = DriveApp.getFileById(spreadsheetId);
        const res = Drive.Files.insert({ title: `${file.getName()}_restored`, mimeType: MimeType.GOOGLE_SHEETS, parents: [{ id: file.getParents().next().getId() }] }, blob);
        console.log(res.id);
      } else {
        console.error("Endpoint for exporting XLSX was not found.");
      }
    } else {
      console.error("File is not Google Spreadsheet.");
    }
  } else {
    console.error(`No revisions in ${date}.`);
  }
}

When this script is run, the last version on "2022-12-31" is restored as a new Google Spreadsheet. The Spreadsheet is created in the same folder as the target Spreadsheet.
If you want to restore the 1st version on "2022-12-31", please modify const revision = list.pop(); to const revision = list[0];.

References:

Updated on December 13, 2023

In the current stage, when Drive API is enabled at Advanced Google services, Drive API v3 is automatically used. In the official document, Drive API v3 has already been used. Ref In the current stage, the users can select V3 and V2.

If you enable Drive API at Advanced Google services, Drive API v3 is automatically set. In this case, the above sample script is modified as follows.

This sample script is for Drive API v3.

function myFunction() {
  const date = "2022-12-31"; // This is from "I want to get the version of my Google sheet dated back to 31st December 2022."
  const spreadsheetId = "###"; // Please set your Spreadsheet ID. Or, please use SpreadsheetApp.getActive().getId() if your script is container-bound script.

  let list = [];
  let pageToken;
  do {
    const obj = Drive.Revisions.list(spreadsheetId, { maxResults: 1000, fields: "nextPageToken,revisions(id,mimeType,modifiedTime)", pageToken });
    const items = obj.revisions.filter(({ modifiedTime }) => modifiedTime.split("T")[0] == date);
    if (items.length > 0) {
      list = [...list, ...items];
    }
    pageToken = obj.nextPageToken;
  } while (pageToken);
  if (list.length > 0) {
    const revision = list.pop(); // This is the last version on the date of `date`. If you want to first version on the date, please use list[0] instead of list.pop()
    if (revision.mimeType == MimeType.GOOGLE_SHEETS) {
      const endpoint = Drive.Revisions.get(spreadsheetId, revision.id, { fields: "exportLinks" }).exportLinks["application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"];
      if (endpoint) {
        const blob = UrlFetchApp.fetch(endpoint, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } }).getBlob();
        const file = DriveApp.getFileById(spreadsheetId);
        const res = Drive.Files.create({ name: `${file.getName()}_restored`, mimeType: MimeType.GOOGLE_SHEETS, parents: [file.getParents().next().getId()] }, blob);
        console.log(res.id);
      } else {
        console.error("Endpoint for exporting XLSX was not found.");
      }
    } else {
      console.error("File is not Google Spreadsheet.");
    }
  } else {
    console.error(`No revisions in ${date}.`);
  }
}