Export a Google Sheet to Google Drive in Excel format with Apps Script

Question

I would like to create a backup of my Google Spreadsheet into my Google Drive folder, but as an Excel file. I managed to create a code that create a copy of the gsheet and save it into the folder, but I could not change the code to save it as an Excel file.

Could you please help me with it?

function makeCopy() {
  var formattedDate = Utilities.formatDate(new Date(), "CET", "yyyy-MM-dd' 'HH:mm");
  var name = "Backup Copy " + formattedDate;
  var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");
  var file = DriveApp.getFileById("2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c")
  file.makeCopy(name, destination);
}

Answer 1

How about this answer? I think that there are several answers for your situation. So please think of this as one of them.

In order to convert spreadsheet to excel, the endpoint of https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx can be used. In this answer, this was used.

Modified script :

function makeCopy() {
  var formattedDate = Utilities.formatDate(new Date(), "CET", "yyyy-MM-dd' 'HH:mm");
  var name = "Backup Copy " + formattedDate;
  var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");

  // Added
  var sheetId = "2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c";
  var url = "https://docs.google.com/spreadsheets/d/" + sheetId + "/export?format=xlsx&access_token=" + ScriptApp.getOAuthToken();
  var blob = UrlFetchApp.fetch(url).getBlob().setName(name + ".xlsx"); // Modified
  destination.createFile(blob);
}

If I misunderstand your question, I'm sorry.

Updated: February 7, 2020

From January, 2020, the access token cannot be used with the query parameter like access_token=###. Ref So please use the access token to the request header instead of the query parameter. It's as follows.

var res = UrlFetchApp.fetch(url, {headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}});