Convert entire Google Sheet to xlsx file

Question

I have tried to amend the below script to essentially convert a entire google sheet to xlsx file, keeping tab names the same and locating them to a folder,

The issue I am experiencing with the below is that is is splitting out each tab into separate files but I would like to keep them all together in one file

https://webapps.stackexchange.com/questions/58615/export-all-sheets-in-a-spreadsheet-to-csv-in-google-apps

function saveAsxlsx() {
  
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  // create a folder from the name of the spreadsheet
  var folder = DriveApp.getFolderById('xxxxxxx');
  
 for (var i = 0 ; i < sheets.length ; i++) {
    var sheet = sheets[i];
    // append ".xlsx" extension to the sheet name
    fileName = sheet.getName() + ".xlsx";
    // convert all available sheet data to xlsx format
    var xlsxFile = convertRangeToxlsxFile_(fileName, sheet);
    // create a file in the Docs List with the given name and the xlsx data
    folder.createFile(fileName, xlsxFile);
  }
  Browser.msgBox('Files are waitig in a folder named ' + folder.getName());
}

function convertRangeToxlsxFile_(xlsxFileName, sheet) {
  // get available data range in the spreadsheet
  var activeRange = sheet.getDataRange();
  try {
    var data = activeRange.getValues();
    var xlsxFile = undefined;

    // loop through the data in the range and build a string with the xlsx data
    if (data.length > 1) {
      var xlsx = "";
      for (var row = 0; row < data.length; row++) {
        for (var col = 0; col < data[row].length; col++) {
          if (data[row][col].toString().indexOf(",") != -1) {
            data[row][col] = "\"" + data[row][col] + "\"";
          }
        }

        // join each row's columns
        // add a carriage return to end of each row, except for the last one
        if (row < data.length-1) {
          xlsx += data[row].join(",") + "\r\n";
        }
        else {
          xlsx += data[row];
        }
      }
      xlsxFile = xlsx;
    }
    return xlsxFile;
  }
  catch(err) {
    Logger.log(err);
    Browser.msgBox(err);
  }
}

CSV doesn't have the concept of multiple sheets per workbook. You should try to export directly to XLSX, see https://www.howtogeek.com/757935/how-to-convert-a-google-sheet-to-microsoft-excel — James, Jul 12 '22 at 12:52
I have amended the script to pick up xlsx, the bookmark is a reference to what i have used — Alexasks, Jul 12 '22 at 12:58
The problem there is that question is specifically asking to get each sheet as a separate CSV. An xlsx file is a ZIP file that contains a few folders. I would expect any "xlsx" exporter to have a ZIP mechanism. Check https://stackoverflow.com/questions/31809987/google-app-scripts-email-a-spreadsheet-as-excel — James, Jul 12 '22 at 13:03

score 1 · Answer 1 · answered Jul 12 '22 at 19:56

The script you're using was designed to get each sheet as a separate file. You can refer to this example instead that will convert Google Sheet to Excel XLSX Spreadsheet. I added a couple of lines to the code to save the file to a folder instead of sending an email as the example does.

function getGoogleSpreadsheetAsExcel() {
  try {
    var ss = SpreadsheetApp.getActive();
    var url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' + ss.getId() + '&exportFormat=xlsx';
    var folder = DriveApp.getFolderById('folderID'); //Add the folder ID of the folder where you want to save the file

    var params = {
      method: 'get',
      headers: { Authorization: 'Bearer ' + ScriptApp.getOAuthToken() },
      muteHttpExceptions: true,
    };

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

    blob.setName(ss.getName() + '.xlsx');
    folder.createFile(blob)

  } catch (f) {
    Logger.log(f.toString());
  }
}

Thank you Lorena, is there a way to add something to paste as values as some of the sheets have importranges that do not carry over so the cells have a #REF — Alexasks, Jul 25 '22 at 13:45

Convert entire Google Sheet to xlsx file

1 Answers1