I was using below script to create copies of Google Sheets (files) as XLSX and the script was working perfectly.

Now with the pasaage of time numbers of Google Sheets (files) has been increased.

I want to Make a copy of Entire folder as XLSX format with all Google sheets. Your help will be much appreciated in this regards

function GoogleSpreadsheetAsExcel() {
  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'); 

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

CodePudding user response：

Your current function works on an active Sheets file context and only exports one file at the time.

The export logic is already implemented, you only need to read the files in a folder and export them in a loop.

To preserve existing logic and to accordingly run in this context, some values became constants (source and target Folder IDs) and file name and id are on the existing function signature to be provided on the loop iteration context.

Here is my suggestion:

Sample code:

const TARGET_FOLDER = "<TARGET_ID>"; //Folder ID where the exported XLSX files Will be saved
const SOURCE_FOLDER = "<SOURCE_FOLDER>"; //Folder ID where the Sheet files are located

function GoogleSpreadsheetAsExcel(fileId, fileName) {
  try {
    var url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key='   fileId   '&exportFormat=xlsx';
    var folder = DriveApp.getFolderById(TARGET_FOLDER); 

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

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

    blob.setName(fileName   '.xlsx');
    folder.createFile(blob)

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

function exportFiles(){
  var folder = DriveApp.getFolderById(SOURCE_FOLDER); 

  var files = folder.getFiles();
  while (files.hasNext()){ //Loop to read all files in SOURCE_FOLDER
    file = files.next(); //Keep reference of the file in the iteration 

    if (file.getMimeType() == "application/vnd.google-apps.spreadsheet"){ //If the iteration file is Sheets
      GoogleSpreadsheetAsExcel(file.getId(), file.getName()); //Export file as XLSX
    }
  }
}