CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsexport-to-excelgoogle-sheets-macros
Erika
Erika

Reputation: 290

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

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

Upvotes: 2

Views: 11805

Answers (4)

j0lly_Rog3r
j0lly_Rog3r

Reputation: 21

Good day,

For anyone who plans on using this method in a fully automated setting, you may run into issue where the excel document is only partially filled out. This is because the UrlFetchApp isn't going to wait for computations/other SpreadSheetApp commands to complete like SpreadsheetApp or Sheets does. You can use

SpreadSheetApp.flush();
var blob = UrlFetchApp [...]

To force it to fully update sheet first.

Upvotes: 0

Cooper
Cooper

Reputation: 64100

I just added a Simple UI to make it more useful as a day to day tool. The top script is nearly a direct copy of MattMcCode's code. So give him the attribution.

function exportSpreadsheetToXLSX(obj) {
  if (obj) {
    console.log(JSON.stringify(obj));
    var sheetId = obj.ssid;
    var spreadsheetName = obj.filename;
    var destination = DriveApp.getFolderById(obj.desid);
    var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sheetId + "&exportFormat=xlsx";
    var params = {
      method: "get",
      headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true
    };
    var blob = UrlFetchApp.fetch(url, params).getBlob();
    blob.setName(spreadsheetName + ".xlsx");
    destination.createFile(blob);
  }
}

Just execute the below dialog to enter your ssid, folderid and filename

function openSpreadsheetExportToXLSXDialog() {
  let html = '';
  html += '<html><head><style> input{margin: 2px 5px 2px 0;}</style></head><body>';
  html += '<form>';
  html += '<input type="text" name="ssid" size="50" placeholder="Source Spreadsheet Id" /><br />';
  html += '<input type="text" name="desid" size="50" placeholder="Destination Folder Id" /><br />';
  html += '<input type="text" name="filename" size="50" placeholder="Spreadsheet ID" /><br />';
  html += '<input type="button" value="Submit" onClick="processForm(this.parentNode)" />';
  html += '<input type="button" value="Exit" onClick="google.script.host.close();" />';
  html += '</form>';
  html += '<script>';
  html += 'function processForm(obj){console.log(obj);google.script.run.withSuccessHandler((obj)=>{google.script.host.close();}).exportSpreadsheetToXLSX(obj);}';
  html += '</script></body></html>';
  SpreadsheetApp.getUi().showModelessDialog(HtmlService.createHtmlOutput(html),'Spreadsheet Export to XLSX Dialog');
}

Upvotes: 0

MattMcCode
MattMcCode

Reputation: 307

Just wanted to tag team off of Tanaike's answer and show how to add the authentication to the header during the call.

function convertSheetToXLSX() {
  var sheetId = "2SqIXLiic6-gjI2KwQ6OIgb-erbl3xqzohRgE06bfj2c";
  var spreadsheetName = "My Spreadsheet";
  var destination = DriveApp.getFolderById("1vFL98cgKdMHLNLSc542pUt4FMRTthUvL");
  var url = "https://docs.google.com/feeds/download/spreadsheets/Export?key=" + sheetId + "&exportFormat=xlsx";  
  var params = {
    method      : "get",
    headers     : {"Authorization": "Bearer " + ScriptApp.getOAuthToken()},
    muteHttpExceptions: true
  };
  var blob = UrlFetchApp.fetch(url, params).getBlob();
  blob.setName(spreadsheetName + ".xlsx");
  destination.createFile(blob);
}

Upvotes: 2

Tanaike
Tanaike

Reputation: 201613

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

Upvotes: 7

Related Questions