Export Google Spreadsheet to .XLSX every time an e

问题:

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?

回答1:

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')
}

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.

回答2:

It was not indeed ! probably because the oAuth function didn't get the right authorization and also because the simple onEdit is not allowed to do this kind of operation.

You'll have to create an installable trigger (menu>ressource>current trigger>create).

Try this script below and run the authorize function.

I changed also a few details : timeZone is taken directly from the spreadsheet and id is also taken from the active spreadsheet.

Note also that the newly created XLSX will not overwrite any previous file, you'll get many documents with the same name ! If you want to keep only the latest version then you should take care of that by yourself, getting all the docs names 'new file' and delete them using file.setTrashed(true) before you create the new one.

This would be as simple as these 2 lines of code :

var oldVersions = DocsList.find('newfile.xls');
for(var d in oldVersions){oldVersions[d].setTrashed(true)};

The code :

function myOnEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 1 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, ss.getSpreadsheetTimeZone(), "MM/dd/yy, hh:mm:ss");
    var id = ss.getId();
    s.getRange('A' + row.toString()).setValue(time); 
    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 authorise(){
  // function to call to authorize googleOauth
  var id=SpreadsheetApp.getActiveSpreadsheet().getId();
  var url = 'https://docs.google.com/feeds/';
  var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
                            googleOAuth_('docs',url)).getBlob()
}
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"};
}

---

EDIT : following your comment, here is a version that saves only every 30 sec (or more if no edit are done). You can easily change the time value to another interval if necessary.

Re-run the authorize function to initialize the scriptProperty.

function myOnEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = ss.getActiveSheet();
  var r = s.getActiveCell();
  if( r.getColumn() != 1 ) { //checks the column
    var row = r.getRow();
    var time = new Date();
    time = Utilities.formatDate(time, ss.getSpreadsheetTimeZone(), "MM/dd/yy, hh:mm:ss");
    var id = ss.getId();
    s.getRange('A' + row.toString()).setValue(time); 
    var lastSaveTime = new Date(Utilities.jsonParse(ScriptProperties.getProperty('exportTime')));
    var now = new Date().getTime();
    Logger.log(now - lastSaveTime.getTime())
    if (now - lastSaveTime.getTime() > 60000){ // delete every minute
      var oldVersions = DocsList.find('newfile.xls');
      for(var d in oldVersions){oldVersions[d].setTrashed(true)};
    }
    if (now - lastSaveTime.getTime() > 30000){ // save every 30"
      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')  
      ScriptProperties.setProperty('exportTime',Utilities.jsonStringify(new Date()));
    }
  }
}

function authorise(){
  // function to call to authorize googleOauth + initialize the TIMER
  ScriptProperties.setProperty('exportTime',Utilities.jsonStringify(new Date()));
  var id = SpreadsheetApp.getActiveSpreadsheet().getId();
  var url = 'https://docs.google.com/feeds/';
  var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
                                googleOAuth_('docs',url)).getBlob()
}

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"};
}