每次编辑时将Google Spreadsheet导出到.XLSX [英] Export Google Spreadsheet to .XLSX every time an edit is made

问题描述

我想要一个Google脚本，在编辑时自动将电子表格导出到.XLSX，覆盖以前的任何版本。以此答案作为模板，我创建了以下代码：
$ b

I want a Google Script that automatically exports the Spreadsheet to a .XLSX whenever there is an edit made, overwriting any previous versions. Using this answer as a template, I created the following code:

function onEdit() {
  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 1 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, "GMT-08:00 ", "MM/dd/yy, hh:mm:ss");
    SpreadsheetApp.getActiveSheet().getRange('A' + row.toString()).setValue(time); 

    var id = 'MY_SPREADSHEET_KEY'
    var url = 'https://docs.google.com/feeds/';
    var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
                                googleOAuth_('docs',url)).getBlob()
    DocsList.createFile(doc).rename('newfile.xls')
  };
 };

function googleOAuth_(name,scope) {
  var oAuthConfig = UrlFetchApp.addOAuthService(name);
  oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope="+scope);
  oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken");
  oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken");
  oAuthConfig.setConsumerKey('anonymous');
  oAuthConfig.setConsumerSecret('anonymous');
  return {oAuthServiceName:name, oAuthUseToken:"always"};
}

但是，它似乎不是导出。或者，如果是出口，我不知道这是怎么回事。

However, it doesn't seem to be exporting. OR, if it is exporting, I'm not sure where this is happening.

有什么想法？

Any ideas?

推荐答案

因为由于Google API的变化，Serge的某些脚本不再可用，所以我发布了基本上将当前电子表格导出到 xlsx （请注意，不支持导出到 xls ），并将其保存到名为 Exports 的文件夹中。 。在此之前，它会删除以前的 xlsx 文件并只保留最新的文件，这样就不需要计算时间或更改任何单元格：

Since, some of the script of Serge is no longer usable due to changes in Google's API, I am posting my script which basically exports the current spreadsheet to xlsx (please note that exporting to xls is not supported) and saves it to a folder called Exports. Prior to doing this, it deletes the previous xlsx file and keeps only the latest one, so that you don't need to count time or alter any cells:

function exportAsxlsx() {
  var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId()
  var file          = Drive.Files.get(spreadsheetId);
  var url           = file.exportLinks[MimeType.MICROSOFT_EXCEL];
  var token         = ScriptApp.getOAuthToken();
  var response      = UrlFetchApp.fetch(url, {
    headers: {
      'Authorization': 'Bearer ' +  token
    }
  });

  var blobs   = response.getBlob();
  var folder = DriveApp.getFoldersByName('Exports');
  if(folder.hasNext()) {
    var existingPlan1 = DriveApp.getFilesByName('newfile.xlsx');
    if(existingPlan1.hasNext()){
      var existingPlan2 = existingPlan1.next();
      var existingPlanID = existingPlan2.getId();
      Drive.Files.remove(existingPlanID);
    }
  } else {
    folder = DriveApp.createFolder('Exports');
  }
  folder = DriveApp.getFoldersByName('Exports').next();
  folder.createFile(blobs).setName('newfile.xlsx')
}

如果没有一个，它也会创建特定的文件夹。你可以玩这些命令，看看这些类是如何工作的。请注意，您需要从资源 - >中启用Drive API。高级Google服务 - > Drive API ，方法是在上切换到，也可以从Google Developers Console（请参阅 here ）。我还设置了一个简单的触发器，在每次编辑时调用此函数。这可以通过以下方式完成： Resources - >当前项目的触发器 - >添加一个新的触发器。您不需要添加任何库。

It also creates the specific folder if there isn't one. You can play with these commands and see how these classes work. Note that you will need to enable Drive API from both Resources -> Advanced Google Services -> Drive API by switching it to on and also from Google Developers Console (see detailed instructions here). I have also set a simple trigger that calls this function on each edit. This can be done by: Resources -> Current project's triggers -> Add a new trigger. You won't need any libraries to add.

这篇关于每次编辑时将Google Spreadsheet导出到.XLSX的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！