如何获取旧版本的 Google 电子表格数据? [英] How to get older versions of Google Spreadsheet data?

问题描述

我正在尝试比较不同版本的电子表格数据并突出显示两个版本之间的差异.我知道有版本历史功能，但我想要的是在选择版本时获取所有修订历史.(即，如果最新版本是五个而我选择两个，我想获得版本二之间的所有修订和五个).

I'm trying to compare different versions of Spreadsheet data and highlight differences between the two versions. I know there is Version History feature, but what I'm trying is to get all the revision history when choosing the version.(i.e. if the latest version is five and I choose two, I want to get all the revisions made between version two and five).

我知道如何访问当前版本，但我对如何使用旧版本执行此操作感到困惑.有没有办法像下面的代码一样访问旧版本?

I know how to access to the current version, but I'm stuck with how to do this with older versions. Is there any way to access to older versions as the same manner as the following code?

var spreadsheet = SpreadsheetApp.openById(id);
var sheet = spreadsheet.getSheets()[0];
var sheetdata = sheet.getDataRange().getValues();

推荐答案

• 您想使用以下脚本从其他版本的电子表格中检索值.

  • You want to retrieve the values from other version of Spreadsheet using the following script.

    var spreadsheet = SpreadsheetApp.openById(id);
    var sheet = spreadsheet.getSheets()[0];
    var sheetdata = sheet.getDataRange().getValues();
    

  • 您已经获得了电子表格的修订 ID.

  • You have already got the revision ID of the Spreadsheet.

    如果我的理解是正确的，这个答案怎么样?当仅从其他版本的电子表格中检索值时，此解决方法如何?不幸的是，某些修订版的电子表格不能作为电子表格直接检索.所以在这个解决方法中，它被转换为其他格式并检索值.请将此视为多个答案之一.

    If my understanding is correct, how about this answer? When only values are retrieved from the other version of Spreadsheet, how about this workaround? Unfortunately, the Spreadsheet of the some revision cannot be directly retrieved as the Spreadsheet. So in this workaround, it is converted to other format and retrieved the values. Please think of this as just one of several answers.

    此解决方法的流程如下.

    The flow of this workaround is as follows.

    1. 以excel格式检索其他版本的电子表格.
    2. 将 excel 格式转换为 Google 电子表格.
       • 通过这种方式，值被分隔到每个工作表中.

    示例脚本:

    在运行此脚本之前，请在 高级 Google 上启用 Drive API服务.并设置spreadsheetId和revisionId的变量.

    function myFunction() {
      var spreadsheetId = "###"; // Please set the Spreadsheet ID.
      var revisionId = "###"; // Please set the revision ID.
    
      var url = "https://docs.google.com/spreadsheets/export?id=" + spreadsheetId + "&revision=" + revisionId + "&exportFormat=xlsx";
      var blob = UrlFetchApp.fetch(url, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}}).getBlob();
      var id = Drive.Files.insert({mimeType: MimeType.GOOGLE_SHEETS, title: revisionId}, blob).id;
    
    
      var spreadsheet = SpreadsheetApp.openById(id);
      var sheet = spreadsheet.getSheets()[0];
      var sheetdata = sheet.getDataRange().getValues();
    }
    

    注意:

    • 在此示例脚本中，将在根文件夹中创建版本的电子表格.
      • 文件名是修订 ID.
      • 下载和发布文件修订版
      • 修订:列表
      • 修订:获取
      • 文件:插入
      • 高级 Google 服务

      如果我误解了您的问题并且这不是您想要的方向，我深表歉意.

      If I misunderstood your question and this was not the direction you want, I apologize.

      这篇关于如何获取旧版本的 Google 电子表格数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！