Export Google Spreadsheet to .XLSX every time an edit is made

Question

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?

Answer 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.