CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Alexasks
Alexasks

Reputation: 113

Convert entire Google Sheet to xlsx file

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

Upvotes: 1

Views: 673

Answers (1)

Lorena Gomez
Lorena Gomez

Reputation: 2203

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

Upvotes: 1

Related Questions