0

I have an apps script that generates a 2D array. I would like to export this array to a folder on my Google Drive in legacy .XLS format, ideally without first creating a Google Sheet and then converting that sheet.

I thought I could turn my array into a CSV string and convert that to blob with the appropriate MimeType, and save that in Drive.

However, when I download the file from Drive and open it, the values aren't separated (tried "," and ";" as delimiter).

My script below, with a simplified array for example.

function createXls() {
var data = [["a","b","c"],["d","e","f"]];
var csvString = toCsv(data);

var xlsName = "here goes the filename";
var driveFolder = DriveApp.getFolderById("hereGoesTheFolderId");

var blob = Utilities.newBlob(csvString, MimeType.MICROSOFT_EXCEL_LEGACY);

blob.setName(xlsName + ".xls");
driveFolder.createFile(blob);

};

function toCsv(arr) {
    return arr.map(row =>
        row.map(val => val).join(';')
    ).join('\n');
};

Am I missing something here, or is there no wat around putting the data in a sheet first and converting that sheet to xls?

Thank you!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
MRI80
  • 35
  • 1
  • 6
  • Related [Apps Script, convert a Sheet range to Blob](https://stackoverflow.com/q/44002917/1595451) – Rubén Oct 30 '20 at 15:42
  • 3
    The structure of XLS data can be seen at [here](https://docs.microsoft.com/en-us/openspecs/office_file_formats/ms-xls/cd03cb5f-ca02-4934-a391-bb674cb8aa06?redirectedfrom=MSDN). From this, I think that the file created with your script is CSV file with the mimeType of XLS. By this, I think that the file cannot be opened. But unfortunately, it seems that Google Spreadsheet cannot be directly exported to XLS format. If you want to retrieve the XLS data from Google Spreadsheet, I thought that XLS data might be required to be retrieved from XLSX data exported from Spreadsheet. I apologize for this. – Tanaike Oct 30 '20 at 23:42

0 Answers0