Google Script to export as xlsx not working

Question

function exportSpreadsheet() {
  var destination = SpreadsheetApp.create('Temp');
  var dest1 = destination.getActiveSheet().setName('출고입력 거래처정보(상단)');
  var dest2 = destination.insertSheet('출고입력 폼목정보(하단)');  
  var source1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('출고입력 거래처정보(상단)').getDataRange();
  var source2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('출고입력 폼목정보(하단)').getDataRange();
  var source1Data = source1.getValues();
  var source2Data = source2.getValues(); 
  var s1Rows = source1.getNumRows();
  var s1Columns = source1.getNumColumns();  
  var s2Rows = source2.getNumRows();
  var s2Columns = source2.getNumColumns();   

  // Copy Data to Temp file
  for(var i = 0; i<s1Rows; i++){ 
  for(var j = 0; j<s1Columns; j++) {
       dest1.getRange(i+1,j+1).setValue(source1Data[i][j]);
    }
  }  

  for(var i = 0; i<s2Rows; i++){ 
    for(var j = 0; j<s2Columns; j++) {
        dest2.getRange(i+1,j+1).setValue(source2Data[i][j]);
    }
  }

  // Export to xlsx
  var ssID = destination.getId();
  var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export"+"?format=xlsx";

  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var response = UrlFetchApp.fetch(url, params).getBlob();
  // Save to drive
  var folder = DriveApp.getFolderById('1vDFMM2EfRe1unR6hJOsKapfJrcwxRkxy');
  folder.createFile(response).setName('출고.xlsx');

  // Delete Temp file
  DriveApp.getFilesByName('Temp').next().setTrashed(true);

}

I am trying to export two tabs from my spreadsheet and save it as a xlsx file. In doing so I have copied the two tabs to a new spreadsheet and then exporting to xlsx using the following code. I have read through almost all posts that deal with export to xlsx via Google Script and have concluded they are all variations of the code below. It seems to work but with two issues:

1. It works but the excel file doesn't contain the data and just empty with two tabs. The URL works fine and the xlsx file contains data when I download it.

2. The last line that deletes a temp file I created to export doesn't do its job. The file still exists in the drive and nothing has changed.

I have looked everywhere for answers but can't figure this out. Any help will be appreciated.

Answer 1

Try this code

function exportSpreadsheet() {

  var ssID = "yourID";

  var url = "https://docs.google.com/spreadsheets/d/"+ssID+"/export"+
                                                        "?format=xlsx&"+
                                                        "size=0&"+
                                                        "fzr=true&"+
                                                        "portrait=false&"+
                                                        "fitw=true&"+
                                                        "gridlines=false&"+
                                                        "printtitle=true&"+
                                                        "sheetnames=true&"+
                                                        "pagenum=CENTER&"+
                                                        "attachment=true";

  var params = {method:"GET",headers:{"authorization":"Bearer "+ ScriptApp.getOAuthToken()}};

  var response = UrlFetchApp.fetch(url, params).getBlob();

  var folder = DriveApp.getFolderById("YourFolder");

  folder.createFile(response);

}

Answer 2

A for Q 1 :

In your case, how about using SpreadsheetApp.flush()? For example, please put this as follows.

// Export to xlsx
SpreadsheetApp.flush();
var ssID = destination.getId();

A for Q 2 :

After you updated your script, I noticed that DriveApp.removeFile(DriveApp.getFilesByName('Temp').next()) is modified to DriveApp.getFilesByName('Temp').next().setTrashed(true). By this, the file of Temp is put in the trash. I think that you have already known about this.

If this was not what you want, I'm sorry.