CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-drive-apixlsxurlfetch
dave872
dave872

Reputation: 51

How to convert a Google Sheets-File to an Excel-File (XLSX)

The image shows the code who is updated. 1

The var "xlsFile" is undefined, why? How can I convert the Google Sheets file to an Excel file with (Google Sheets) Script Editor

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

function test(){
  var id = '#'
  exportToXls(id)
}

function exportToXls(id){
   var mute =  {muteHttpExceptions: true };
   var name = DriveApp.getFileById(id).getName()
   var url = 'https://docs.google.com/feeds/';
   var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',     mute).getBlob()
   var xlsfile = DocsList.createFile(doc).rename(name+'.xlsx')
}

Upvotes: 5

Views: 11559

Answers (2)

Serge insas
Serge insas

Reputation: 46794

The code below uses oAuthConfig which is now deprecated. Use Mogsdad answer instead. The importXLS function uses the drive API and still works.

You'll find many post saying this is not possible and (a few) others saying that you can...and obviously you can !

Mogsdad's answer here (simultaneously) brings an elegant solution using drive service, here is another one so you have a choice ;-)

As a bonus, I added the reverse process, if ever you need it.

Use a function call similar to what I use in the test function to make it work.

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

function test(){
  var id = 'spreadsheet_ID'
  exportToXls(id)
}

function exportToXls(id){
  var name = DriveApp.getFileById(id).getName()
  var url = 'https://docs.google.com/feeds/';
  var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xls',
                              googleOAuth_('docs',url)).getBlob()
  var xlsfile = DocsList.createFile(doc).rename(name+'.xls')
}

function importXLS(){
  var files = DriveApp.searchFiles('title contains ".xls"');
  while(files.hasNext()){
    var xFile = files.next();
    var name = xFile.getName();
    if (name.indexOf('.xls')>-1){
      var ID = xFile.getId();
      var xBlob = xFile.getBlob();
      var newFile = { title : name+'_converted',
                     key : ID
                    }
      file = Drive.Files.insert(newFile, xBlob, {
        convert: true
      });
    }
  }
}

Upvotes: 2

Mogsdad
Mogsdad

Reputation: 45710

Using the Drive API, we can get more information about files than is available through the DriveApp methods. Check out the file data, especially exportLinks. Those links contain the magic that will let us get an XLS file. (For fun, put a breakpoint after file is assigned, and check what information you have to play with.)

This script uses the Advanced Drive Service, which must be enabled. A more complete version, with error checking, is available in this gist.

/**
 * Downloads spreadsheet with given file id as an Excel file.
 * Uses Advanced Drive Service, which must be enabled. * Throws if error encountered.
 *
 * @param {String}   fileId       File ID of Sheets file on Drive.
 */
function downloadXLS(fileId) {
  var file = Drive.Files.get(fileId);
  var url = file.exportLinks[MimeType.MICROSOFT_EXCEL];

  var options = {
    headers: {
      Authorization:"Bearer "+ScriptApp.getOAuthToken()
    },
    muteHttpExceptions : true        /// Get failure results
  }

  var response = UrlFetchApp.fetch(url, options);
  var status = response.getResponseCode();
  var result = response.getContentText();
  if (status != 200) {
    // Get additional error message info, depending on format
    if (result.toUpperCase().indexOf("<HTML") !== -1) {
      var message = strip_tags(result);
    }
    else if (result.indexOf('errors') != -1) {
      message = JSON.parse(result).error.message;
    }
    throw new Error('Error (' + status + ") " + message );
  }

  var doc = response.getBlob();
  //DocsList.createFile(doc).rename(file.title + '.xlsx') // Deprecated
  DriveApp.createFile(doc).setName(file.title + '.xlsx');
}

Upvotes: 11

Related Questions