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

Question

The image shows the code who is updated.

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

The MIME type to convert to. For most blobs, 'application/pdf' is the only valid option. This MIME type (Excel) is not supported with getAs(). You can find more information on this link: https://developers.google.com/apps-script/reference/docs-list/file — Dayton Wang, Dec 03 '14 at 17:30
Here's a similar question with an up-to-date answer. http://stackoverflow.com/questions/31809987/google-app-scripts-email-a-spreadsheet-as-excel — Christiaan Westerbeek, Oct 08 '15 at 15:07

Mogsdad · Answer 1 · 2015-09-02T18:17:15.897

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

Mogsdad, I think the UrlFetch needs a token to download the converted file. Please take a look [here](http://stackoverflow.com/questions/32356863/converting-google-spreadsheet-to-excel-with-appscript-and-send-it-via-email/32359156#32359156) — Henrique G. Abreu, Sep 02 '15 at 17:08
Thanks @HenriqueAbreu. Fully updated with my production script. — Mogsdad, Sep 02 '15 at 18:18

Serge insas · Answer 2 · 2015-11-28T12:38:46.267

2

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

edited Nov 28 '15 at 12:38

answered Dec 03 '14 at 21:17

Serge insas

45,904
7
105
131

Beat you by 5 minutes! – Mogsdad Dec 03 '14 at 21:23
you're just typing faster XD – Serge insas Dec 03 '14 at 21:24

The error that comes when I run the query.

Fehler bei der Anfrage für . Folgender Code wurde zurückgegeben: 404. Gekürzte Serverantwort:

– dave872 Dec 09 '14 at 09:20

how are you trying this code ? did you follow exactly the procedure I described ? – Serge insas Dec 09 '14 at 12:45
I've updated my Code (see above), and it still happens nothing? @Sergeinsas – dave872 Dec 16 '14 at 09:38
@Mogsdad whats your meaning? – dave872 Dec 16 '14 at 15:15
@dave872 - I don't understand your comment. – Mogsdad Dec 17 '14 at 02:24
@Mogsdad with a successful run of the code, the file must be created or not? – dave872 Dec 17 '14 at 07:28
1

I just want a Google Docs file saved as an Excel file , is this so difficult? @Mogsdad – dave872 Dec 17 '14 at 07:37
@dave872 - It only seems difficult because you have implemented it wrong. The updated code in your question shows that you left out the Authorization bearer from the `options` parameter, and are instead only muting exceptions. You must provide that authorization, otherwise `UrlFetchApp` will be like a user who has not logged in, it will have no access to Google Drive. (Why are you asking me about Serge's answer, anyway? Did you even try mine?) – Mogsdad Dec 17 '14 at 12:00
When I implement the Authorization then comes this Error: Methode fetch(string,object,object) nicht gefunden (Zeile 33, Datei "Export") @Mogsdad – dave872 Dec 17 '14 at 12:53
And when I implement the code without the mute (mutehttpExceptions) then come the Server Error (see above) || var doc = UrlFetchApp.fetch(url+'download/spreadsheets/Export?key='+id+'&exportFormat=xlsx', googleOAuth_('docs',url)).getBlob() @Mogsdad – dave872 Dec 17 '14 at 12:55
` fetch(string,object,object)` - there are just TWO parameters for fetch - you're doing it wrong. – Mogsdad Dec 17 '14 at 13:14

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

2 Answers2

Linked

Related