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

by

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

More Related Contents:

  1. How to automatically import data from uploaded CSV or XLS file into Google Sheets
  2. Google app scripts: email a spreadsheet as excel
  3. Moving Files In Google Drive Using Google Script
  4. Copying a spreadsheet copies all linked files as well
  5. Script to convert .XLSX to Google Sheet and move converted file
  6. Converting .xls to google spreadsheet in google apps script
  7. google script openById : You do not have permission to perform that action
  8. Determining the last row in a single column
  9. ArrayFormula is breaking the getLastRow() funtion. Possible workarounds?
  10. Easiest way to get file ID from URL on Google Apps Script
  11. Google Sheets OnEdit() – You do not have permission to call a service
  12. Creating anchored comments programmatically in Google Docs
  13. Detect user inserting row or column in a google spreadsheet and reacting in a script
  14. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  15. How to convert a Google Docs-File to an Excel-File (XLSX)
  16. How to get onFormSubmit to trigger automatically?
  17. Correct usage of DriveApp.continueFileIterator(continuationToken)
  18. onEdit simple trigger never seems to be triggered
  19. “SpreadsheetApp.getUi() cannot be called from this context”
  20. Using built-in spreadsheet functions in a script
  21. How to access new ‘in-cell-image’ from google apps script?
  22. Can no longer produce PDF from Google Sheets spreadsheet for some of the users
  23. Difference between getValue() and getDisplayValue() on google app script
  24. How to allow onEdit function to affect protected cell in a Google Sheet?
  25. Creating and Sending sheets in the same function
  26. How do I know if spreadsheet cells are merged using google apps script
  27. How to use a formula written as a string in another cell [evaluate for Google Spreadsheet] [duplicate]
  28. Google apps script error: “You do not have permission to call protect” [duplicate]
  29. Google spreadsheet “Cannot call method “getRange” of null”
  30. Is it possible to create macro buttons to navigate in Google Sheets?

Leave a Comment