如何获取旧版本的Google Spreadsheet数据? [英] 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 Spreadsheet.
       • 通过这种方式，将值分隔到每张纸上.
    1. Retrieve Spreadsheet of other version as the excel format.
    2. Convert the excel format to Google Spreadsheet.
       • By this, the values are separated to each sheet.

    示例脚本:

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

    Sample script:

    Before you run this script, please enable Drive API at Advanced Google services. And set the variables of spreadsheetId and 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.

      Note:

      • In this sample script, a Spreadsheet of the version is created to the root folder.
        • The filename is the revision ID.
        • 下载并发布文件修订
        • 修订:列表
        • 修订:获取
        • 文件:插入
        • 高级Google服务
        • Download and publish file revisions
        • Revisions: list
        • Revisions: get
        • Files: insert
        • Advanced Google services

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

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

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